How do I get started with debugging VBA macros?

This is to help to locate faults and mistakes within the code and the data it handles.


Already the most basic level of debugging a macro is useful: This is how to get started;

Arrange the prerequisites for running the macro, then press ALT+F11

Look up the macro in any 'Modules' (you might need to open a panel in the View menu to see them), there may also be 'Modules' in several places - one being a 'standard' location, and at least one in each open sheet in addition to that.

As you have the macro code visible; Click to place the cursor e.g. on a line of interest, or on a line slightly before it.

Note that Excel doesn't allow breakpoints on comment-only or 'dim' lines.
F9 will place a break point on the line, which makes execution stop there as the macro is running.

Now start the macro in the way you're used to. It will run until it hits the break point. As it has done that, the editor will pop up - and be in "Debug mode"; this makes more of the options in the "Debug" menu be useful (F9 above is the same as 'Toggle breakpoint' also in the menu).

F8 is a shortcut for 'Step into' which will run one clause in the macro at a time, also jumping into any subroutine calls and functions that you have defined.

As each clause has executed you may examine the Excel sheet cursor positions and the variables in your macro.
You may also add a 'Watch' on any variable to have its content visible while you're debugging.

Good luck!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.