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.


Click this tag search to find other How do I… articles and downloads.


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