Microsoft Office Tutorials and References
In Depth Information
Function Examples
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, at times, it may return an incorrect result. Excel’s calculation
engine can’t account for ranges in your code that aren’t 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
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).
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 isn’t defined.
A function with two arguments
Imagine that the aforementioned hypothetical sales managers implement a new policy to help
reduce turnover: The total commission paid is increased by 1 percent for every year that the
salesperson has been with the company.
I modified the custom Commission function (defined in the preceding section) so that it takes
two arguments. The new argument represents the number of years. Call this new function
Commission2 :
Function Commission2(Sales, Years)
‘ Calculates sales commissions based on
‘ years in service
Const Tier1 = 0.08
Const Tier2 = 0.105
Const Tier3 = 0.12
Const Tier4 = 0.14
Select Case Sales
Case 0 To 9999.99: Commission2 = Sales * Tier1
Case 1000 To 19999.99: Commission2 = Sales * Tier2
Search JabSto ::

Custom Search