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 
End Function 

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, _ 
         Description:=sFunctionDescription, _ 
         Category:=sFunctionCategory, _ 
         ArgumentDescriptions:=aFunctionArguments 
End Sub

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.


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 
End Function 

Private Function Multiply2(ByVal N1 As Double, _ 
                           ByVal N2 As Double) As Double 
   Multiply2 = N1 * N2 
End Function 

Private Function Multiply3(ByVal N1 As Double, _ 
                           ByVal N2 As Double, _ 
                           ByVal N3 As Double) As Double 
   Multiply3 = N1 * N2 * N3 
End Function 

Private Sub Auto_Open() 
   Call Register("DIVIDE", _ 
                3, _ 
                "Numerator,Divisor", _ 
                1, _ 
                "My Functions1", _ 
                "Divides two numbers", _ 
                """Numerator"",""Divisor""", _ 
                CharNextA) 

   Call Register("MULTIPLY2", _ 
                3, _ 
                "Number1,Number2", _ 
                1, _ 
               "My Functions1", _ 
               "Multiplies two numbers", _ 
               """First number"",""Second number""", _ 
               "CharNextA") 

   Call Register("MULTIPLY3", _ 
                4, _ 
               "Number1,Number2,Number3", _ 
                1, _ 
                "My Functions2", _ 
                "Multiplies three numbers", _ 
                """First number"",""Second number"",""Third number""", _ 
                "CharNextA") 

End Sub 

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 & ")" 

End Sub 

Sub Auto_Close() 

 Dim FName 
 Dim i As Integer 
 FName = Array("DIVIDE", "MULTIPLY2", "MULTIPLY3") 
 For i = 1 To 3 
   With Application 
     .ExecuteExcel4Macro "UNREGISTER(" & FName(i) & ")" 
     .ExecuteExcel4Macro "REGISTER(" & Lib & ",""CharNextA"",""P"",""" & FName(i) & """,,0)" 
     .ExecuteExcel4Macro "UNREGISTER(" & FName(i) & ")" 
   End With 
 Next 

End Sub

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,


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext