Despite the aforementioned error checking, Excel may still encounter an error at run-
time. If this happens, click the Debug button. The line that caused the error is high-
lighted in yellow. Hover your mouse cursor over any variable to see the current value of
the variable.
When you are in Debug mode, use the Debug menu to step line by line through code.
You can toggle back and forth between Excel and VBA to see the effect of running a
line of code on the worksheet.
Other great debugging tools are breakpoints, the Watch window, the Object Browser,
and the Immediate window. Read about these tools in the Excel VBA Help menu.
The Macro Recorder
Excel offers a macro recorder that is about 90% perfect. Unfortunately, the last 10% is frus-
trating. Code that you record to work with one dataset is hard-coded to work only with that
dataset. This behavior might work fine if your transactional database occupies cells
A1:K415501 every single day, but if you are pulling in a new invoice register every day, it is
unlikely that you will have the same number of rows each day. Given that you might need to
work with other data, it would be a lot better if Excel could record selecting cells using the
End key. This is one of the shortcomings of the macro recorder.
In reality, Excel pros use the macro recorder to record code but then expect to have to clean
up the recorded code.
Understanding Object-Oriented Code
VBA is an object-oriented language. Most lines of VBA code follow the Noun.Verb syntax.
However, in VBA, it is called
Object.Method. Objects can be workbooks, worksheets, cells,
or ranges of cells. Methods can be typical Excel actions, such as
.Copy, .Paste, and
.PasteSpecial.
Many methods allow adverbs—parameters you use to specify how to perform the method. If
you see a construct with a colon/equal sign, you know that the macro recorder is describing
how the method should work.
You also might see the type of code in which you assign a value to the adjectives of an
object. In VBA, adjectives are called properties. If you set
ActiveCell.Font.ColorIndex = 3,
you are setting the font color of the active cell to red. Note that when you are dealing with
properties, there is only an equal sign, not a colon/equal sign.
Learning Tricks of the Trade
You need to master a few simple techniques to be able to write efficient VBA code. These
techniques will help you make the jump to writing effective code.
Chapter 11 Using VBA to Create Pivot Tables
234
11
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 234
To Next Page
Loading...
Need help?
General
