Custom UDFs







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


6 thoughts on “Custom UDFs

  1. Bob – my UDFs appear in the intellisense list (in 2007)unless they are in another file. I don’t recall any setting for this, and I couldn’t find one. Is this different behaviour to what you found, or were the UDFs in a different file in your case?

  2. Doug,

    Yes, they were in an addin. I forgot to mention that bit (I assumed everyone is writing addins ).

  3. Thanks for sharing this post. This is a very helpful and informative material. Good post and keep it up. Websites are always helpful in one way or the other, that’s cool stuff, anyways, instant loans a good way to get started to renovate your dreams into the world of reality.

  4. I have tried the above method, which works fine.

    1. Is it possible to have UDF screentips as per native Excel functions? E.g. type in “=SUM” in an active cell, and a screentip of “add all the numbers in a range of cells” appears.



  5. No existe ningun sitio en la internet, en donde se presente un ejemplo completo de uso del Addin FunCustomize de Laurent Longre. En cuanto a la localizacion del archivo de ayuda para la UDF. Ni tampoco se detalla el software que debe emplearse para crear ese archivo de ayuda.

    De no ser correcta mi comentario, indicarmelo a mi email

Leave a Reply

Your email address will not be published. Required fields are marked *