So far we've assumed that everything has worked first time with no problems. Sadly, this isn't a true reflection of how things will go in the real world! This part of the lesson explains how to deal with common problems that arise when writing and running VBA code.
You can use the file you have created over the previous parts of this lesson. If you don't have this code you can click here to download a copy.
The completed code for this part of the lesson is identical to that in the Files Needed section above.
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:
'create a worksheet
'enter values into cells
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
'change column width
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!
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.
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.
To practise dealing with syntax errors:
- Using the subroutine you've been working with in this lesson so far, edit the following three lines of code to introduce some syntax errors:
Delete a double-quote from each of the first two lines, and a full stop from the third line.
- Now replace the characters that you have deleted to clear the three syntax errors:
For reference, this is what the code should look like without syntax errors.
To practise dealing with compile errors:
- Edit the following three lines of code to introduce some compile errors:
Remove the = sign from each of the three lines.
- Confirm that your code won't compile by choosing Debug | Compile VBAProject from the menu:
Choose this option to reveal the first compile error in your code.
- Click OK on the message that appears:
Click OK to close the message that appears.
- Fix the first compile error by replacing the = sign and then choose to compile the project again:
You still have two errors to fix! Click OK to close the message.
- Replace the = sign on each of the remaining two lines and choose Debug | Compile VBAProject from the menu to confirm that you've solved all of the errors.
To practise dealing with run-time errors:
- Edit the following line of code to introduce a run-time error:
Change the cell reference from A1 to 1A.
- Run the procedure to confirm that you see a run-time error:
Click Debug to see which line has caused the error.
- Find the line which has generated the run-time error:
This line should be quite easy to spot!
- Fix the error by changing the cell reference back to A1.
- Reset the procedure by choosing Run | Reset from the menu.
- Run the subroutine again to confirm that it works.