Avoiding the "Invalid in Immediate Pane" Error
Sometimes, using the colon character to combine statements in the immediate window results in an "Invalid in immediate pane" error. Here's why and how to fix it.
Today I was trying to execute two long-running routines one after the other by calling them on the same line using the colon (:) end-of-statement token in the immediate window.
To my chagrin, my initial attempts failed with the following error message:
Microsoft Visual Basic for Applications
Compile error:
Invalid in Immediate pane
Fix: Use the Optional Call Keyword
To avoid this error, you can use the optional Call keyword before calling the first routine:
Explanation
If you look closely at the original error message, you will note that this is a compile error.
In other words, VBA does not like something about our syntax. What could that be, though? And why does using the Call keyword avoid the error?
The reason is obvious if we try to do the same thing inside of a normal routine:
Notice that the cursor is still editing the Foo: Bar line. Watch what happens when I press [Enter]:
Hey, Foo: just got automatically repositioned to the far left of the line. You know what that looks like? A label. Because it is a label. It's an ambiguity in the VBA syntax. When faced with the ambiguity, VBA comes down on the side of treating it as a line label rather than a procedure identifier as we intend.
By using the optional Call keyword, we make our intent explicit to the VBA compiler:
Alternate Workarounds
Now that we understand why we were getting the compile error, we can make sense of why this is a problem in some cases but not in others.
Pass an Argument
For example, if we pass an argument to our subroutine then VBA can no longer mistake it as a line label and everything works fine:
Debug.Print a Function Result
Here we've changed Foo from a Sub to a Function and we're using the Debug.Print shorthand to display the result of the function call:
Anything that Makes the Code an Invalid Label
Basically, we just need something to come before the colon that is not a valid label name. Whether that means passing an argument, including a Debug.Print call, or explicitly using the Call keyword, we simply need to reassure VBA that we are not trying to use a line label in the Immediate Window.
External references
Cover image created with Microsoft Designer
