1.
Selection.SpecialCells(xlCellTypeBlanks).Select
This
statement selects the blank special cells of the original selection. (The
word SpecialCells is a method that handles many of the options
in the Go To Special dialog box.)
2.
Selection.FormulaR1C1 =
"=R[-1]C"
This
statement assigns =R[-1]C as the formula for the entire selection. When you
entered the formula, the formula you saw was =C2, not =R[-1]C. The formula =C2
really means "get the value from the cell just above me," but only if
the active cell happens to be cell C3. The formula =R[-1]C also means "get
the value from the cell just above me," but without regard for which cell
is active.
You could
change this statement to Selection.Formula = "=C2" and the macro
would work exactly the same—provided that the order file you use when you run
the macro is identical to the order file you used when you recorded the macro
and that the active cell happens to be cell C3 when the macro runs. However, if
the command that selects blanks produces a different active cell, the revised
macro will fail. The macro recorder uses R1C1 notation so that your macro will
always work correctly.
3.
Selection.CurrentRegion.Select
Selection.Copy
Selection.PasteSpecial
Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
These
statements select the current region, convert the formulas to values, cancel
copy mode, and select cell A1.
4.
Sub AddDates()
Range("A1").Select
Selection.EntireColumn.Insert
ActiveCell.FormulaR1C1 = "Date"
Range("A2").Select
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "Nov-2007"
Range("A1").Select
End Sub
This macro
is pretty straightforward. Notice that the statement that enters the word Date uses
the word ActiveCell as the object, changing the
"formula" of only the active cell, whereas the statement that enters
the actual date uses the word Selection as the object,
changing the "formula" of the entire range of selected cells. When
you enter a formula using the Enter key alone, the macro uses the word ActiveCell.
When you enter a formula using Ctrl+Enter, the macro uses the word Selection.
(If the selection consists of only a single cell, ActiveCell and Selection are
equivalent.)
In
addition, using just the Enter key changes the selection to the next cell down.
That's why the Range("A2").Select statement is in the macro. It
doesn't hurt anything, but it is also unnecessary. Removing unnecessary statements
from a recorded macro makes it easier to read, and easier to modify in the
future if you ever need to.
5.
Selection.End(xlDown).Select
This
statement is equivalent to pressing Ctrl+Down Arrow. It starts with the active
cell, searches down to the last nonblank cell, and selects that cell.
6.
Workbooks.Open Filename:="C:\MSP\ExcelVBA07SBS\Orders.xlsx"
In the
Visual Basic editor window, the statement that opens the master list
should be highlighted:
Workbooks.Open
Filename:="C:\MSP\ExcelVBA07SBS\Orders.xlsx"
This
statement opens the master list workbook.
TIP
If you
remove the path from the file name, leaving only the actual file, the macro
looks for the file in the current folder. That would be useful if you move the
project to a new folder. However, if the master list is always in the same
location, but the source file may be in different locations, it is better to
leave the full path of the master file.
7.
ActiveWorkbook.Close
SaveChanges:=False
The SaveChanges
argument answers the dialog box's question before it even gets asked. While
testing, you can have the macro not save the workbook. Once
you're ready to really use the macro, you can change the argument to True.
8.
ActiveWindow.SelectedSheets.Delete
The
statement refers to the "selected sheets of the active window"
because it's possible to select and delete multiple sheets at the same
time. (Press and hold the Ctrl key as you click several sheet tabs to see how
you can select multiple sheets. Then click an unselected sheet without using
the Ctrl key to deselect the sheets.) Because you're deleting only one
sheet, you could change the statement to ActiveSheet.Delete if you wanted, but
that isn't necessary.
9.
Application.DisplayAlerts = False
DisplayAlerts
is a property of the Excel application. When you set the value of this property
to False, any confirmation prompts that you would normally see are treated as
if you had selected the default answer. The DisplayAlerts setting lasts only
until the macro finishes running, so you don't need to set it back to True. You
do, however, need to be careful to never run this macro when the active sheet
is something you care about. Naturally, you should also be careful to save your
work often and keep backup copies.
(From the book
Microsoft® Office Excel® 2007 Visual Basic® for Applications Step by Step
by Reed Jacobson)
