Arguments & Descriptions
User Defined Arguments
Prior to Excel 2010 there was no easy way to add argument descriptions to your user defined functions.
In Excel 2010 an additional argument was added to the Application.MacroOptions method.
In this example are we are going to use the CONTAINS user defined function.
Just adding the code to a workbook will display the following dialog box, i.e. with no argument descriptions.
Excel 2010 and later
Public Functon MyUserDefinedFunction(ByVal sText As String) As String
Public Sub DefineFunction
Dim sFunctionName As String
Dim sFunctionCategory As String
Dim sFunctionDescription As String
Dim aFunctionArguments(1 To 2) As String
sFunctionName = "MyUserDefinedFunction"
sFunctionDescription = "This is my new user defined function"
sFunctionCategory = 7 ' Text category
aFunctionArguments(1) = "String to contain the name"
aFunctionArguments(2) = "Long to contain the value"
Application.MacroOptions Macro:=sFunctionName, _
Excel 2007 - Character Restriction
You can add argument descriptions by using the old XLM Register Function in conjunction with the ExecuteExcel4Macro VBA function but you are limited to a maximum of 255 characters.
This method is therefore no good when you have functions that contain more than a couple of arguments.
It is possible to overcome this restriction by using an ancient XLM macro sheet.
Excel 2007 - No Character Restriction
Laurent Longre discovered, that if one uses the Excel 4 macro language's register function to register a function residing within any system dll, using an alias name which is identical to the name of a UDF, one can assign the UDF to one's own category in the function wizard. At the same time, one can also add a function description and argument descriptions.
This method is based on a curious behaviour of XL97 : assume that an open workbook contains a function called "Myfunc". If you try now to register any DLL function (for instance, one of the Win95 API) with the same name, here's what happens: when you use MyFunc() in a worksheet, it works fine and returns the result of the VBA function. But if you call it with the function wizard, it displays the informations of ... the registered DLL function!
In other words, you can add a few lines in an Auto_open Sub which register some API functions with the same names as your VBA functions. REGISTER enables to assign the functions to any custom categories, and also to "document" each argument. When you call later one of the VBA custom function, these parameters passed to REGISTER will appear in the function wizard, including the custom category.
After further experimenting, I've noticed also that you must declare the VBA functions "Private" in order to remove them from the default "user-defined" category (otherwise, they would appear twice in the wizard). Of course, the VBA add-in must also provide an Auto_close Sub which unregisters the functions. To remove the added custom categories, I've found this way : first, unregister the functions, then register them with the MacroType argument set to 0 (= hidden function), and finally unregister them one more time.
This method is just a funny work-around. I don't know if it works without any restriction (I've just achieved a few tests), and it requires that you add some Auto_open and Auto_close code in the add-in workbook.
the REGISTER function allows one to "register" a function from a library such as USER32.dll with an alias name (perhaps originally intended to allow a more user-friendly name), assign the library function to a function category for Excel's Paste Function list palette, and also provide brief descriptions of the arguments for the library function that will appear in Excel's Paste Function formula palette. However, if the alias name (e.g. Multiply2) chosen for the library function (e.g. CharNextA) happens to be also the name of an UDF available in the workbook, then the UDF will be used in place of the library function when the function name/alias is called up.
I see that Joerd indicates that the same library function can be used in "registering" each UDF; I never tried this; please let me know if it really works. As I understand the method, each UDF has to be "registered over" a different function from the library. The library function that is "registered over" with a user-defined-function (UDF), is not available once the UDF is treated by this method but there are a lot of obscure functions in these libraries that are very seldomly used. All of the functions in the library (e.g. USER32.dll) can be quickly seen by opening the dll file in notepad and scrolling down to where identifiable words show up. A few of the functions in USER32.dll are: ActivateKeyboardLayout AdjustWindowRect AdjustWindowRectEx ... BeginDeferWindowPos BeginPaint ... CallMsgFilter CallMsgFilterA ... CharLowerA CharLowerBuffA CharLowerBuffW CharLowerW CharNextA CharNextExA CharNextW CharPrevA CharPrevExA CharPrevW CharToOemA CharToOemBuffA CharToOemBuffW CharToOemW CharUpperA CharUpperBuffA CharUpperBuffW CharUpperW ... DdeAbandonTransaction DdeAccessData ... etc.
However, I won't avdocate which library functions you should register over.
Const Lib = """USER32"""
Option Base 1
Private Function Divide(ByVal N1 As Double, _
ByVal N2 As Double) As Double
Divide = N1 / N2
Private Function Multiply2(ByVal N1 As Double, _
ByVal N2 As Double) As Double
Multiply2 = N1 * N2
Private Function Multiply3(ByVal N1 As Double, _
ByVal N2 As Double, _
ByVal N3 As Double) As Double
Multiply3 = N1 * N2 * N3
Private Sub Auto_Open()
Call Register("DIVIDE", _
"My Functions1", _
"Divides two numbers", _
Call Register("MULTIPLY2", _
"My Functions1", _
"Multiplies two numbers", _
"""First number"",""Second number""", _
Call Register("MULTIPLY3", _
"My Functions2", _
"Multiplies three numbers", _
"""First number"",""Second number"",""Third number""", _
Private Sub Register(ByVal sFunctionName As String, _
ByVal iNoOfArguments As Integer, _
ByVal Args As String, _
ByVal MacroType As Integer, _
ByVal Category As String, _
ByVal Descr As String, _
ByVal DescrArgs As String, _
ByVal FLib As String)
Application.ExecuteExcel4Macro "REGISTER(" & Lib & ",""" & FLib & """,""" & String(NbArgs, "P") & _
""",""" & FunctionName & """,""" & Args & """," & _
MacroType & ",""" & Category & """,,,""" & Descr & """," & DescrArgs & ")"
Dim i As Integer
FName = Array("DIVIDE", "MULTIPLY2", "MULTIPLY3")
For i = 1 To 3
.ExecuteExcel4Macro "UNREGISTER(" & FName(i) & ")"
.ExecuteExcel4Macro "REGISTER(" & Lib & ",""CharNextA"",""P"",""" & FName(i) & """,,0)"
.ExecuteExcel4Macro "UNREGISTER(" & FName(i) & ")"
Unfortunately, if you load files which introduce new categories, the category index numbers depend on the order of loading. If you can't predict this, you can't be sure what category the function will fall into. I know of no good solution to this,
User Defined Descriptions
There are at least 2 ways you can use to add a description to you user defined functions.
One is relatively well known, while the other is a simple but little known method. Lets use the better known method first.
Using (Insert > Function) Dialog Box
When you select a function in the (Insert > Function) dialog box a brief description appears at the bottom telling you what the function does.
In the case of the SUM() function it adds all the numbers in a range of cells.
Press (Tools > Macro > Macros) to display the "Macros" dialog box.
This dialog box only displays procedures and not functions although it is possible to assign a description to a function.
Type the exact name of the function. If the name is valid then the "Options" button should be enabled.
If the Options button is greyed out then your function name cannot be recognised. It is not case sensitive.
You can then add your description in the same way you do for a procedure. The shortcut key is clearly redundant in this case.
Using VBA Code
The following line defines the descriptions for a function called CapitalLetter.
Application.MacroOptions Macro:="CapitalLetter", _
Description:="RETURNS the character as a capital letter"
Using Object Browser
Open up the VBE (Alt+F11) and select anywhere within your Function code.
Now Push F2 to open the "Object Browser".
At the top of the Object Browser there are 2 drop down boxes. Click the top one and select "VBAProject".
You should now have all Modules and global Objects showing in the "Classes" box situated at the bottom of the Object Browser.
Click on the name of the Module that houses your UDF.
In the "Members of..." box to the right you should see the names of all Functions and Procedures within the selected Module.
Simply right click on the name of your UDF and select "Properties".
Type a description for your UDF, then click Ok and then Save.
You must save and close for the changes to be made
Reopning the workbook will show you the new description
User Defined Categories
By default any user defined functions will be added to the User Defined category in the (Insert > Functions) dialog box.
It is possible to create a new category to store your functions but it involves using an Excel 4.0 Macro.
There is no direct way to add a function to a function category when it is created.
By default they will all be added to the User Defined category.
The table below lists the category names with their corresponding numbers.
|0||All (no specific category)||10||Commands (normally hidden but visible if you add a function to this category)|
|1||Financial||11||Customizing (normally hidden but visible if you add a function to this category)|
|2||Date & Time||12||Macro Control (normally hidden but visible if you add a function to this category)|
|3||Maths & Trigonometry||13||DDE / External (normally hidden but visible if you add a function to this category)|
|4||Statistical||14||User Defined (default)|
|5||Lookup & Reference||15||Engineering|
|7||Text||17||First custom category|
|8||Logical||18||Second custom category|
You can define the category for your functions when the workbook or add-in is opened.
Application.MacroOptions macro:="Macro Name", Category:=2
Insert an Excel 4.0 Macro worksheet
Right click a worksheet and select the Insert button.
Select "MS Excel 4.0 Macro" and click OK.
This will insert a new Excel 4.0 Macro worksheet into your workbook (or add-in).
Add the new Function Category
Select (Insert > Name > Define) and in the bottom right click "Function".
Type the name of your category in the "Names in workbook" box.
The new category will be added to the list in the (Insert > Functions) dialog box.
Once you have added at least one function to this new category you can actually delete the Macro 4.0 worksheet.
You will not be able to add any more functions to the category once the Macro 4.0 worksheet has been deleted.