In Excel 2007, if you start to enter a formula, such as =I, in
the formula bar, it immediately provides you with intellisense of all functions
starting with I. As with normal intellisense, the more you type, the more
precise the list becomes. I am sure that we all agree that intellisense is a
wonderful feature and its addition to formulas in Excel 2007 is a welcome step.
I recently had a client who was building some UDFs, each of
which would start with a particular id. They rightly thought that it would be a
great idea to be able to type their id and the list of their UDFs would come up
in intellisense. Sound thinking, but unfortunately it doesn’t work, because
UDFs do not show up in the intellisense. Why? Only MS knows I am afraid, I
certainly don’t, but suspect it is because MS do not care about VBA anymore.
Anyway, I had read an article by Jan Karel Pieterse some
months previously about registering UDFs. The objective of registering the UDFs
in this article was to categorise the UDFs, and provide help in the function
wizard. It occurred to me that perhaps if the UDFs were registered in this way, with Excel 'owning' them,
they might show in intellisense. I revisited the article to check that my
recollection was correct, and a quick test of this with a few of our UDFs in
Excel 2007 showed my assumption to be correct, so we had the perfect solution.
The article in question can be found at Registering A User Defined Function With Excel.
The technique is not Jan Karel’s, it originates from Laurent Longre and KeepItCool,
but Jan Karel has provided a real service in documenting and publishing this
technique. The link also provides a download file for you to get started.
One thing to note about this technique is that if your UDFs
are public, they will show up in you defined category, but will also continue
to show up in the ‘User defined’ category. Believe it or not, this can be
overcome by making the UDFs private, they still function correctly, still show
in intellisense, but are not shown in the ‘User defined’ category. The article
does state this, but I mention it here because I missed it on first reading,
and it is so useful.
The example file allows for just 15 UDFs to be
registered, and has a small error in the example in that the dll procedure
CharPrevA is in the list twice. If you try to register a UDF on line 6, you
will get an error. We had a need for a lot more UDFs, and so we needed more dll
procedures. As this technique uses the Excel 4 macro language's register
function to register a function residing within any system dll, using an alias
name which is identical to the name of a UDF, we needed to find more dll procedures
that we could add to the list. Another wonderful little utility from NirSoft, the
DLL Export Viewer, gave us a list of all of the exported functions in
user32.dll, so we were able to add to the list, and now have over 40 UDFs in
our own category, all showing in intellisense. Wonderful!
Here is a picture of the technique in action. First my setup
And this is the intellisense in action
The next step is to add more. There are two choices I
believe, the first is to find another system dll that we can utilise in this
manner, I admit to being wary of using functions such as GetFileAttributesW in
user32.dll, or write our own dll. I think the latter way is what I will be