April 23, 2008

How do I… Create a user-defined function in Microsoft Excel

by Mark Kaelin | Aug 17, 2004 7:00:00 AM

Takeaway: Microsoft Excel allows users to create their own user-defined functions. Using the power of Visual Basic for Applications (VBA), you can code complicated formulas only once and use them again and again in your spreadsheets and workbooks. These functions can be used just like any built-in function already available in Excel. Here's how you create one.

Sometimes you find yourself in a situation where you can't get Microsoft Excel to do exactly what you want it to do without coding a complicated formula over and over again. In such a scenario, you often have to spend an unacceptable amount of time finding circular logic errors or fixing syntax problems. Using the power of Visual Basic for Applications (VBA), you can code that complicated formula only once and use it again and again in a user-defined function.

Build your function

According to Microsoft, to create a user-defined function in Excel, you first call up the Visual Basic Editor, which is located in the Tools | Macros menu. Once in the editor, navigate to Insert | Module to open a blank module screen. This is where you'll type in your code. (See Figure A.)

Figure A

Visual Basic Editor

For example, let's assume you want to calculate the commissions earned on fees charged, but you want to give a break to your best clients. You can type in an IF statement in each cell in your workbook, or you can create a single function with an If-Then-Else statement.

Here is the code to create our user-defined Commission() function:

Function Commission(Fee)
If Fee <= 1000 Then Commission = Fee * 0.1 Else Commission = Fee * 0.05
viagra best prices /> End Function

This function will charge a commission of 10 percent for fees less than or equal to $1,000 and 5 percent on all other fees.

While the commission calculation formula would be simple enough to include in each cell individually, it could quickly become a quagmire if we had a more complicated set of criteria to run through. In such instances, a user-defined function can save us lots of trouble.

Our user-defined function operates the same way as any other Excel function operates, as you can see in Figure B. The cells in Column C contain the Commission() function and calculate the commission based on the criteria provided by the user-defined function we created.

Figure B

Example calculation

With this technique, you can create very complicated functions and applications without resorting to extended formulas that reside within the cells themselves. This means your Excel applications can be easier to follow and adjust in the future.