Microsoft Office Tutorials and References
In Depth Information
Weapons of Mass Debugging
The Locals window can help you in situations
when you get a runtime error and the
offending line of code involves a variable. The Locals
window displays the variables and their values
for the macro(s) you are currently running.
Figure 17-13 shows a very simple macro that
attempted to activate a worksheet based on the
object variable mySheet . Because that variable
was never set with an identifying worksheet,
a runtime error occurred because VBA could
not determine which sheet the mySheet variable
was referring to. While in Break mode in this example, the Locals window shows that mySheet is
set to Nothing , telling you that you forgot to include a Set statement for mySheet .
The Immediate window allows you to type in or paste a line of VBA code, which will execute when
you press the Enter key. To see the Immediate window, you can click its icon button on the Debug
toolbar, or from the menu click View ➪ Immediate Window, or press Ctrl+G.
If it hasn’t happened already, you’ll soon find yourself using the Immediate window for reasons
having nothing to do with errors. The Immediate window is a great way to execute commands quickly
without needing to create a formal macro to get the task done, such as in the following examples.
To eliminate leading apostrophes in cell values, which can occur when manually entered
or imported from external source data, you can type Activesheet.UsedRange.Value =
Activesheet.UsedRange.Value and press the Enter key.
To delete hyperlinks but keep the underlying cell value, you can type ActiveSheet.Hyperlinks
.Delete and press the Enter key.
When querying some fact or condition, precede your statement with
a leading question mark. If you want to know the version of Excel
you are using, type ? Application.Version and press the Enter
key. As shown in Figure 17-14, when I entered that statement into
the Immediate window, the value 14.0 was returned, which is Excel’s
The point to be made about the Immediate window is that it is a proactive tool. If you are
wondering whether a line of code will fail, or whether it will produce the result you have envisioned, you
can test that code line in the Immediate window and see the results before taking your chances of
putting it into your code.
The Watch window allows you to watch a variable or an expression change as your code executes.
You’d normally do this with values that are associated with runtime errors, so you can see at what
point the VBA expressions produced a value that might have caused the error.