Lesson 5: Modifying Macros in the Visual Basic Editor

Let's begin this chapter my modifying the VBA procedure that you have created with the macro recorder earlier. Open Excel, open the workbook "VBATest1.xls" and go to the Visual Basic Editor. Double click on Module1 in the VBA project window and the following code will appear in the Code window.

Click in the code window and print the module for future refernece "File/Print/Current Module"

Print this page and follow the instructions step by step.

The Macro Recorder (MR) has a very special way to write code. The procedure above works so we could leave it alone but just to make it clearer let's make it simpler. You can replace each line of code yourself or copy/paste the entire new procedure below from this web page onto the old procedure in the VBE

When I started the Macro Recorder cell A1 was selected and I entered 34 in it. The MR writes
ActiveCell.FormulaR1C1="34"
I never use and you will never use the FormulaR1C1 property. Secondly we will add a line of code to tell Excel to start this procedure in cell A1 otherwise, 34 will be entered in the cell that is selected when we start the procedure. Cell A1 is the ActiveCell and you can replace this line simply by:
Range("A1").Select
ActiveCell.Value=34

Line 2 and 3 of the procedure read like this:
Range("A2").Select
ActiveCell.FormulaR1C1 = "55" 
Again we will avoid the FormulaR1C1 thing and we wont select the cell we will just give it a value so replace lines 2 and 3 by this:
Range("A2").Value=55
Remember that you don't need to select acell to give it a value

Line 4 and 5 of the procedure read like this:
Range("A3").Select
ActiveCell.FormulaR1C1 = "=R[-2]C+R[-1]C" 
Again we will avoid the FormulaR1C1. Again we wont select the cell we will just enter a formula so replace lines 4 and 5 by this:
Range("A3").Formula="=A1+A2"

Line 6 and 7 of the procedure read like this:
Range("A2").Select
Selection.Font.ColorIndex = 3
Here we wont select the cell we will just specify that we want to change the color of the font so replace lines 6 and 7 by this:
Range("A2").Font.ColorIndex = 3
Three is red. I always use the MR in these situation because I don't feel like remembering all the color codes.

Line 8 to 12 of the procedure read like this:
Range("A1").Select
With Selection.Interior
        .ColorIndex = 41
        .Pattern = xlSolid
End With
The MR uses a lot of "With..End With" but I don't. The code developed by the MR would read in plain English: Select cell A1 (cell A1 becoming the Selection) then for the Selection.Interior make the ColorIndex 41 and the Pattern xlSolid. Here is a simpler version so replace lines 8 to 12 by this:
Range("A1").Interior.ColorIndex = 41
Range("A1").Interior.Pattern = xlSolid

Line 13 to 25 of the procedure read like this:
Range("A3").Select
With Selection.Font
        .Name = "Arial"
        .Size = 24
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
End With
Again MR uses "With...End With" and also modifies ALL the properties of the font each time. We just want the font's size to be changed to 24 so replace lines 13 to 25 by this:
Range("A3").Font.Size= 24

The VBA procedure or macro called "Macro1" now looks like this:

Sub Macro1()

' Macro1 Macro
' Macro recorded 5/27/2005 by Peter'

    Range("A1").Select
    ActiveCell.Value = 34
    Range("A2").Value = 55
    Range("A3").Formula = "=A1+A2"
    Range("A2").Font.ColorIndex = 3
    Range("A1").Interior.ColorIndex = 41
    Range("A1").Interior.Pattern = xlSolid
    Range("A3").Font.Size = 24    

End Sub

We will test it in lesson 6