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…

Excel User Defined Functions

Excel has a rich set of pre-defined functions: Functions for math calculations, finance calculations, date manipulations and many more. But as rich as it gets, there are always cases you need to build your own special function and you want to use it in the same ease of use as you would use the pre-defined functions.

Luckily, Excel supports the development of custom functions or “User Defined Functions” as Microsoft calls them. In this blog entry I will demonstrate the basics of this concept using a simple example: A custom function called “Half” that accepts one parameter and divide it by two.

The first thing we will need to do is to expose the “Developer” options that are needed for development of user defined function. Right-click on the Excel upper ribbon and select the “Customize the Ribbon” option:

Excel User Defined Functions.1

Then make sure the “Developer” main tab is checked.

Excel User Defined Functions.2

At this stage a new “Developer” menu should appear and look something like the following:

Excel User Defined Functions.3

In the newly added “Developer” Ribbon click on the “Macros” button to create our new function. A pop-up window will be opened. It is a little bit strange window. When one first open it and there are no already defined macros, all the buttons are grayed and you may find yourself asking “What now?”. So don’t worry, just start typing the name of the function in the “Macro Name” field at the top of the window and the “Create” button will get active. In our case we want to create a function called “Half”:

Excel User Defined Functions.4

Click on the “Create” button and a new window will get opened. This is the developer environment for user defined functions (and other macros).  The language we will use is called “Visual Basic for Applications” or VBA and you can read more about it here:

http://office.microsoft.com/en-us/excel-help/creating-custom-functions-HA001111701.aspx

If you follow the instructions thus far your screen should look like this:

Excel User Defined Functions.5

For our use case, we will change the type of the macro from Sub to Function. We will also accept a parameter called “Value” and finally we will divide “Value” by 2 and return the new value by assigning it to an implicit parameter that has the same name as the function. In our case “Half”.

The code will look something like the following:

Excel User Defined Functions.6

Switch back to the Excel windows and let’s give it a try:

Excel User Defined Functions.7

Try the function with constant values or with reference to other cells:

Excel User Defined Functions.8

You have just completed the definition of your first User Defined Function in Excel.

The last weird thing happens when you try to save the Excel file. You will get a notification like the following:

Excel User Defined Functions.9

Click “No” and make sure that you save the Excel file as a Macro enabled file. In simple words, file type should be “xlsm” and not the regular “xlsx”.

More information and examples can be found here:

http://office.microsoft.com/en-us/excel-help/create-your-own-worksheet-functions-HA001054846.aspx