Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
545 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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:
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
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:
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:
Delete a double-quote from each of the first two lines, and a full stop from the third line.
For reference, this is what the code should look like without syntax errors.
To practise dealing with compile errors:
Remove the = sign from each of the three lines.
Choose this option to reveal the first compile error in your code.
Click OK to close the message that appears.
You still have two errors to fix! Click OK to close the message.
To practise dealing with run-time errors:
Change the cell reference from A1 to 1A.
Click Debug to see which line has caused the error.
This line should be quite easy to spot!
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.