There are multiple ways to deal with this, and we'll
use the most common one for this month and expand
with other methods as we go forward.
We'll change our code ever so slightly and make
it much more flexible.
Our new function looks like this:
Function CubitsToInches(dblCubits As Double,
strPerson As String) As Double
Dim dblInchesPerCubit As Double
On Error GoTo PROC_ERR
dblInchesPerCubit = DLookup
("CubitLen", "tblCubit", "Person = '" & strPerson & "'")
CubitsToInches = dblInchesPerCubit *
dblCubits
PROC_EXIT:
Exit Function
PROC_ERR:
CubitsToInches = 0
GoTo PROC_EXIT
End Function
We've added four lines of code and two labels (the
labels are the lines that end with a colon).
Let's take a look at our new additions.
On Error GoTo PROC_ERR
This line says if there is any problem at all, jump to
the line that says "Proc_Err." I intentionally use the
term Proc_Err because it has meaning; this section is
a procedures error handling section.
Our next two new lines appear to be out of order
PROC_EXIT:
Exit Function
This comes before the end of the function for a good
reason. We'll leave this for the time being and come
back to it because I want to explain how the error
handling part of the routine works.
PROC_ERR:
CubitsToInches = 0
GoTo PROC_EXIT
Here's where we jump if there is a problem. The first
thing to note is that PROC_ERR: has a colon at the
end of it. This is a signal to Access that this line is
not a line of code, but rather a label. This means
that it will never be executed, but rather it is a point
to jump to from other parts of the code. Because of
the earlier statement On Error GoTo
PROC_ERR, if there is a problem in the code the
program jumps directly to this line to handle problems
Our function name is CubitsToInches; the line
CubitsToInches = 0 says that the function
should return a value of zero if an error occurs. A
value of zero should certainly alert our user that
there is a problem.
The final new line is GoTo PROC_EXIT. This
says we're done here-jump to the line label that says
PROC_EXIT. This part of the code, if it is ever
reached (and in most cases it won't be), will send
the code back the Proc_Exit part of the function so
that no matter what happens the code will always
exit from the same spot. Although in our case it's not
strictly required, it's still a good habit to get into.
The basic law of any procedure is that it should have
just one exit point. By following this convention your
life will be easier when it comes time for debugging.
At this point we end up back at
PROC_EXIT:
Exit Function
The Proc_Exit line is just a label so there is no code
executed there – the next line "Exit Function" pretty
much does what it says-it exits the function. Now
we will have reached this point in one of two ways:
either everything has gone according to plan and we
are returning a reasonable number, or there has been
a problem and the code jumped to the line Proc_Err
and from there will have set our CubitsToInches to 0
and then sent the code back to Proc_Exit.
Once this new code is in place, you can test it with
a variety of names and measures. If you give it the
name of someone who is already in our table, you'll
get a valid response; if you ask it to convert cubits
to inches for someone who is not in out table, you'll
get back a zero. Type the following code into the
immediate window:
? CubitsToInches(2,"Paul")
Based on the discussion above, the value returned
will be zero because the code detected that Paul
was not in the table. The function worked by
returning a zero for an invalid value, thus alerting the
user that there was a problem.
