To begin this part of the lesson you'll need a copy of the procedure we've
been creating over the previous parts. You can either use the copy you've
been writing yourself, download a copy using the link in the Files
Needed section above, or copy and paste the code shown below into a new
module:
Sub My_First_Program()
Worksheets.Add
Range("A1").Value = "Wise Owl"
Range("A2").Value = Date
Range("A3").Value = Time
Range("A1:A3").Interior.Color = rgbCornflowerBlue
Range("A1:A3").Font.Color = rgbWhite
Columns("A").AutoFit
End Sub
Syntax Errors
The first type of problem you're likely to encounter in VBA is called a
syntax error. A syntax error is a mistake in the punctuation of an
instruction that you've written. Syntax errors are highlighted when you
write or edit an instruction and then move the cursor to another line. To
demonstrate this, try editing the line of code shown in the image below:
Position the cursor next to the double-quote after the cell reference
A1.
Now delete the double-quote character.
The VBE won't detect the syntax error until you move the text cursor onto a
different line. Try doing this now either by clicking on a different line
or by using the cursor keys :
The offending line will be highlighted
in red. You'll also see a dialog box appear with a message that is often difficult to decipher!
To fix a syntax error, you simply need to punctuate your instruction correctly!
Add a double-quote in the correct position and move the cursor to a different line of code to check that you've fixed the syntax error.
The messages that accompany syntax errors are often unhelpful. If you
prefer, you can disable these messages. To do this, choose
Tools | Options...
from the VBE menu and complete the dialog box as shown below:
On the
Editor tab, uncheck the box labelled
Auto Syntax Check and click OK.
The next time you make a syntax error you'll still see the offending line in
red, but you won't see an annoying popup message!
Compile Errors
Syntax errors are easy to spot as the VBE warns you about them as soon as you
create them. Another type of problem that is less easy to spot is called a
compile error. You can think of a compile error as a mistake in
the structure of your code,
the grammar of an instruction or, sometimes, in the spelling of a keyword in the
instruction.
Compile errors are detected automatically when you choose to run a subroutine
but you can check for them at any time by choosing
Debug | Compile VBAProject from the VBE menu:
Choose this option to check the entire project for compile errors.
To demonstrate a compile error, let's use the wrong method to attempt to create a new
worksheet. Edit the first line of the subroutine:
Alter this line by removing the
Add keyword.
Replace
Add with Insert.
Although Insert is a valid keyword in Excel VBA, you can't
apply it to the Worksheets object to create a new worksheet.
When you move the cursor to another line you won't see any code highlighted
in red. This simply means that you don't have any syntax errors
in your code:
Just because you don't have any red text doesn't mean that things can't go wrong!
Compile your project by choosing Debug | Compile VBAProject from the VBE menu:
Compile errors are indicated by highlighting the offending piece of code in blue and displaying a message box.
To fix this problem, click OK on the message box,
then alter the line of code to use the Add method:
Change the code back to its original form to solve the compile error.
You may wish to compile your project once more to establish that you've solved
the problem.
Run-time Errors
The final type of error that you're likely to encounter is referred to as a
run-time error. As its name suggests, this type of error occurs
when your program encounters a problem while it is running. Run-time
errors can occur for all kinds of reasons, some of which may not even be your
fault!
As it can be difficult to anticipate when run-time errors will occur, it's
important to know how to write code to deal with them. You'll learn how to
write error-handling code in a later module.
To demonstrate a simple run-time error, let's make a mistake in the line
which changes the value of cell A1:
Change the cell reference from
A1 (that's the number, one)...
...to
Al (that's a lower case letter L).
This type of mistake is difficult to spot when you're writing code - the
difference between the digit 1 and a lower case L
is very slight. Try moving the cursor to a different line of code to check
that you haven't created a syntax error. Choose Debug | Compile VBAProject
from the menu to check that you don't have any compile errors.
Now try running the subroutine (you can press F5
to do this):
You should see a message
like this one informing you that something has
gone wrong.
You can stop the subroutine by clicking End on
the dialog box shown above. However, you'll get an extra clue about what
has gone wrong by clicking Debug instead:
Clicking Debug pauses your subroutine on the line which has caused the run-time error,
which is helpfully highlighted in yellow.
While your code is paused you can attempt to identify the problem and make
changes to fix it:
When you edit the code, the yellow highlighting will disappear until you move the cursor to another line. Here we've replaced the lower case
L with a number 1.
At this point you can either:
- Continue running the subroutine from the highlighted line
(choose Run |
Continue); or
- Stop the procedure then run it again from the beginning
(choose Run |
Reset, followed by
Run | Run Sub/UserForm).
Congratulations! At this point your code should be error-free again and
you can run your subroutine without issues. You can now either spend more
time practising in the Extra Practice section below, or move on
to the next part of this lesson.
