Add Muscle to Excel
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:
Commission = 1000 + sales*10%
Commission = (sales-budget)*5%
‘computes commission based on regiondim temp as integertemp = 0
if region = “North” then temp = 1000 + sales * 0.1end if
if region = “South” then temp = (sales-budget) * 0.05end ifcommission = temp
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:
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:
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:
|if region||=||“North” then|
|=||1000 + sales * 0.1|
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:
|if region||=||“South” then|
|=||(sales-budget) * 0.05|
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:
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
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.
Jeff Lenning, CPA, CITP, is the founder of Click Consulting, Seal Beach, Calif., which specializes in network support and application development.
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.