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 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.
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.
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.
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:
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.
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
Check out this example:
But don't take my word for it. Go give it a try!