Part VI: Automating Excel
See Chapter 42, “Introducing Visual Basic for Applications,” for more on recording modes.■ (^) If you create a macro that loops through each cell in the current range selection,
be aware that the user can select entire columns or rows. In such a case, you need
to create a subset of the selection that consists only of nonblank cells. Or, you can
work with cells in the worksheet’s used range (by using the UsedRange property).
■ (^) Be aware that Excel allows you to select multiple ranges in a worksheet. For exam-
ple, you can select a range, press Ctrl and then select another range. You can test
for this in your macro and take appropriate actions.
The examples in the following sections demonstrate these points.
Copying a range
Copying a range is a frequent activity in macros. When you turn on the macro recorder
(using Absolute recording mode) and copy a range from A1:A5 to B1:B5, you get a VBA
macro like this:
Sub CopyRange()
Range("A1:A5").Select
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
This macro works, but it’s not the most efficient way to copy a range. You can accomplish
the same result with the following one-line macro:
Sub CopyRange2()
Range("A1:A5").Copy Range("B1")
End Sub
This code takes advantage of the fact that the Copy method can use an argument that
specifies the destination. Useful information about properties and methods is available in
the Help system.
Most of the examples in this chapter use unqualified object references. A qualified object reference is one where
you explicitly tell VBA which object you want by identifying its Parent objects. For example, Range("A1") is
unqualified because we haven’t told VBA which worksheet it’s on. The fully qualified version is Application.
Workbooks("MyBook").Worksheets("MySheet").Range("A1").
When you use an unqualified range reference in a standard module, VBA assumes you mean the ActiveSheet in
the ActiveWorkbook. This saves you from having to type the whole string of parents if that is indeed what you
mean. If you want or need to be explicit, consider using an object variable as described in “Simplifying object refer-
ences” later in this chapter.