Cleaning Up the Macro Code

Now, as you review and analyze your macro code line by line, you may notice that Excel recorded a lot of information that you didn't intend to include. For example, after selecting cells containing text, in addition to setting the font style to bold and the color to violet, Excel also recorded the current state of options on the Font tab—font name, font size, strike-through, superscript, subscript, shadow, and underline. Take a look at the following code fragment:

With Selection.Font .Name - "Arial" .FontStyle - "Bold" .Size - 10

.Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False

.Underline = xlUnderlineStyleNone .Colorlndex = 13 End With

When you use dialog boxes, Excel always records all the settings. These additional instructions make your macro code longer and more difficult to understand. Therefore, when you finish recording your macro, it is a good idea to go over the recorded statements and delete the unnecessary lines.

1. In the following block of code, delete the lines that are crossed out:

With Selection.Font .Name - "Arial" .FontStyle - "Bold" .Size---10

.Strikethrough - False ■Superscript - False ■Subscript - False .OutlineFont - False ■Shadow - False

■Underline - xlUnderlineStyleNone .Colorlndex - 13 End With

After the cleanup, only two statements should be left between the keywords With and End With. These statements are the settings that you actually changed in the Format dialog box when you recorded this macro:

With Selection.Font .FontStyle = "Bold" .Colorlndex = 13 End With

2. Locate the macro code that formats cells containing numbers, and make the necessary changes using the example below:

' Find and format cells containing numbers With Selection

.SpecialCells(xlCellTypeConstants, 1).Select .Font.Colorlndex = 11 ' Sets the font color to Violet End With

Range("C6").Select

3. Locate the macro code that formats cells containing formulas, and make changes following the example below:

' Find and format cells containing formulas

Selection.SpecialCells(xlCellTypeFormulas, 23).Select With Selection.Font .FontStyle = "Bold" .Colorlndex = 3 End With

4. Locate the following two lines of code: Range("A1:A3").Select Selection.EntireRow.Insert

5. Replace the above two lines of code with the following line: Range("A1:A3").EntireRow.Insert

Notice that the macro recorder uses the R1C1-style notation to set the formula for the selected cell:

ActiveCell.FormulaR1C1 = "Text" ActiveCell.FormulaR1C1 = "Numbers" ActiveCell.FormulaR1C1 = "Formulas"

To select the active cell, the macro recorder uses the word ActiveCell once, and it applies the word Selection another time. Both of these words are called properties. You will learn about properties in Chapter 2. When only one cell is selected, you may use ActiveCell or Selection interchangeably.

0 0

Post a comment