Registering a User Defined Function with Excel

Pages in this article

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.