Databases Reference
In-Depth Information
The following will then be printed:
result = 0.75
Here the returned value of the function (which is a double precision number) is assigned to the
variant variable result, which is then printed to the immediate window. When utilizing functions,
it is important to understand that the values they return are of a certain type. Therefore, when a
variable is set equal to the value of a function, the variables type must be the same as that which
will be returned by the function (preferred), or the variable must be of type variant (less desirable
method — see Chapter 3).
Functions can also be utilized to
replace
a passed parameter in a subroutine. Consider the
following subroutine:
Sub TestScore(Score As Single)
Debug.Print "Your Test Score is: "; Trim(Score * 100); "%"
End Sub
Here, a score is required that is a decimal number between 0 and 1. In this instance, such a score
would be the number of correct answers divided by the total number of problems in a test. Thus,
could be replaced by the earlier function
Suppose a student were to answer
Score
DivideBy.
82 of 100 answers correctly. The subroutine
could be called in this fashion:
TestScore
TestScore(DivideBy(82,100))
Your Test Score is: 82%
Notice that the passed parameter
is only dimensioned as
The consequence
Score
Single.
of this is that the double precision value returned by the
function will be truncated to
a single precision value when the single precision passed parameter Score is assigned the returned
value from the double precision function
DivideBy
DivideBy.
One final aspect to consider when programming in Excel VBA, which is not an issue when
programming in regular Visual Basic, is that the return value of a function can also be assigned to
a cell within a Worksheet. Thus statements such as this are valid in Excel VBA.
Cells(1,3).Value = DivideBy(500,10)
Here a value of 50 (500/10
=
50) is assigned to row 1, col 3 (C) of the active Worksheet.
ActiveWorkbook.ActiveSheet.Cells(1,4).Value = DivideBy(500,100)
Such a statement can, of course, be explicitly referenced by preceding the Cells method with
Workbook and Worksheet references as just previously shown. In this example, the value 5 is placed
in row 1, col 4 (D) of the active Worksheet.
4.3
HANDLING ERRORS IN VBA FUNCTIONS
The
function has performed as expected in the examples given so far. However, consider
DivideBy
this case:
Debug.Print DivideBy(100,0)
Typing the above in the immediate window will cause an error (Figure 4.1). Division by zero,
while possible in theory — (when x > 0) — is not allowed in Excel. Although few
would make such an overt mistake within a function call, suppose the passed parameters to the
n
lim (
)
=∞
n
→
0
function were calculated by other functions that were extremely complex in nature. It
is easy to see how such a call could mistakenly happen. All functions have roots, and depending upon
what numbers are plugged in where, a function could return a value of zero. Therefore, when performing
DivideBy
