As you may recall, I’ve written about registering User Defined Functions before.
The problem with the trick I used in that post, was (and this isn’t trivial): it crashed Excel.
KeepITCool came up with yet another trick to make this work, which I have described in
this article.
Enjoy!
Regards,
Jan Karel Pieterse
JKP Application Development Services
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:
http://blogs.msdn.com/excel/archive/2005/10/19/482826.aspx
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.
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
No problem Jeremy.
Besides, as all Office MVP’s I am on the Office 2007 beta program, so I’ve worked with it already.
Hi JK. Does this version work with addins without referencing them, or only when the registered function is used from within the same workbook?
Hi Stephen,
I have not had time to test that yet. Maybe Jurgen has?
Stephen,
Do you mean whether the registration code needs to be in the book with the UDF?
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
Jurgen:
If no-one says it works otherwise, I’ll add this to the site.
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.
Regards
Stephen
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.
regards
Charles
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!
However:
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.
keepITcool
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.
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.
Jurgen,
Is it possible for You to make the automation add-in and its VB-project available?
Kind regards,
Dennis
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?
The method was designed for that, so yes.