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
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
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