VBA in Excel - Properties, Methods and Events - 2

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)