When one writes a User Defined Function in Excel VBA, this function appears in the function wizard under the category "Custom". It is possible to set which category the function belongs to by changing the "macro properties" of the UDF.
To set argument descriptions, the method to be used differs which Excel version you are targetting. For Excel 2010, we've got a new argument we can use, which makes setting argument descriptions very simple.
The Application.MacroOptions method has gained a new argument called ArgumentDescriptions which enables you to do just what this article describes. The VBA code is simple (in a normal module):
Dim vArgDescr(1 To 19) As Variant
Dim lCt As Long
For lCt = 1 To 19
vArgDescr(lCt) = "Description for argument # " & lCt
Macro:="UDFTest", Description:="Test function with 19 arguments", _
Category:="UDF Helper demo", _
Public Function UDFtest(Optional arg1, Optional arg2, Optional arg3, _
Optional arg4, Optional arg5, Optional arg6, Optional arg7, _
Optional arg8, Optional arg9, Optional arg10, _
Optional arg11, Optional arg12, Optional arg13, _
Optional arg14, Optional arg15, Optional arg16, _
Optional arg17, Optional arg18, Optional arg19) As String
UDFtest = "Test!"
All Excel versions up to (and including) 2007
Warning. The technique uses a number of tricks that are officially unsupported. Use at your own risk. As far as I know however, this does work reliably.
One can assign the UDF to a specific function category using the Application.MacroOptions method, but that method does not allow for proper function descriptions to be set.
Unfortunately, VBA does not allow specifying a description for the UDF's arguments. There is a workaround which uses the old XLM REGISTER function in conjunction with the ExecuteExcel4Macro function from within VBA (as described in the excellent book Professional Excel Development). This function however only accepts 255 characters or less, which is insufficient for UDFs with more than just one or two arguments and severely limits the amount of text one can use for the explanation of the arguments. This article describes how this can be overcome, using some trickery invented by Laurent Longre (page is in French, the website is no longer available, so the link points to the Webarchive.com).
Originally I used an XLM (Excel 4 macro) sheet to overcome the 255 character limit, but this caused Excel to crash very frequently.
KeepItCool invented a workaround by putting the arguments of the register function into Excel's little know hidden namespace (Application level defined names only accessible through Excel4 macro commands). He also wrote the workbook that can be downloaded here.