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:
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:
Now the Half function is there:
And also there is a wizard for each argument that looks like this:
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:
This is something that will be a great addition for a future Microsoft Excel release…