Pages in this article
-
Introduction
-
How does it work?
-
Setting things up
-
Class Module
-
VBA code
-
Put to Use
-
Wrap Up
Introduction
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.
Excel 2010
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):
Option Explicit
Sub RegisterFunction()
Dim vArgDescr(1 To
19) As Variant
Dim lCt As
Long
For lCt = 1 To
19
vArgDescr(lCt) = "Description for argument # " & lCt
Next
Application.MacroOptions _
Macro:="UDFTest", Description:="Test
function with 19 arguments", _
Category:="UDF Helper demo", _
ArgumentDescriptions:=vArgDescr
End Sub
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!"
End Function
To remove the argument descriptions and the custom category, run the
UnRegisterFunction routine as shown below. It will move the UDF back to
the User Defined category.
Sub UnRegisterFunction()
'Make sure the array below has the same size
as the original number of arguments
Dim vArgDescr(1 To 19)
As Variant
Application.MacroOptions _
Macro:="UDFTest", Description:="Test
function with 19 arguments", _
Category:=14, _
ArgumentDescriptions:=vArgDescr
End Sub
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.