Problems When Running VBA Code
Lots of things can go wrong when you try to run a subroutine, and not all of them are down to coding errors. This article explains a few of the common mistakes people make when running VBA code and how to get around them.
Macros are Disabled
You might think that this is a silly mistake to make, but it's surprisingly common, especially on training courses! All of the Microsoft Office applications have a number of macro security levels and the level that is set determines when macros are allowed to run. If you try to run a macro when they are not enabled, you'll see a message like this:
This is an example of the type of message you'll see when macros are disabled but you try to run one anyway.
Making sure that macros are enabled works slightly differently depending on which version of Office you are using, but in essence there are two steps involved:
Choosing an appropriate level of security.
Choosing to enable macros each time you open a file that contains them.
Changing the Macro Security Level in Office 2003
To choose the appropriate level of security in Office 2003 applications:
From the menu, choose: Tools -> Macro -> Security...
On the dialog box that appears, select Medium, and then click OK.
Medium is probably the most sensible option to choose. A Low security level means that your code will always be allowed to run, but so will everyone else's, including potentially dangerous code.
Changing the Macro Security Level in Office 2007 and 2010
To choose the appropriate level of security in Office 2007 or 2010 applications:
From the Ribbon, select: Developer -> Macro Security.
On the dialog box that appears, choose the option shown in the diagram below.
The selected option in this diagram is the equivalent of the Medium security level in Office 2003.
Once you have done this, close down the application and then reopen it. The next time you open a file that contains macros you must choose to enable them. This works differently in the different versions of Microsoft Office.
Enabling Macros in Office 2003
In Office 2003 applications when you open a file containing macros you will see a dialog box like this one:
Click Enable Macros to make sure your code will run.
Enabling Macros in Office 2007
In Office 2007 you will see a small message appear below the Ribbon when you open a file containing macros:
Click the Options... button to choose to enable macros.
On the dialog box that appears you can then choose to enable macros:
Choose this option and then click OK to make sure your code can run.
Enabling Macros in Office 2010
In Office 2010 you will see a message appear under the Ribbon - simply click the Enable Content button to make sure your macros can run.
Click the button shown here to make sure you can run your subroutines.
When you are sure that macros are enabled there are still plenty of other potential sources of error!
Not Selecting the Correct Subroutine
If you are running your code from within the VB Editor, you have to select the subroutine you want to run before you try to execute it.
If the text cursor isn't within the subroutine you want to run, the VB Editor won't know which one to run.
If you don't have a subroutine selected at all you will see a dialog box appear to ask you which one you want to run.
You could always select the macro at this point and click Run, but it's much easier to just click in the macro you want before you try to run it!
Syntax Errors
Syntax errors are mistakes in the punctuation of your VBA sentences. These are very easy to spot because as soon as you make a syntax error and try to move the cursor to a different line of code, the offending line will be highlighted in red.
With the default settings in the VB Editor, you will also see a dialog box attempting to explain the problem.
To solve this issue, click OK on the message that appears and then try to amend the line of code that is flagged in red. Here the mistake we've made is to miss the double-quotes after the cell reference A2. When you have edited the line of code, move the cursor to a different line to check that it doesn't get highlighted in red again.
The dialog box that pops up to warn you about syntax errors is never particularly useful, and it's quite annoying to have to click OK before you can fix the problem. You can turn off this warning message (but still have syntax errors highlighted in red) by selecting: Tools -> Options... and then completing the dialog box that appears as shown below:
Make sure the Auto Syntax Check box is unchecked and then click OK to make sure you don't see the annoying pop-ups every time you make a mistake.
Compile Errors
When you choose to run a program that you've written in VBA, the VB Editor first of all compiles your code to make sure that all the words make sense in the context you've used them. A compile error is a problem that occurs at the compile stage but before your code has actually started running.
In this example we've misspelt the name of the Add method. The VB Editor has helpfully highlighted this for us so our job of fixing it is as easy as possible.
To fix a compile error, click the OK button on the message that appears and try to identify what is wrong with the part of the code that has been highlighted. Compile error messages are often quite descriptive, like the one shown below:
It's fairly obvious what this error message means, and as if it wasn't obvious enough the offending part of the code is highlighted again.
You can ask the VB Editor to compile your project without trying to run a subroutine by selecting: Debug -> Compile ProjectName
Run-Time Errors
Run-time errors are errors that occur when your program has successfully passed all of its syntax checking and compiling and is in the process of running. Any executable line of code has the potential to generate a run-time error and it's not always obvious why one has occurred. The diagram below shows an example of a run-time error:
It's not immediately obvious what's gone wrong here as run-time errors don't highlight any of your code immediately. You can click the Debug button to see which line of code has failed to run.
When you click the Debug button on a run-time error dialog box, the line of code that has failed will be highlighted in yellow. In the example below you can see that it's the second line of code that has gone wrong.
Closer inspection should reveal a mistake in the cell reference we've typed in.
This is just one of many examples of run-time errors that you'll experience when running VBA code. Run-time errors are often the most difficult to resolve and there's really no substitute for experience when things get tricky. The best way to learn is to try something, get it wrong, and then fix it!
What We've Learnt
If you've been following this blog series from the beginning and you'd never even used VBA before, congratulations! You've come a long way from where you started, but there's still an awful lot to learn before you become a fully fledged programmer. The key now is to start experimenting with new techniques: try recording some macros to see the code that gets written for you; edit some recorded macros to see what happens; read more Wise Owl blogs to give you more ideas. Above all, learning to program in VBA can be immensely satisfying, so have fun with it!
