To view a macro's code open the Macro dialog box (Fig. 14) (Tools > Macro > Macros or [Alt]+[F8]) then select the macro from the list and click the Edit button. Excel opens the Visual Basic Editor window. Here you can view, edit and manage all your Excel VBA macros and also write new ones. Each Microsoft Office program which permits VBA programming has its own Visual Basic Editor similar to this one.
(NOTE: You can not use the Edit button to view macros stored in Personal.xls because this is a hidden workbook. To view the code inside a hidden workbook you must open the Visual Basic Editor as described below (see: Opening the Visual Basic Editor on page 32) and locate it using the Project Explorer .)
When you open the Visual Basic Editor from the Macro dialog box it takes you direct to the code procedure for the macro that was selected when you clicked the Edit button on the dialog box.
More detailed information about using the Visual Basic Editor is given in the section entitled The Visual Basic Editor on page 32.
This section examines the code that was recorded in the Step-by-Step exercises earlier in this document...
(NOTE: The VBA code shown in the examples below has been given line numbers. Most VBA developers do not bother to number their code like this. It has been done here for clarity and ease of identification in the t ext. The Visual Basic Editor does not automatically number lines of code although numbering can be added manually if required.)
The Simple Macro
Here is the code recorded for t he Simple Macro (see: Step-by-Step: Record a Simple Macro on page 10) which contained a number of simple cell formatting commands.
1 Sub Macro1()
2 '
3 ' Macro1 Macro
4 ' Macro recorded 30/03/2005 by Martin Green
5 '
6 '
7 Selection.Font.Bold = True 8 Selection.Font.Italic = True 9 With Selection
10 .HorizontalAlignment = xlCenter 11 .VerticalAlignment = xlBottom 12 .WrapText = False
13 .Orientation = 0 14 .AddIndent = False 15 .IndentLevel = 0 16 .ShrinkToFit = False
17 .ReadingOrder = xlContext 18 .MergeCells = False
19 End With
U n i v e r s i t y o f G r e e n w i c h S t u d e n t s ' E d i t i o n
f o r E d u c a t i o n a l U s e O n l y
associated with the current procedure. There is a corresponding End Sub line at the end of the macro (Line 25).
Lines 2 to 6 are Comments, usually coloured green in the Visual Basic Editor (you can choose your own colour scheme). Comments are pieces of descriptive text or notes. In this instance the comment text has been copied by t he Macro Recorder from the description that was supplied. Comments are always prefixed by an apostrophe ( ') which instructs the code compiler to ignore what follows in that line and not try to execute it as VBA code.
When the Bold toolbar button was clicked the Macro Recorder wrote Line 7 which tells Excel to apply the Bold property to the Font property of the Selection object.
Line 8 is very similar and was written when the Italic button was clicked. It tells Excel to apply the Italic property to the Font property of the Selection object.
When the macro was being recorded the next step was to click the Center align button. This resulted in eleven lines of code (Lines 9 to 19). Here is an example of the Macro Recorder generating far more code than is necessary. This often happens when recording macros, especially when you have to visit a dialog box containing many options. Although here only one option was changed (the HorizontalAlignment of the Selection was changed to xlCenter) all the default settings have been recorded as well. The items listed can all be found on the Alignment tab of the Format Cells dialog box (Fig. 28).
Fig. 28 The Macro Recorder records all the defaults from the Alignment tab of the Format Cells dialog box.
The Macro Recorder has used a With Statement to bracket together a number of code lines all of which refer to the same object. This coding technique is used to avoid repetition of the same code statement (in this case the keyword Selection). With Statements start with the keyword With and end with End With.
When a Fill Color was chosen the Macro Recorder wrote another With Statement (Lines 20 to 23) defining the ColorIndex of the Interior as well as the type of Pattern. The latter is the default setting and was not changed so it too is superfluous and can be omitted.
Finally, when the Font Color was chosen Line 24 res ulted specifying the ColorIndex.
Generally speaking, the fewer lines of code that are used to achieve a specific task the
better. Concisely written code usually runs faster and more efficiently. Code lines referring to default settings can normally be removed without altering the macro. Also, because the actions were carried out in a particular order which had no special bearing on the macro's outcome, some repetition has resulted (i.e. Font properties specified in different places instead of all together).
U n
This particular macro could be safely edited to remove the unnecessary code statements and rearranged to reduce the total number of lines considerably. The resulting macro will run faster because the code compiler has less work to do. It is also much easier to read and will carry out all the tasks required of it...
1 Sub Macro1() 2 With Selection
3 .Font.Bold = True 4 .Font.Italic = True 5 .Font.ColorIndex = 3
6 .HorizontalAlignment = xlCenter 7 .Interior.ColorIndex = 6
8 End With
9 End Sub
The FillEmptyCells Macro
The code listing for the next macro (see: Step-by-Step: Record and Test a More Complex Macro on page 13) was generated much more efficiently by the Macro Recorder...
1 Sub FillEmptyCells()
2 '
3 ' FillEmptyCells Macro
4 ' Fills each empty cell in a selection with data from the cell above.
5 '
6 '
7 Selection.CurrentRegion.Select
8 Selection.SpecialCells(xlCellTypeBlanks).Select 9 Selection.FormulaR1C1 = "=R[-1]C"
10 Selection.CurrentRegion.Select 11 Selection.Copy
12 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False
13 Application.CutCopyMode = False 14 End Sub
The keyboard shortcut [Control]+* was used to Select the CurrentRegion of the Selection in Line 7 and again in Line 10.
The command Edit > Go To > Special > Blanks was used to Select the SpecialCells(xlCellTypeBlanks) of the Selection in Line 8.
Line 9 shows how, with m ultiple cells selected, the Macro Recorder interpreted the keyboard command of [Equals][Up Arrow] followed by [Control]+[Enter]. The latter is the "block fill"
command which tells Excel to enter the typing into all the cells that are currently selected.
The VBA simply refers to this as the Selection. Then FormulaR1C1 inserts a formula into the cells using R1C1 (or Row Column) notation. This is a way of referring to cell addresses in a general (i.e. Relative) way. Cell addresses in formulas entered this way get translated as they are written into the cell. The formula is presented as a string (i.e. a piece of text) so must be enclosed between pairs of quote marks: "=R[-1]C". This refers to the cell one row above (R[-1]) in the same column (C) (NOTE: this could have been written "=R[-1]C[0]"
but when there is no difference in row or column the zero is usually omitted.)
Having selected the CurrentRegion again in Line 10 the instruction is given to Copy the Selection in Line 11 then Paste it using PasteSpecial in Line 12.
As in the previous example, some default items have been entered in Line 12 ( Operation, SkipBlanks and Transpose). The only essential item here is Paste:=xlPasteValues. The others can be omitted.
U n
Although it might appear that, since the Selection object is referred to at the beginning of each line from Lines 7 to 12, a With Statement could be used to good effect here. But in this case it would not be appropriate (and the code would not work properly) because although the term Selection is used each time the actual cells it refers to changes several times (i.e.
it is a different selection each time). With Statements can only be used to group together commands referring to precisely the same thing.
The AbsoluteReferences and RelativeReferences Macros
An earlier exercise (see: Step-by-Step: Compare Relative and Absolute References on page 18) demonstrated the effect of choosing to record Absolute or Relative references. Here you can see the VBA code that was recorded for the two different macros...
Sub AbsoluteReferences()
In the exercise, two macros were recorded. Before each macro was recorded the cell C10 was selected. The operation that was recorded consisted simply of selecting cell B5 .
The first macro ( AbsoluteReferences) was recorded without the Relative References button pressed in. In the resulting code the instruction is given to Select the Range("B5"). This is quite specific and refers to an actual cell address. This is why, regardless of which cell is selected when the macro is run, the result is always the selection of cell B5 .
The second macro (RelativeReferences) was recorded with the RelativeReferences button pressed in. The resulting code statement did not specify a particular cell to be selected.
Instead it used the Offset property of the currently selected cell (t he ActiveCell) to refer to another location relative to the current cell. Offset uses Row Column notation. Negative numbers (i.e. numbers preceded by a minus sign) mean "up" when referring to rows and "to the left" when referring to columns. Positive numbers (no plus sign is necessary) mean
"down" when referring to rows and "to the right" when referring to columns. A zero (0) denotes the same row or column as the current cell.
The RelativeReferences macro refers to a cell 5 rows above (-5) and one column to the left (-1) of the ActiveCell. At this point the selection is further defined as Range("A1") which might appear rather confusing and really only applies if a block of cells were being selected.
The line would normally be written...
ActiveCell.Offset(-5, -1).Select
As a result, a single cell would be selected. If instead you wanted to select a block of cells, say three columns wide by five rows high, starting at that cell you would instead write...
ActiveCell.Offset(-5, -1).Range("A1:C5").Select