Program by Levels

Tutorial on VBA for Excel (Macros)

bringing you the simplest and most efficient ways
to do things in VBA for Excel

VBA Lesson 8 of 33: Modifyng a Macro in Excel
(Levels: Beginners, Intermediate)

Note: For a better comprehension of the lessons in section 1 on the Visual Basic Editor and the Macro Recorder www.excel-vba.com has created a workbook (vba-tutorial-editor.xls) one of the 25 that are included with the VBA for Excel Desktop Tutorial.

Let's begin by modifying the VBA procedure that we have recorded  earlier (Macro1). Open Excel, open the workbook "vba-tutorial-editor.xls" and go to the Visual Basic Editor. Double click on Module1 in the Project window and the following code will appear in the Code window.

Click in the code window and print the module for future reference "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 well so we could leave it alone. But just to make it  clearer let's make it simpler.

First let's discover a neat trick. Click on the line that reads Selection.Font.ColorIndex = 3 between the Color and Index. Look at the "Standard" toolbar at the top of your screen and you will see this:

This means that the cursor is on line 15 column 25 of the module. This functionality is very useful when you talk with somebody else on the phone about your procedure.

The first line of code reads like this::
Range("A1").Select

We then entered 34 in it. The MR writes
ActiveCell.FormulaR1C1="34"
You will rarely use the FormulaR1C1 property. You can replace this line simply by:
ActiveCell.Value=34
note that there are no apostrophes around 34. This means that 34 is a number. You would need apostrophes if you were entering a text in the cell like "Peter" or a serial number like "3452945" .

Line 2 and 3 of the procedure read like this:
Range("A2").Select
ActiveCell.FormulaR1C1 = "55"  
There is no need to select a cell before you enter a value or a formula in it. 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 a cell 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"
The form "=R[-2]C+R[-1]C" is called a relative formula. You will only use this format in certain tight spots. R[-2]C  basically means 2 rows up same column. Again we will avoid the FormulaR1C1 and 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

Let's change the name of the VBA procedure to "proTest1" and remove the comments. It now looks like this:

Sub proTest1()

        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

This page is one of the 33 lessons from the
Tutorial on VBA for Excel (Macros)
VBA for Excel made simple