How to Clear the Immediate Window Using VBA

A VBA program can have multiple subroutines and functions. If you are a programmer you will need to test these subroutines and functions while developing the VBA code. Then the immediate window is a helpful tool you can use to test your components. If it is a subroutine, then you can print your variables using debug.print method to ensure whether they are calculated correctly. If it is a function you can print the return value in the immediate window.

From one of our previous posts we learnt how to output data in VBA using a few different ways. In that post you can find how to output data to Immediate window using VBA.

Output data in Excel VBA

So today I'm going to show you how to clear the immediate window automatically using VBA. You may know how to clear it manually. If you don't know, follow these simple steps. First select everything in the immediate window. Then press the delete key on the keyboard to delete everything. If the immediate window is not visible you can use shortcut keys “Ctrl + G” to view the immediate window. Also if the cursor is in a different window than the immediate window, and if you press the above shortcut keys, then the cursor will move to the immediate window giving you access to the immediate window.

Now let’s look at how we can clear the immediate window using VBA. We can use the Application.Sendkeys method to clear the immediate window using VBA. Application.Sendkeys method lets us send keystrokes to the active application. So here is the order of keystrokes we need to send to clear the immediate window.

Ctrl + G
Ctrl + A
Delete

Ctrl + G will activate the immediate window. Then Ctrl + A will select everything in the immediate window. And Delete keystroke will delete the content from the immediate window. However in the Application.Sendkeys method, these keys are represented by one or more characters. Ctrl is represented by ^ character. You can simply use g and a keys to represent G and A respectively. and the delete key is represented by {DEL} characters. Check this documentation from Microsoft to see the codes of other available keys.

Application.SendKeys method (Excel)

So now you can rewrite the order of keystrokes in the Application.Sendkeys notation as follows.

^a^g{DEL}

Then you can develop the macro to clear the Immediate window using VBA as follows.

Sub ClearImmediateWindow()

     Application.SendKeys "^g^a{DEL}"

End Sub

To test this macro, First I need to output something in the Immediate window. I will use the below macro to print “Hello, World!” hundred times in the immediate window.

Sub WriteHelloWorld()

     Dim i As Integer

     For i = 1 To 100
         Debug.Print "Hello, World!"
     Next i

End Sub

This is how the Immediate window looks like when run the above macro.

Now let's run the ClearImmediateWindow subroutine to see how it clears the immediate window.

However, using the Application.Sendkeys method is not advisable. Because unexpected things can happen if a user interacts with the Excel application while running the program. For example a whole code in your modules can be deleted if accidentally selected a module instead of the immediate window. So this is not a robust code to use in a VBA application. I suggest not to use the Application.Sendkeys method if there are any other options.