Add Muscle to Excel

ave you ever wanted to do something in Excel only to find that it has no function for that task? That shouldn’t stop you, because Excel has a built-in ability to help you customize your own functions.

The key is Excel’s macro tool, which uses software code written in Visual Basic for Applications, or VBA. If you’ve never prepared VBA code, don’t worry. After I walk you through the steps, you’ll see how easy it is to use macro functions to perform calculations inside Excel formulas. With them you can perform calculations that would otherwise be impossible—or at least very difficult.

AUTOMATE A TASK
Consider this example: One of your clients, Ocean Ridge Technology, has a sales commission plan that is so unique there is no way Microsoft could have included the necessary worksheet function in Excel. However, since you prepare Ocean Ridge’s payroll and related monthly commission computations, automating this monthly task would speed your work significantly. Follow along as I go through each step to create the unique function.

In Ocean Ridge’s commission plan, sales reps in the North region receive a base commission of $1,000 plus 10% of their total sales. Sales reps in the South region receive a monthly commission of 5% of the amount by which their sales exceed budget. So our goal is to create a function I’ll call =commission() that calculates the figures.

The first step is to think through the process and then document each step you’d use to calculate the commission. For example, the following would be a useful statement:

If region is North then
Commission = 1000 + sales*10%
If region is South then
Commission = (sales-budget)*5%

Here is the VBA code for the function.

Function commission(region,sales,budget)
‘computes commission based on region
dim temp as integer
temp = 0

end if

end if
commission = temp
End function

Notice the similarities between the initial statement we composed and the final code. Now I’ll walk you through each line so you’ll see how the code is composed so you can compose your own code later.

The first line is:

Function commission(region,sales,budget)

I used the keyword Function rather than the usual macro keyword Sub to signal to Excel how I intend to use the code. The keyword Sub tells Excel that this code is a macro and should appear in the Macros list and run when activated by the user. The keyword Function tells Excel that it should show up in the User Defined Functions category and run when summoned through a worksheet cell formula. I gave the function a name, commission() , that describes the calculation and is easy to remember. That is the name you will use to refer to the function in your worksheet cell formula. Later you’ll see that the brackets () attached to commission will contain the function arguments and look like this:

=commission()

Tip: Avoid using function names that are similar or equal to existing Excel function names, like Sum() .

Moving on to the next line:

‘computes commission based on region

Notice that the line begins with a single quote; that punctuation instructs Excel to ignore what follows, which in this case is only a comment you may wish to add to help you identify the function.

The next line:

dim temp as integer

The keyword dim declares, or defines, a variable, which in this case is temp . The word temp is our variable name; values are assigned to it during the execution of the code. You have some freedom when setting your variable name, but it must start with an alphabetic character and should contain no spaces or special characters (such as ? or >). The keyword integer tells Excel that the type of data the variable temp will contain is an integer—that is, a whole number with no decimals. Other types of data include string (text) and date , among several others.

The next line:

temp = 0

This assigns the value of 0 to the variable temp . It’s generally a good idea to assign a default value to every variable you set up. By doing so, you avoid unexpected errors and conditions in your code later on, and it keeps your code nice and clean.

The next lines:

end if

The if command alerts Excel that it will be asked to process a standard if/then command—that is, if the region is North, then the subsequent VBA code should be executed. But if the condition is not true, then the code is to be skipped.

The next lines:

end if

These lines do the same for the South condition as the one above did for the North condition.

The next line:

commission = temp

This line tells Excel which value to return to your worksheet cell. Assigning a value to the function name ( commission ) instructs Excel to put that value (that is, the results of your computation) into the worksheet cell formula.

The last line tells Excel the code has finished:

End function

ADD THE FUNCTION TO EXCEL
Now that the code is prepared, it has to be installed in an Excel module. Start by opening the Visual Basic Editor by clicking on Tools , Macro , Visual Basic Editor . Then click on Insert , Module (see screenshot below).

Enter the code by typing (or copying) it into the space in the module just as we’d prepared it in the word processor (see screenshot below).

PUT THE FUNCTION TO WORK
Now that the custom function code is written, it can be used the same way you would apply any other Excel function. Using the spreadsheet shown in the screenshot below as an illustration, you can see how I entered the function name in the
formula bar with the function arguments inside parenthesis: =commission(B5,C5,D5) .

Inserting worksheet functions into cells is always accomplished either by manually typing them into a cell preceded by an equal sign, or by using the Insert Function, which is accessed by clicking on Insert, Function. This is true as well for custom functions; after selecting Function, click on User Defined and they appear for your selection (see screenshot).

Insert Function also provides an easy way to enter function arguments by either typing in the cell location manually (B5) or by clicking the worksheet cell B5, as shown below.

Additional resources for VBA programming are available through the Visual Basic Editor help menu and at www.microsoft.com. If you do a Google search with the key words VBA custom functions , hundreds of sites and code samples will turn up.

Although it takes a little time to prepare a custom function, once it’s done it becomes an enormous timesaver. Now that you’ve seen how easy they are to create, you may want to start creating even more complex functions.

Jeff Lenning, CPA, CITP, is the founder of Click Consulting, Seal Beach, Calif., which specializes in network support and application development. His e-mail address is jeff@clickconsulting.com, and his Web site is www.clickconsulting.com.