Analyzing the Macro Code

All macro procedures begin with the keyword Sub and end with the keywords End Sub. After the Sub keyword comes the actual name of the macro, followed by a set of parentheses. Between the keywords Sub and End Sub are statements that Visual Basic executes each time you run your macro. Visual Basic reads the lines from top to bottom, ignoring the statements preceded with an apostrophe (see the previous section on comments) and stops when it reaches the keywords End Sub.

Notice that the recorded macro contains many periods. The periods appear in almost every line of code and are used to join various elements of the Visual Basic for Applications language. How do you read the instructions written in this language? They are read from the right side of the last period to the left. Here are a few statements from the WhatslnACell procedure:

Range("A1:A3").Select Select cells A1 to A3.


Insert a row in the selected area. Because the previous line of code selects three cells, Visual Basic will insert three rows.

ActiveCell.FormulaR1C1 = "Text"

Let the formula of the active cell be "Text." Because the previous line of code, Range("B1").Select, selects cell B1, B1 is currently the active cell, and this is where Visual Basic will enter the text.

With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid .PatternColorIndex = xlAutomatic

End With

This is a special block of code that is interpreted as follows: Set the color for the interior of the currently selected cells to red (ColorIndex = 3), set the interior pattern to solid (xlSolid), and specify the default pattern for the selected cells (xlAutomatic).

The block of code that starts with the keywords With and ends with the keywords End With speeds up the execution of the macro code. Instead of repeating the following instructions each time, the macro recorder uses a shortcut.

Selection.Interior.ColorIndex = 3 Selection.Interior.Pattern = xlSolid

Selection.Interior.PatternColorlndex = xlAutomatic

It places the repeating text, Selection.Interior, to the right of the keyword With and ends the block with the keyword End With.

0 0

Post a comment