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

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 “Excel User Defined Functions”

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