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. Looks good Jurgen.

   Any similarity with what I tried to piece together?

6. 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

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

8. 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.

9. 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.

10. 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!

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

12. toKeepItCool: show us the trick getting around with Evaulate 255 char limits as well.

13. 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.