and evaluation.) A large base of users can help you find run-time errors that
depend on particular machine configurations or specific kinds of user-entry
techniques.
You can trap run-time errors or change the program flow to ensure that they
don’t happen. In Chapter 5, I show several forms of error trapping. See the
upcoming “Prevention Is Better than a Cure” section of this chapter for addi-
tional examples. The remainder of this book contains yet more examples of
error trapping because this is an important topic. Error trapping helps your
program overcome errors that you can’t predict when you write a program.
Understanding semantic errors
A particularly difficult error to
find and understand is the
semantic error,
which happens when the VBA code and logic are correct but the meaning
behind the code isn’t what you intended. For example, you could use a
Do...Until
loop in place of a
Do...While
loop. Even if the code is correct
137
Chapter 6: Trapping Errors and Squashing Bugs
Difficult-to-diagnose run-time errors
Sometimes VBA actually creates problems for
you when it comes time to diagnose bugs. The
SendKeys()
function is one example. You
can see this function in use in Listing 5-8. The
biggest
problem with the
SendKeys()
func-
tion is that it works in only the active window. If
you have the Visual Basic Editor displayed
when you call
SendKeys()
, VBA sends the
SendKeys()
output to the Visual Basic Editor
rather than to the active Office window, as you
might expect. Consequently, a procedure that
you write that normally
works perfectly well
suddenly fails because of a poor implementa-
tion of the
SendKeys()
function by Microsoft.
The best way to overcome this kind of problem
is to place debugging code in your
Sub
or
Function
. Use the
MsgBox()
function
to
display a message containing the
SendKeys()
information immediately before
the actual
SendKeys()
call in your code. For
example,
you might add a
MsgBox()
call like
this:
‘ Use SendKeys to select all
of the cells in the
column.
MsgBox “Select all of the
cells in the column.”
SendKeys “+^{DOWN}”, True
Using this technique lets you keep the focus on
the Office application, yet also helps you deter-
mine what actions are taking place in the back-
ground. Although this solution is less than
perfect, it’s probably
the best solution in most
cases because you can change the debugging
statements as needed. Of course, you can add
MsgBox()
calls after
SendKeys()
as well
when needed, but the important location is
before you make the call so that the focus is in
the right place when the actual
SendKeys()
call is made.
11_046500 ch06.qxp 12/5/06 5:35 PM Page 137
and you use the correct logic, the code doesn’t
produce the result that you
expected because the meaning of a
Do...Until
loop is different from the
meaning of a
Do...While
loop.
The meaning that you assign to your code has to match the words that you
use to write the code. Just as a good book uses precise terms, a good pro-
gram relies on precise statements to ensure that VBA understands what you
want to do. The best way to avoid semantic errors is to plan your application
carefully, use pseudo-code to “pre-write” the design,
and then convert the
pseudo-code to VBA code. When you skip steps in the process, you can
introduce semantic errors because you don’t communicate your ideas well
to VBA.
Introducing semantic errors in subtle ways is easy. Writing an equation the
wrong way can result in output errors. When you use the wrong equation to
determine the next step in a loop, the problem becomes worse because the
error looks like a syntax or run-time error. The steps between loops and the
expression used to make a decision are very important.
The most common
error is leaving a parenthesis out of an equation. For example, VBA interprets
1 + 2 * 3 as 7 but (1 + 2) * 3 as 9. A missing parenthesis is easy to miss when
you frantically search for an error.
Prevention Is Better than a Cure
Avoiding an error is always easier than trapping or handling it later. However,
any form of prevention is better than letting the error occur without any
intervention at all. Whenever your program experiences an unhandled error,
it stops running (crashes). Here are some reasons why you want to handle
errors rather than just let them happen:
You can provide specific error information.
Programs can recover from many errors.
Programs can reduce or prevent data loss.
You can include special handling that tracks the error for later
debugging.
The host application (such as Office) can
help reduce the effects of
the error.
The operating system can help with some errors and reduce the effect
of others.
138
Do'stlaringiz bilan baham: 
