16 thoughts on “Registering a UDF Take 2

  1. This is awesome! Thanks guys.
    The only piece that’s missing now is passing the info to the Function Argument ToolTips.

    However, I’m lucky to work at a small firm with control over our technology adoption. We’ll be getting XL IntelliSense as soon as we can with Office 2007.
    David Gainer talks about it on his blog here:

    The key points being:
    Here is a complete list of the items that will appear in the Formula AutoComplete drop down:

    Excel functions – this includes all functions in the Excel library
    User defined functions – Functions defined by the user either through VBA, automation add-ins, or .XLL’s show up as functions
    Defined Names – All defined names are surfaced
    Enumerated Arguments – Some enumerated arguments (like the function_num argument in the example) will have auto complete support
    Table Structure References – More on this in post coming soon about improved table support.

  2. Just read through the comments on David’s blog and saw that you had some commentary back and forth with him Jan Karel. Sorry to point you to something you obviously already knew about. :-D

  3. Stephen and JK

    I’m not entirely sure of Stephen’s question, but will give it a try.

    The “Registration” code exposes some functions thru the function wizard (even if private).
    (this code can be anywhere and does not need a reference)

    The “Function” code with the exposed functions does NOT need to be in either the calling worksheet’s parent workbook nor in the workbook containing the “registration” code.
    Nor does it need a reference.

    If the functions are in an xla addin: no need to use qualified function names during the registration process (even if not in same wkb as registration code) ELSE you must use a qualified function name.

    The “Caller” workbook needs the functions to be loaded, and alas will create an external file link.
    (a reference is optional to force loading the “Functions”)

    Does this answer it?
    Greetz, Jurgen

  4. Hi Jurgen,

    Assume we have an addin FuncLib.xla, which contains the VBA UDFs and the code to register them in the function library.

    We have another sheet, caller.xls which wants to use a function defined in FuncLib.xla. If I’m in caller.xls and use the function wizard to enter the function, does it work OK? If I enter the function without using the function wizard, does it work OK?

    Using the technique we described in PED, the above only works if the caller.xls VBProject references the funclib.xla VBProject – which is a major downside.



  5. Stephen,

    I have implemented this for the FastExcel XLA functions using a similar approach based on Jurgen’s technique and it works fine in the scenario you describe in all Excel versions from 97 to 2003 (have not tried it with XL12 yet).
    The only drawback I can see is that Excel still creates a link to the XLA in Caller.xls.


  6. Guys,
    no reference is needed in the caller.xls BUT the caller will have links to the xla.

    I’ve successfully tested this method with the UDF’s in a VB6 ActiveX dll.
    No references and no links!

    Once the the dll is connected and the registration process completed it works as expected.
    (OnConnect sets a global application object and calls the UdfHelper class… and even private functions in the dll can be registered)

    BUT if the dll connects because the user clicks the (modal) function wizard
    there’s a threading? conflict when the application object is called from the dll
    and registration fails..

    Anyone ideas on how to force connection of the dll, or delay the function wizard?
    pls contact me via email.


  7. addendum:
    a private function in a dll can de “described” but cannot be called from a worksheet.
    this gives some problems with regard to the function category.

  8. addendum 2:
    KB 285337 says: “there is no way to specify function descriptions, argument descriptions, or help for Automation Add-in functions in the Function Wizard.

    Not true! I can now add full descriptions to functions in vb dlls!
    I’ve solved the loading issues for UDF’s in automation addins, by using a timer to check Excel’s Ready state. Hiding the Function Category of the “original” is still a nono.

  9. I absolutely love the end results of this method. However, I’m curious if it can only be employeed with UDF’s contained in a DLL. Can this method be adjusted to work with XLA’s?

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

Leave a Reply

Your email address will not be published.