Introduction to Visual Basic for Applications (VBA)
Part five of a five-part series of blogs

If you've heard people in your office talking about macros or VBA but don't understand what they mean, this blog series will make everything clear. It's an introduction to the most basic skills you'll need to start creating your own programs in any of the Microsoft Office applications.

  1. Introduction to VBA (Visual Basic for Applications)
  2. Creating Your First VBA Program
  3. Writing VBA Code (Visual Basic for Applciations)
  4. Running a Subroutine in VBA macros
  5. Problems When Running VBA Code (this blog)

Posted by Andrew Gould on 01 July 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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:

An error message

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:

  1. Choosing an appropriate level of security.
  2. 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:

  1. From the menu, choose: Tools -> Macro -> Security...
  2. On the dialog box that appears, select Medium, and then click OK.
Macro security in Excel 2003

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:

  1. From the Ribbon, select: Developer -> Macro Security.  If you can't see the Developer tab, click here to see how to enable it in Office 2007, or here to see how to enable it in Office 2010.
  2. On the dialog box that appears, choose the option shown in the diagram below.
Macro security in Office 2007

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:

Excel 2003 macro dialog

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:

Enabling macros in Office 2007

Click the Options... button to choose to enable macros.

 

On the dialog box that appears you can then choose to enable macros:

Enabling macros in Office 2007

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.

Enabling macros in Office 2010

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.

Non-selected macro

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.

Selecting a macro 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.

A syntax error in VBA

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:

Turning off syntax error warnings

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.

A compile error

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:

Another compile error

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:

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.

Debugging a routine

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 can be immensely satisfying, so have fun with it!

This blog has 0 threads Add post