Module 1 - Getting Started in VBA
Lesson 1.1 - Your First Program
Topic 1.1.5 - Dealing with Problems

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.

Files Needed

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.

Completed Code

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:

Sub My_First_Program()

 

'create a worksheet

Worksheets.Add

 

'enter values into cells

Range("A1").Value = "Wise Owl"

Range("A2").Value = Date

Range("A3").Value = Time

 

'format cells

Range("A1:A3").Interior.Color = rgbCornflowerBlue

Range("A1:A3").Font.Color = rgbWhite

 

'change column width

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:

Alter line

Position the cursor next to the double-quote after the cell reference A1.

 
Delete

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 :

Syntax error

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!

Fix syntax error

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:

Disable syntax messages

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:

Compile

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

Alter this line by removing the Add keyword.

 
Replace with insert

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:

Looks ok

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 error

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:

Alter code

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:

Edit A1

Change the cell reference from A1 (that's the number, one)...

 
Al

...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):

Run-time error

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:

Break mode

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:

Fix error

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.

Syntax Errors

To practise dealing with syntax errors:

  1. 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:
Syntax errors

Delete a double-quote from each of the first two lines, and a full stop from the third line.

 
  1. Now replace the characters that you have deleted to clear the three syntax errors:
Fixed errors

For reference, this is what the code should look like without syntax errors.

 

Compile Errors

To practise dealing with compile errors:

  1. Edit the following three lines of code to introduce some compile errors:
Compile errors

Remove the = sign from each of the three lines.

 
  1. Confirm that your code won't compile by choosing Debug | Compile VBAProject from the menu:
Compile

Choose this option to reveal the first compile error in your code.

 
  1. Click OK on the message that appears:
Confirm message

Click OK to close the message that appears.

 
  1. Fix the first compile error by replacing the = sign and then choose to compile the project again:
More errors

You still have two errors to fix!  Click OK to close the message.

 
  1. 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.

Run-time Errors

To practise dealing with run-time errors:

  1. Edit the following line of code to introduce a run-time error:
Run-time error

Change the cell reference from A1 to 1A.

 
  1. Run the procedure to confirm that you see a run-time error:
Run-time error message

Click Debug to see which line has caused the error.

 
  1. Find the line which has generated the run-time error:
Error line

This line should be quite easy to spot!

 
  1. Fix the error by changing the cell reference back to A1.
  2. Reset the procedure by choosing Run | Reset from the menu.
  3. Run the subroutine again to confirm that it works.
This page has 0 threads Add post