While programming, it is always good to test parts of your code as you are writing it instead of writing the whole program, compiling it and running it — only to find out that it has a bug. In this article, you are going to learn how to use the VBA console, which is also known as the immediate window.
What is the Use of the VBA Immediate Window?
The VBA immediate window is a console; it’s basically like a playground. It’s a place to test code because the process of creating a new module and writing a procedure is fairly time consuming.
If you only want to test a line of code, you don’t need to go through all the packaging and compilation procedures. You can just use the immediate window for that.
It should be noted that the immediate window is not made for testing large scale procedures, it is only intended for one or two lines of code just to make sure that everything is working well as expected.
How Do You Activate the Immediate Window?
The immediate window is not a default feature of the Visual Basic Editor. To activate it, follow the steps below:
Step 1: Select the Developer tab on the Excel menu and click on the Visual Basic option or click on Alt + F11 simultaneously.
Step 2: Click on the View tab on the VBE Menu and select “Immediate Window" or press Ctrl + G
You should have the screenshot below:
Note: In the rest of the article, I will use the term VBA console to refer to VBA immediate window.
How Do You Use the VBA Console?
The VBA console can be used in many ways from basic mathematical operations to testing lines of codes. We are going to see some of them in the following examples.
Using the VBA console for basic mathematical operations
Whenever you want to get an answer from the console, you should put the question mark (?) or the command “Print" before the operation. For example, if you want to know the answer of 2 + 3 you should write “? 2 + 3" or “print 2 + 3". Most people prefer to use the question mark because it is shorter.
If you try it in your own VBA console, you should have the following result:
Using the VBA console to interact with a worksheet
If you want to find the value of a cell, you can do so in the console by writing: ? Range(“A1").value
You can also use the console to write a value in a cell by writing: Range(“A1").Value
For instructions or processes asking for actions, you should not use the question mark “?" or “Print" because it is not an inquiry but an instruction telling VBA to do something such as writing the string “Hello" in cell A1.
The operations done on the VBA console are not permanent. They are deleted once you close the workbook. If you want to preserve your code to run it later, you must write it within a procedure and save it.
Print to the VBA Console from a Procedure
You can print in the VBA console from a procedure body. This is really helpful when you want to keep track of what is going on in a procedure like changes in the value of a certain variable. Here you cannot more use the question mark or print. VBA has an object called Debug for that purpose, and it is the main focus of this article. Before going into more details, let’s see how it works. Create a new module and write the following procedure:
Debug.Print 2 * 5
Debug is a VBA object just like Worksheet or Workbook. Print is one of the methods of the Debug object. Debug has precisely two methods which are Assert and Print, but our focus here will be the Print method.
The question mark (?) is an alias of Debug.Print but it can only be used in the VBA Console, unlike Debug.Print, which can be used in both a procedure and the VBA console.
Let’s add more lines of code to the previous procedure and see the outcome in the VBA console:
The immediate window is a very useful feature of the VBA programming language. It can be used not only for inquiries but also to give instructions and test lines of code. It is a good idea to acquaint yourself with it if you plan to make a career in VBA programming.