Debugging macros in Excel Visual Basic / VBA
Part two of a five-part series of blogs

This series of blogs is intended to provide online training in how to debug in Visual Basic for Applications, including using breakpoints and the immediate window.

  1. Debugging Macros
  2. Ways to Step Through Code in VBA (this blog)
  3. Setting Breakpoints and the VBA Stop Statement
  4. Debug.Print and the Immediate Window
  5. Other Useful Debugging Tools

This blog is part of our Excel VBA tutorial.  You can also learn to program in Visual Basic for Applications on one of our scheduled or tailored VBA training courses.

Posted by Andy Brown on 25 November 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.

Ways to Step Through Code in VBA

You can use various combinations of the F8 key to step through code, and also use the yellow arrow and right mouse button to change the next executable statement.

Keys to Step through Code

As mentioned, you can use the F8 key to step through code, but what happens when you reach a call to another routine?

One macro about to call another

In the example shown, the CheckAnagrams macro is about to call the GetWords macro.  What will happen next depends on which function key you press:

 

If you step into the GetWords routine as above by pressing F8 and then realise that you didn't mean to do this, you can press - wait for it - SHIFT + CTRL + F8 to execute the rest of the GetWords routine and return the current line to the main CheckAnagrams program.

Changing the Next Executable Line

The above keys are great for stepping forward through a macro line by line, but whoever said you had to do this?  What happens if you want to go backwards, or jump forwards?  The easiest way to do this is to click and drag on the yellow arrow:

Dragging yellow arrow

Want to run the GetWords routine again? Just drag the yellow arrow to the line which you want to execute next, and Excel will make this the next executable statement.

 

It hardly needs saying, but you should do this with care only!

Another way to achieve the same effect (though not nearly as much fun) is to right-click on the line which you want to execute next:

Setting next statement

Right-click on any executable line to make this the next statement to be executed (it will go yellow).

 

Once you've got the hang of stepping through macros, it's time to set some breakpoints.

This blog has 0 threads Add post