Mistakes are a fact of life, and Visual Basic is
fairly intolerant of them. If you followed along with the preceding
lab, you probably already encountered that fact. Sometimes it's pretty
easy to tell what you've done wrong, and sometimes it's almost
impossible—even for experienced programmers! What marks the difference
between beginning and expert programmers is how they go about solving
those problems.
To
help you understand fixing mistakes, you need to know that there are
four different kinds of errors that are generally identified by where
or why they happen:
Syntax errors
Occur
when you mistype a statement, such as omitting a closing parenthesis or
omitting some part of the statement that is required. Visual Basic
detects these errors right away and highlights them in red as soon as
you move to the next line of code.
Semantic errors
Are also often the result of a typo, but they appear
valid to Visual Basic as you type. Examples of this kind of error
include misspelling a method or property name or using a variable or
procedure name that isn't defined yet. Visual Basic checks for these
errors the moment you run your code (for instance, when you press F5).
At that point, Visual Basic converts your code into a form that Excel
understands (this is called compiling),
and if any of the names you used aren't found, compiling stops and
Visual Basic highlights the error. Semantic errors are sometimes called
compile-time errors
for that reason.
Logic errors
Can
be the hardest to detect. These errors occur when your code simply
doesn't do what you expected it to do. Infinite loops (lather, rinse,
repeat...) are an example, as are unexpected results such as formatting
code that doesn't format everything it should. Logic errors can
sometimes halt your code while it is running, and for that reason they
are often called runtime errors
.
Expected errors
Aren't
your fault, but you need to deal with them all the same. These are
another type of runtime error, and they are usually the result of using
resources outside of Excel, such as trying to get a file from disk or
trying to connect to a database somewhere. In those cases, you need to
anticipate the possibility of a problem using a technique called
exception handling.
The real name for expected errors is exceptions
. (Since you expect them, they aren't really errors, are they?)
For now, let's look at fixing
the errors that are your fault.
1. Fix Syntax Errors
Visual
Basic can detect many kinds of typos as you move from line to line in
the code window. This is the most common type of error you'll make as
you learn programming. Fortunately, Visual Basic can generally tell
what you did wrong, as shown in Figure 1.
If
you click OK but don't fix the error, Visual Basic leaves the line
colored red as a reminder that you should fix it. If you look at the
SyntaxErrors sample, you'll see that it looks like a Christmas tree
with all the green comments and red errors that I've deliberately
included to illustrate the different ways errors in syntax can occur.
If
you don't understand the error dialog box Visual Basic displays, you
can either click Help to get more information about the error or click
OK, select the item you have a question about, and press F1 as shown in
Figure 2.
Help
often tells you what you need to know about a specific Visual Basic
statement. Sometimes it's less helpful about Excel methods, but it's
always a good first place to look since it's only a key press away.
Another good, easy way to figure things out is by using Visual Basic's
Auto Complete
feature. By default, Visual Basic displays lists of items that could complete statements as you type, as shown in Figure 3.
To
insert one of the items from the list, use the arrow keys or mouse to
select the item and press the spacebar to insert the item in your code.
A similar thing happens when you add a statement that takes arguments,
as shown in Figure 4. (Arguments are additional pieces of information that a statement needs to accomplish its task.)
In
this case, the arguments are shown in italics with the current one
shown in bold. Arguments are always separated by commas and once you
type a comma, the next argument becomes bold. Square brackets mean that
an argument can be omitted.
Visual
Basic's automatic syntax checking and Auto Complete features can help
you learn the language, but some programmers find the error dialogs and
pop-up text annoying in some situations. Visual Basic lets you turn off
these features by choosing Tools → Options and selecting the Editor tab
as shown in Figure 5.
Don't do it! Syntax checking and Auto Complete are incredibly useful if you are learning the language.
2. Fix Compile-Time Errors
In
some cases, statements look correct to Visual Basic as you are writing
them, but they don't make sense when Visual Basic tries to compile them
into a program. This occurs because there are some things Visual Basic
has to ignore as you are writing the code but can't ignore when you try
to run it.
A
simple example is when your code calls a procedure that you haven't
written yet. Visual Basic doesn't flag that statement as a syntax
error, because it assumes you'll get around to writing the procedure.
If you forget to do that, Visual Basic reminds you when you try to run
the code (Figure 6).
Visual Basic alerts you to compile-time errors one at a time, so if you fix the one shown in Figure 6 and then try to run again, another error will pop up on the SyntaxError line—that's a case of a simple misspelling, it should be SyntaxErrors.
Visual
Basic is strict about spelling and doesn't guess at what you meant to
write. It would be cool if it were that intuitive, but it would cause
bigger problems if it guessed wrong! |
|
Sometimes
compile-time errors are similar to syntax errors, such as when you omit
a required argument or don't terminate a statement that spans multiple
lines, such as a loop or a decision statement. In those cases, Visual
Basic flags the End Sub or End Function statement because it searched to the end of the procedure without finding the end of the previous block (Figure 7).
The missing End If is pretty obvious in Figure 7 because the procedure is not very long, but it can be much harder to locate where the End If
should go in longer passages of code. For that reason, programmers
usually indent blocks of code that are logically related, for example:
' Activate the next worksheet or chart, depending on
' what type of sheet is currently active. Return to
' first sheet when the end is reached.
Sub ChangeSheets( )
Select Case TypeName(ActiveSheet)
Case "Worksheet"
If ActiveSheet.Index < Worksheets.Count Then
Worksheets(ActiveSheet.Index + 1).Activate
Else
Worksheets(1).Activate
End If
Case "Chart"
If ActiveSheet.Index < Charts.Count Then
Charts(ActiveSheet.Index + 1).Activate
Else
Charts(1).Activate
End If
Case Else
Debug.Print TypeName(ActiveSheet), ActiveSheet.Name
End Select
End Sub
In
this case, indents make it easier for you to match the begin and end
statements for various blocks of code (seen here with all the details
removed):
Sub
Select Case
Case
If
' task
Else
' task
End If
Case
If
' task
Else
' task
End If
Case Else
' task
End Select
End Sub
Indenting
is a standard practice that helps you avoid errors by making it easier
to read and interpret logically related pieces of your code. It is not
required by Visual Basic, and adding or omitting indents does not
affect how your code runs.
3. Fix Runtime Errors
Boy,
it seems like a lot of things can go wrong! However, most of these
problems are pretty obvious and easy to fix. That's not so true for
errors that occur when your program is running. Unlike other types of
errors, Visual Basic can't detect these until the program actually
tries to execute the statement. That makes it harder to tell where the
error occurred and why it happened. For example, Figure 8 shows a procedure with a runtime error.
You don't know which line the error occurred on, although you might guess it was the Worksheets("Resource").Activate
statement because I said so in my comments. If you want to confirm
that's the error, press F8 instead of F5 to step through the procedure (Figure 9).
In this case, you have to ask yourself why Excel couldn't find the Resource worksheet. Well, it's because the worksheet is actually named Resources. I don't mean to beat you over the head with this, but spelling is important!
Runtime errors occur for a variety of reasons. For instance, there is a limit to how big a number can be in Visual Basic and 100 ^ 100 ^ 100 exceeds that limit (Visual Basic calls that an overflow
). Other errors are harder to find with F8, for example the EasyRTErrors
statement calls itself over and over again indefinitely. That's similar
to an infinite loop, but since it's calling itself, it's referred to as
infinite recursion
instead. If you try F8 on that line, you'll see that you can execute it
more than 5000 times without an error. In that case, you just need to
remember that an Out of stack space error usually means you've got an infinite recursion.
Another type of runtime error that's very common but difficult to find is misspelled variable names
. For example, the following code displays a dialog box, but never says "Howdy" no matter what the user clicks:
Sub SubtleRTErrors( )
' I misspelled answer, you never hear Howdy:
answer = MsgBox("Click OK to hear Howdy.")
If aswer = vbOK Then Application.Speech.Speak "Howdy"
End Sub
The Speech object was added to Excel in 2002. If you have an earlier version, use MsgBox instead of Application.Speech.Speak for this sample. |
|
There's
nothing technically wrong with the code, other than the fact that it
doesn't work! This problem occurs because Visual Basic lets you create
variables without ever declaring them. That makes life easier in the
beginning (about 30 minutes) but adds a tremendous burden later on
trying to locate and fix this type of subtle error. Fortunately,
there's a fix: turn off automatic variables by choosing Tools → Options
and selecting the Editor tab, then selecting Require Variable
Declaration as shown in Figure 10.
When you select Require Variable Declaration, Visual Basic adds an Option Explicit statement any time it creates a new class or module. If you wrote code before changing that option, you need to add Option Explicit yourself. The Option Explicit statement causes a compile-time error whenever it encounters an undefined variable, as shown in Figure 11.
Using Option Explicit creates a little more work writing code, but it saves a lot of work fixing code later.
