Add Muscle to Excel: User Defined Functions Empower Calculations
Jeff Lenning · Journal of accountancy online/Journal of accountancy · 2007
Have 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 region receive a base commission of $1,000 plus 10% of their total sales. Sales reps in the 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 then Commission = 1000 + sales*10% If region is 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 if region = North then temp = 1000 + sales * 0.1 end if if region = South then temp = (sales-budget)* 0.05 end if commission = temp End function Notice the similarities between the initial statement we composed and the final code. Now Ill 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 0 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 ? …