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