Excel User Defined Functions

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:

user-defined-functions-1

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

user-defined-functions-2

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

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”:

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:

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

Type the following code:

Function Half(value)
  Half = value / 2
End Function

If all well the code should look something like the following:

user-defined-functions-6

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

user-defined-functions-7

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

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:

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


2 thoughts on “Excel User Defined Functions”

Leave a Reply

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