Debugging Private Procedures

Mike Wolfe -

One of the advantages of VBA being an interpreted language is that we can execute individual procedures without having to compile the entire application.  Let's explore exactly how we do that.

Public Procedures

Public Routines with No Arguments

In a standard module, you can place the cursor inside of a Public Routine that has no arguments, press [F5], and that routine will immediately execute.

Pressing F5 within a routine inside a standard code module will immediately execute that routine.

If you want to step through the routine, you can either set a breakpoint on one of the lines before pressing [F5] or simply press [F8] ("Step Into") and immediately begin stepping through the code for the routine.

To step through the routine one line at a time, press F8 ("Step Into") instead of F5.

Public Functions with No Arguments

This also works with public functions in standard code modules.  The key is that the function cannot accept any arguments, not even optional ones.

Pressing F5 within a function executes it, though nothing is done with the return value.

Public Procedures with Arguments

What happens if you try to execute a public procedure that takes one or more arguments (even optional ones)?  The "Run > Run Macro" command executes:

Pressing F5 to execute a routine that takes arguments will instead execute the "Run > Run Macro" command.

Private Procedures

Private Procedures with No Arguments

I can't take credit for discovering this one.   Special thanks goes out to Terry Chapman for passing along the fact that you can press F5 to debug Private procedures in place, not just Public procedures.

Like Terry, I used to temporarily change my Private procedures to Public while I was debugging them.  It turns out, I never had to do that at all.

Just press [F5] to run private procedures. You can step through using [F8], too!

Private Procedures With Arguments

Once Terry got me questioning my assumptions, I decided to see just how much I could get away with.  A lot more than I was expecting, it turns out!

Believe it or not, you can debug private procedures that take arguments without temporarily changing them to public procedures.  To do this, you just need to call the fully qualified procedure name from the Immediate Window.  By "fully qualified name", I mean the name in the format of {ModuleName}.{ProcedureName}.

Check out this example:

Wait for it...
...BOOM!

But don't take my word for it.  Go give it a try!

Image by Gerhard G. from Pixabay