A function with one argument

This section describes a function for sales managers who need to calculate the commissions earned by their sales forces. The calculations in this example are based on the following table:

> Open table as spreadsheet

Monthly Sales

Commission Rate









Note that the commission rate is nonlinear and also depends on the month's total sales. Employees who sell more earn a higher commission rate.

There are several ways to calculate commissions for various sales amounts entered into a worksheet. If you're not thinking too clearly, you might waste lots of time and come up with a lengthy formula such as this:

=IF(AND(A1>=0,A1<=9999.99),A1*0.08, IF(AND(A1>=10000,A1<=19999.99),A1*0.105, IF(AND(A1>=2 0000,A1<=39999.99),A1*0.12, IF(A1>=40000,A1*0.14,0))))

This is a bad approach for a couple of reasons. First, the formula is overly complex, making it difficult to understand. Second, the values are hard-coded into the formula, making the formula difficult to modify.

A better (non-VBA) approach is to use a lookup table function to compute the commissions. For example, the following formula uses vlookup to retrieve the commission value from a range named Table and multiplies that value by the value in cell A1.


Yet another approach (which eliminates the need to use a lookup table) is to create a custom function such as the following:

Function Commission(Sales) Const Tier1 = 0.08 Const Tier2 = 0.105 Const Tier3 = 0.12 Const Tier4 = 0.14 ' Calculates sales commissions Select Case Sales

Case 0 To 9999.99: Commission = Sales * Tier1 Case 1000 To 19999.99: Commission = Sales * Tier2 Case 20000 To 39999.99: Commission = Sales * Tier3 Case Is >= 40000: Commission = Sales * Tier4 End Select End Function

After you enter this function in a VBA module, you can use it in a worksheet formula or call the function from other VBA procedures.

Entering the following formula into a cell produces a result of 3,000; the amount - 25,000 - qualifies for a commission rate of 12 percent:


Even if you don't need custom functions in a worksheet, creating Function procedures can make your VBA coding much simpler. For example, if your VBA procedure calculates sales commissions, you can use the exact same function and call it from a VBA procedure.

Here's a tiny procedure that asks the user for a sales amount and then uses the Commission function to calculate the commission due:

S ub CalcComm()

Dim Sales as Long

Sales = InputBox("Enter Sales:")

MsgBox "The commission is " & Commission(Sales) End Sub

The CalcComm procedure starts by displaying an input box that asks for the sales amount. Then it displays a message box with the calculated sales commission for that amount.

This Sub procedure works, but it is rather crude. Following is an enhanced version that displays formatted values and keeps looping until the user clicks No (see Figure 10-4).

Figure 10-4: Using a function to display the result of a calculation.

Sub CalcComm()

Dim Sales As Long

Dim Msg As String, Ans As String

' Prompt for sales amount

Sales = Val(InputBox("Enter Sales:", _ "Sales Commission Calculator"))

' Build the Message

Msg = "Sales Amount:" & vbTab & Format(Sales, "$#,##0.00")

Msg = Msg & vbCrLf & "Commission:" & vbTab

Msg = Msg & Format(Commission(Sales), "$#,##0.00")

Msg = Msg & vbCrLf & vbCrLf & "Another?"

' Display the result and prompt for another

Ans = MsgBox(Msg, vbYesNo, "Sales Commission Calculator") If Ans = vbYes Then CalcComm End Sub

This function uses two VBA built-in constants: vbTab represents a tab (to space the output), and vbCrLf specifies a carriage return and line feed (to skip to the next line). VBA's Format function displays a value in a specified format (in this case, with a dollar sign, comma, and two decimal places).

Use Arguments, Not Cell References

All ranges that are used in a custom function should be passed as arguments. Consider the following function, which returns the value in A1, multiplied by 2:

Function DoubleCell()

DoubleCell = Range("A1") * 2 End Function

Although this function works, there are times when it may return an incorrect result. Excel's calculation engine cannot account for ranges in your code that are not passed as arguments. Therefore, in some cases, all precedents may not be calculated before the function's value is returned. The DoubleCell function should be written as follows, with A1 passed as the argument:

Function DoubleCell(cell)

DoubleCell = cell * 2 End Function

In both of these examples, the Commission function must be available in the active workbook; otherwise, Excel displays an error message saying that the function is not defined.

0 0

Post a comment