excel-vba Debugging and Troubleshooting Immediate Window
Example
If you would like to test a line of macro code without needing to run an entire sub, you can type commands directly into the Immediate Window and hit ENTER to run the line.
For testing the output of a line, you can precede it with a question mark ? to print directly to the Immediate Window. Alternatively, you can also use the print command to have the output printed.
While in the Visual Basic Editor, press CTRL + G to open the Immediate Window. To rename your currently selected sheet to "ExampleSheet", type the following in the Immediate Window and hit ENTER
ActiveSheet.Name = "ExampleSheet"
To print the currently selected sheet's name directly in the Immediate Window
? ActiveSheet.Name
ExampleSheet
This method can be very useful to test the functionality of built in or user defined functions before implementing them in code. The example below demonstrates how the Immediate Window can be used to test the output of a function or series of functions to confirm an expected.
'In this example, the Immediate Window was used to confirm that a series of Left and Right
'string methods would return the desired string
'expected output: "value"
print Left(Right("1111value1111",9),5) ' <---- written code here, ENTER pressed
value ' <---- output
The Immediate Window can also be used to set or reset Application, Workbook, or other needed properties. This can be useful if you have Application.EnableEvents = False in a subroutine that unexpectedly throws an error, causing it to close without resetting the value to True (which can cause frustrating and unexpected functionality. In that case, the commands can be typed directly into the Immediate Window and run:
? Application.EnableEvents ' <---- Testing the current state of "EnableEvents"
False ' <---- Output
Application.EnableEvents = True ' <---- Resetting the property value to True
? Application.EnableEvents ' <---- Testing the current state of "EnableEvents"
True ' <---- Output
For more advanced debugging techniques, a colon : can be used as a line separator. This can be used for multi-line expressions such as looping in the example below.
x = Split("a,b,c",","): For i = LBound(x,1) to UBound(x,1): Debug.Print x(i): Next i '<----Input this and press enter
a '<----Output
b '<----Output
c '<----Output
