Figure 1 shows the window configuration I always use when writing VBA. If the Immediate window isn’t visible in your VBE, you can display it by pressing Ctrl + G or selecting View/Immediate Window from the main menu. I’ve changed the background color of the window for reasons I’ll save for a future tip. By default, your Immediate window will have a white background.
Figure 1: The VBE with the Project,
Properties, and Immediate windows open. Click to enlarge
One use of the Immediate window is to evaluate individual VBA statements. In this scenario, you precede the VBA statement you want evaluated with a question mark. You can evaluate the values of object properties, functions, and mathematical statements, among other things. For example, enter?ThisWorkbook.Name
In the Immediate window and press Enter. Notice that a couple of things happen. Of course, after you pressed Enter, the name of the current workbook was displayed on the next line. You probably noticed another very useful feature called Intellisense after you entered the period (.) between ThisWorkbook and Name.
Intellisense is a feature that displays a drop-down list of applicable properties and methods given a VBA object. For example, ThisWorkbook is a Workbook object. The items listed in the drop-down box are the properties and methods (collectively referred to as members) of a Workbook object. Properties are represented by the hand and index card icon, while methods have a “flying square” icon next to them.
For now, it’s not important to know the difference between properties and methods, but it would be good to start mentally paying attention to the fact there is a difference. Figure 2 shows an example of Intellisense in the Immediate window.
Figure 2: An example of Intellisense
in the Immediate window. Click to enlarge
Why is this so important, you may be wondering? The reason is that it is a very good way to learn about (and experiment with) VBA statements and the various object models (in this case Excel).You can also use the Immediate window to execute VBA statements — rather than displaying the value of a given property, you can change the property’s value. For example, last week we changed the Visible property of a Worksheet object using the Properties window in conjunction with the Project window. You could also perform this feat using the Immediate window. Try this:
1. In the Immediate window enter:
?ThisWorkbook.Worksheets(2).Visible
2. Press Enter. Notice that the value of the Visible property is displayed.
3. In the Immediate window enter (without a question mark):
ThisWorkbook.Worksheets(2).Visible = xlSheetVeryHidden
4. Switch to Excel and notice that the worksheet is very hidden.
5. Switch back to the VBE and enter:
?ThisWorkbook.Worksheets(2).Visible
6. Notice that the Visible property has been changed.
An example of this exercise is shown in Figure 3.
Figure 3: Using the Immediate
window to change a property value. Click to enlarge
The final use of the Immediate window I’ll discuss is as an output window using Debug.Print when executing code. Typically you would display diagnostic information in the Immediate window. This can be preferable to using the MsgBox function because if there are several things you want to display, the MsgBox method can grow tiresome rather quickly.As an example, try the following exercise.
1. If there is text in the Immediate window, you can clear it all by putting the cursor in the Immediate window, pressing Ctrl + A, and then pressing Delete.
2. Insert a module by selecting Insert/Module.
3. Enter the following procedure in the module:
Sub WorksheetVisibilityReport()
Dim ws As WorksheetDebug.Print "***********************"
Debug.Print "xlSheetHidden = 0"
Debug.Print "xlSheetVeryHidden = -1"
Debug.Print "xlSheetVisible = 1"
Debug.Print "***********************" & vbCrLfFor Each ws In ThisWorkbook.Worksheets
Debug.Print "The Visible property of " & ws.Name & " is " _
& ws.Visible & "."
Next
End Sub4. To execute the procedure, place the caret anywhere inside the procedure and press F5.
A completed example of this exercise is shown in Figure 4.
Figure 4: Using Debug.Print to
output to the Immediate window Click to enlarge
Next week I’ll wrap up the discussion of various VBE windows by introducing the Locals window, another great way to learn about and find useful objects that you can manipulate with VBA code.