How to describe your Excel User Defined Function

In the previous entry (http://wp.me/pW1Iy-f9) we talked about “Excel User Defined Functions” and we created a very simple example of such custom function called Half. This function receives a single numeric argument and calculates its half value.

It this entry I will be explaining how to add descriptions to the function and its arguments so it will look and feel like any other pre-built function. There are some limitations and I will describe that as well.

The first thing to do is to open a VBA window. To do that, select the “Developer” tab and click on the left-most button labeled “Visual Basic”.

In the editor just type the following text:

Sub Half_Descriptor()
    Dim argDescription(1) As String
    argDescription(0) = "A numeric argument"
   
    Application.MacroOptions Macro:="Half", _
        Description:="Recieved a single argument and returns its half value", _
        Category:="My Category", _
        StatusBar:="Returns the half value of a numeric argument", _
        ArgumentDescriptions:=argDescription
End Sub

The Visual Basic editor will take care if understanding that this is a new Sub and will treat it correctly. The new Sub is very simple and is making one call to the Excel API to a method called Application.MacroOptions. This Method accepts the name of the UDF, the description, optionally help and an array of argument descriptions. It also can accept a name of a Category or a number representing one of the out of the box Categories for easier hierarchy to find your new Function.

More information can be found here:

http://msdn.microsoft.com/en-us/library/ff838997(v=office.14).aspx

Next step is to run the function from the VBA developer environment. To do that, make sure that your mouse pointer is pointing to the code of the Help_Descriptor sub, and click F5. It will run the Sub and you are done.

Now the new function will appear in the expression Function wizard, i.e. when you flick on the following button:

2013-09 Excel Describe User Defined Functions.2

Now the Half function is there:

2013-09 Excel Describe User Defined Functions.3

And also there is a wizard for each argument that looks like this:

2013-09 Excel Describe User Defined Functions.4

All of that is great. But here comes the bad news. There are still limitations. The one thing that bugs me most is that I could not find a way to add the description also to the tooltip of the formula typing box. Same as there is one for out of the box formulas such as Average:

2013-09 Excel Describe User Defined Functions.5

This is something that will be a great addition for a future Microsoft Excel release…

Advertisements

Author: dave

Consider myself kid in soul and naive by choice. I am interested in people, technology and business and thrive when they all work together. My favorite quote and motto is that “You can fool some people some times but you cant fool all the people all the time” ― Bob Marley

2 thoughts on “How to describe your Excel User Defined Function”

  1. Thanks Dave,
    This works brilliantly for my purposes.

    My custom function VBA code is included in the workbook and this MacroOptions code must run whenever the workbook is opened. Never have to run manually anyway.

    Note for Andy – you can include multiple argument descriptions because ArgumentDescriptions is an array variable. One example below:

    Sub temperatureWph_Descriptor()
    Dim argDescription(1) As String
    argDescription(0) = “pressure (bar a)”
    argDescription(1) = “enthalpy (kj/kg)”

    Application.MacroOptions Macro:=”temperatureWph”, _
    Description:=”Returns temperature of superheated steam given pressure and enthalpy”, _
    Category:=”Steam Properties”, _
    StatusBar:=”Returns temperature of superheated steam given pressure and enthalpy”, _
    ArgumentDescriptions:=argDescription
    End Sub

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s