Change or Check the Value of VBA Variables – The Immediate Window

Sometimes when debugging Macros or checking VBA code you may want to force a value into a variable or check what excel is assigning it at any given step.

The Immediate Window can help you here. To show the window if its not already visible on your VBA Editor use Ctrl+G or simply select the Immediate Window option from the View Tab

VBA_Immediate_Window

 

Below is a very simply Macro that I’ll use for a demonstration. It defines a variable called ‘yourVariable’ as a String, gives it a value of ‘WOW’ and then shows a message box with ‘yourVariable’ in it…

Sub Test_Your_Variable()

Dim yourVariable As String

yourVariable = "WOW"

MsgBox (yourVariable)

End Sub

 

If you step through the code using F8 and stop at the MsgBox line you can hover over the variable name with your mouse pointer and the variables value is show.

VBA_Immediate_Window_2

 

However, if you cannot hover or the pop up box cuts part of the variable value off you can use the immediate window to show your variable’s value. As in the image below, simply type a question mark followed by your variable’s name and hit enter!

VBA_Immediate_Window_4

MAGIC!

The immediate window can in be very useful at times. You can run lines of VBA using it mid-macro. In the image below I had stopped at the MsgBox line again. ‘yourVariable’ was still assigned a value of ‘WOW’ but I then used the immediate window to force the value ‘COOL’ into the variable instead.

VBA_Immediate_Window_5

Very useful when you’re stepping through code and want to test different values without having to go through the code from start to finish every time.

No ratings yet.

Please rate this