Tuesday, March 15th, 2016 / by Brandon
Utilizing the Immediate Window
The immediate window is a console of sorts that allows the user to enter VBA code that can be directly evaluated or executed during the development or debugging process. It is useful for checking values and states at various points in the execution of a routine. It can also be used to manipulate the worksheet, select cells and move data. To display the Immediate Window, press Ctrl-G or click on View in the menu bar and select Immediate Window, as depicted in the image below. The immediate window will then appear at the bottom of your screen.
Echoing Debug Messages
At any point during the execution of a routine, such as a For/Next Loop, a Debug.Print command can be entered in the VBA code within the module the results will be returned to the Immediate Window. The following example will demonstrate how this works.
Create a new module and paste this code into it.
Debug.Print “Enter MyLoop”
Dim iCounter as Integer
For iCounter = 1 to 5
Debug.Print “iCounter=” & iCounter
Debug.Print “Exit MyLoop”
In the immediate window, type in MyLoop and press enter. MyLoop will execute and you should see debugging text in the immediate window, as shown below.
In this way, variables can be checked to see if their values are being set properly.
For a massive project, with hundreds of routines, having to manually enable and disable debugging text using the above technique can be cumbersome. The solution for this is to use a global flag that can quickly enable or disable all the Debug.Print commands. The example below will demonstrate this.
Let’s take the MyLoop sub and set a global flag to determine the level of debugging to be enabled. The global flag is set once, as follows:
Public Const DEBUG_LEVEL = 1
If DEBUG_LEVEL >=1 then Debug.Print “Enter MyLoop”
Dim iCounter as Integer
For iCounter = 1 to 5
If DEBUG_LEVEL >=2 then Debug.Print “iCounter : “ & iCounter
If DEBUG_LEVEL >=1 then Debug.Print “Exit MyLoop”
The first set of results with the debug level set to 2, Enter, Exit and the iCounter loop counter variable results are displayed in the immediate window.
The second set of results is with the debug level set to 1, only the prints for Enter and Exit MyLoop are displayed in the immediate window.
Selections and Content
Another power of the immediate window is to work with the current selected set of cells. In your use of Excel you may have the need to select a range of cells, modify it in some way, and then select another range of cells with the same pattern and modify it. This process may need to be repeated multiple times. Without utilizing the immediate window, each range would need to be manually selected. Using the Selection object in the immediate window can make this process more efficient.
To select a range of cells with the same pattern, just 3 columns to the right, type the following into the immediate window and press enter.
See below, the cells A1, B2 and C3 are selected.
By typing in Selection.offset(0,3).Select into the immediate window and pressing enter, we move the selection to C1, D2, E3 depicted below.
In addition to moving the selection, the values or formats of the cells can be changed from the immediate window, using the following command:
Selection.Value = # or “Some String”
This will assign that value to all the selected cells.
Nearly anything that can be done in a VBA module can be done in the immediate window, unless it requires multiple commands. In that instance, simply create a sub/function and call it from the immediate window.
I can not recommend ExcelHelp more highly. We had been struggling to complete a critical project and finding them was a lifesaver. Tom was knowledgable, professional, personable, patient and responsive. Look no further for your database programming needs. Thanks Tom and ExcelHelp.com!Randy Hartnell
We anticipate a long business partnership with ExcelHelp and look forward to working with them. I have been nothing but impressed with your firm’s services, your team members tenacity and the overall results. Sometimes you have to travel a bumpy road trying different firms until you find the one that fits.Posted By: Charlene Faber
Chief Operation Officer, Current Builders
I’d like to express my gratitude for the help and cooperation we’ve received from your entire organization during the development of our Modeling Utility. Over the past several weeks we’ve engaged key customers worldwide...We have confidence that this effort will add significant capability and value to the overall success of the program.Business Development Manager
Network Architecture and Strategy
Posted By: Marty M.