Registering a User Defined Function with Excel

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.

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 UDF’s 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 an ancient XLM macro sheet together with some trickery invented by Laurent Longre.

It enables you to set your own category:

Custom category created by technique described in the article

and your own descriptions for each argument:

Argument descriptions created by technique described in the article

Enjoy!

Regards,

Jan Karel Pieterse
JKP Application Development Services

Posted in Uncategorized

13 thoughts on “Registering a User Defined Function with Excel

  1. When using as part of an .xla it has some side effects.

    The routine HandleFunctionRegistration gets error 9 Subsript out of range in the for…next loop as auFuncData is nothing. Then in the custom category the function is listed twice. Once as the Function name and once as addinname!Function.

    If havn’t really had time to figure out why the error happens in an xla but not a xls.

  2. I remember spending many, many hours on this problem. It was several years ago, and I’ve forgotten all of the specifics. I only remember my conclusion: It works fine — as long as no other add-ins use custom categories. If so, everything gets screwed up.

    I haven’t revisited this topic in a long time. Does this technique really work, even if other add-ins use custom categories?

  3. I have to revisit this one too John, I hand’t tested it by using the thing as an add-in. When I did, it crashed Excel when I tried to enter the functions into Excel (XP). Not every time, but even once is too many in my opinion.

    I’ll take a further look next week, but I suspect this is a dead-end street after all.

    I’ll put up a warning message in the article just to be sure.

  4. I have a way around ExecuteExcel4Macro’s 255 char limit, entirely from vba without the macrosheet. No problems when invoked from addin. I’m working on a documenting and testing my code.

    John, afaik Category problems are caused by Analysis Toolpak, some of those functions are hardcoded into Category 15 (Engineering). I’ll try to find a trick for that too.

    If interested drop me a mail, else i’ll post here soon.

  5. I’ve contacted Jan Karel Pieterse, and we’re working on fitting this into a class module
    which will be published on his site later.

    This method appears to be stable. No crashes on unregistering, or closing the workbook without unregistering. Can be called from an addin or without activeworkbook. Descriptions DO remain in the Function Wizard when the book is closed, similar to starting and unloading ATP addin.

    Using Application.MacroOptions requires an activeworkbook and stores the description and category number in 2 hidden lines in the VBA module.(the category NAME is stored in the workbook itself) This hardcoding may conflict with other addins using the same numbers. This approach does not.

    My “trick” used to circumvent the 255 character limit is all based on storing the argument values in Excel’s “Global namespace”. (Laurent Longre uses the term “Hidden Namespace”). These names exist at application level and can be reached by all workbooks and addins using SET.NAME and GET.NAME macro functions

    Note the “trick” also works very well with the Evaluate method, which has a similar 255 char limit..

    here comes the code… hopefully ungarbled when HTML is done with it.

    Jurgen

    Option Explicit

    ‘This is the function we want to ‘describe’ in the Function Wizard
    ‘Note the function is Private to avoid appearance in ‘User Defined’ category
    Private Function Occurs(sText$, sPhrase$)
    With Application
    Occurs = (Len(sText) – Len(.Substitute(sText, sPhrase, _
    “”))) / .Max(1, Len(sPhrase))
    End With
    End Function

    ‘This is the sample procedure to enter the function and argument
    ‘descriptions for use in the Function Wizard
    Sub DescribeIT()
    ‘keepITcool 2006-02-22

    ‘Notes:
    ‘Argument 1 must be a Dll name (as used in API declarations)
    ‘Argument 2 must be a unique procedure with the dll
    ‘Argument 3 should not be changed
    ‘Argument 6 should not be changed

    ‘For documentation on REGISTER()
    ‘see macrofun.hlp and/or excel97sdk.chm

    ‘Store the arguments in Excel’s name space using SET.NAME
    ‘————————————–
    SetGlobalName “arg1?, “user32.dll” ‘module_text
    SetGlobalName “arg2?, “CharNextA” ‘procedure
    SetGlobalName “arg3?, “P” ‘type_text
    SetGlobalName “arg4?, “Occurs” ‘function_text
    SetGlobalName “arg5?, “Text,Phrase” ‘argument_text
    SetGlobalName “arg6?, 1 ‘macro_type
    SetGlobalName “arg7?, “Cool Functions” ‘category
    SetGlobalName “arg8?, “” ‘shortcut_text
    SetGlobalName “arg9?, “” ‘help_topic
    ‘Function and Argument descriptions
    SetGlobalName “arg10?, “returns the number of times ” & _
    “a phrase occurs in a text.”
    SetGlobalName “arg11?, “is a (cell reference to) ” & _
    “the text to be searched”
    SetGlobalName “arg12?, “is a (cell reference to) ” & _
    “the phrase to search for.”

    ‘Now Register your function
    ‘—————-–
    Application.ExecuteExcel4Macro _
    “REGISTER(arg1,arg2,arg3,arg4,arg5,arg6,arg7,arg8,arg9,arg10,arg11,arg12)”
    ‘Block access to the function pointer (could possibly be CALLed in xl97)
    Application.ExecuteExcel4Macro “SET.NAME(arg4,0)”

    ‘Now remove the argument names
    ‘——————–
    Dim i%
    For i = 1 To 12
    SetGlobalName (“arg” & i)
    Next
    End Sub

    Sub UnDescribeIT()
    Application.ExecuteExcel4Macro _
    “UNREGISTER(REGISTER.ID(“”user32?”,””CharNextA””))”
    End Sub

    Function SetGlobalName(sName As String, Optional ByVal vValue)
    ‘Wrapper function for the SET.NAME xlm command
    ‘If called without the optional vValue the name is DELETED.
    Dim sCmd$
    Select Case True
    Case IsMissing(vValue)
    sCmd = “SET.NAME(“”” & sName & “””)”
    Case TypeName(vValue) = “String” Or IsEmpty(vValue)
    sCmd = “SET.NAME(“”” & sName & “””,””” & vValue & “””)”
    Case IsArray(vValue)
    ‘Arrays s/b passed as a string like ‘{1,2,3}’
    SetGlobalName = CVErr(xlErrValue)
    Exit Function
    Case Else
    With Application
    vValue = .Substitute(CStr(vValue), .DecimalSeparator, “.”)
    End With
    sCmd = “SET.NAME(“”” & sName & “””,” & vValue & “)”
    End Select
    SetGlobalName = Application.ExecuteExcel4Macro(sCmd)
    End Function

  6. be sure to replace “starting and ending double quotes” and ‘opening apostrophe
    with their ascii brethren

  7. In my case, this code works fine for one function. However, when I enter 2 functions in the same category, they both get the same number of parameters, and the same function and parameter descriptions.

  8. Further on my remark above: I realised that the Excel4 routine is based on the registration of a DLL function. Therefore you need a different DLL function for each UDF (arg2 in the code of KeepITcool above).

    If you want to register a number of UDF functions this is a bit tedious. So I created a text file with all functions in user32.dll. I have replaced “CharNextA” in:

    SetGlobalName “arg2?, “CharNextA”

    with a function that returns the last non-used DLL function from the text file.

    The only non-standard VBA in my addition to the code is the use of the “hidden Excel namespace”. I keep a counter in that namespace using SET.NAME and GET.NAME. The counter is up to date for the complete Excel session, but is reset to zero when Excel is closed. Just as the Doctor ordered.

    Everything works fine now. Still, I worry a bit about programming practice: we are using an obsolete fuction, that does our job becasue it contains a bug(correct me if I am wrong). What about new future versions of Excel?

    Please mail at franswes@xs4all.nl if you want a copy of the text file with the DLL functions, and the VBA code I have completed for this purpose.

  9. Is there any new wisdom on this topic? I’ve created an automation addin with C# and it would be nice to see a category other than my namespace.class, and to see real help info.

    Has anyone translated Jurgen’s VBA into a .NET class? I’ll try to translate if required but it would be nice to avoid effort duplication. I’ve been to Laurent’s site but his offering doesn’t seem to support automation either.

    Does Excel 2007, maybe with VSTO, recognize development in this area as a little more than a second-class citizen?

    Thanks!

  10. to KeepItCool: I still couldn’t figure the trick getting around with Evaluate method, and still stuck with 255 characters.

  11. I’ve built a XLA/XLSM solution working perfectly with more 20 UDF including arguments and help file.

    I want to transfer that on C# Visual Studio 8 Express Edition without DNA (pure Microsoft C#).

    This work perfectly with registering each UDF, but I dont know how to register with arguments and help !

    The code used and published in various site and blogs is :

    ===============================================================
    [ComRegisterFunctionAttribute]
    public static void RegisterFunction(Type type)
    {

    Registry.ClassesRoot.CreateSubKey(

    GetSubKeyName(type, “Programmable”));

    RegistryKey key = Registry.ClassesRoot.OpenSubKey(

    GetSubKeyName(type, “InprocServer32?), true);

    key.SetValue(“”,

    System.Environment.SystemDirectory + @”mscoree.dll”,

    RegistryValueKind.String);
    }

    [ComUnregisterFunctionAttribute]
    public static void UnregisterFunction(Type type)
    {

    Registry.ClassesRoot.DeleteSubKey(

    GetSubKeyName(type, “Programmable”), false);
    }

    =======================================================================


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.