VBA for Excel Lesson 8: Modifying a Recorded Macro in Excel

If you are using Excel 1997 to 2006 see lesson 8 here

You have started using the Macro Recorder to write code for you. Here is an example where the macro recorder overdoes it and even makes a mistake that you will have to correct. For example when we develop tables and reports we will often select a set of cells and use the borders' icon from the "Home" ribbon to add small borders around all cells. When working with cells' borders you will always use the macro recorder because it would be fastidious to write the code by hand.

So start the macro recorder, select cell A1 and use the border icon shown above. Then use the wheel of your mouse to scroll down the sheet a few line. Stop the recorder and go to the Visual Basic editor to see the following code in module 1.

Sub Macro1()

'' Macro1 Macro
'
'

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone

    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

ActiveWindow.SmallScroll Down:=21

End Sub

Now if you try to run this macro you will get an error message. The error comes from the sections about xlInsideVertical and xlInsideHorizontal. These sections would be necessary if you were working with a set of cells (A1 to V34) because there would be borders between cells and there would be xlInsideVertical and xlInsideHorizontal borders.

You will notice that this is a series of "With/End With" statements (4 for the edges and 2 for the insides). You can copy this code anytime you need it but make sure that you are not generating an error so remove the sections about "xlInsideHorizontal" and "xlInsideVertical" .

You can also remove the first two lines of code about the "xlDiagonal..." and any of the statements that you do not need like the SmallScrollDown line. Formatting cells through VBA is time consuming so slim down your code as much as possible.

Now you can run the macro and you will not get an error message.

There are three sections in this website and in the downloadable tutorial on VBA for Excel.

Section 1: Excel Macros Programming: 10 lessons
This section is about recording, writing, modifying and testing macros in the Visual Basic Editor. You will learn about security and discover "events" (an event is what starts the macro).
Section 2: Excel VBA Vocabulary: 13 lessons
Developing a macro is like talking with Excel and to do so you need to use a language called VBA. There are over a thousand VBA words but you will really need only 100 to talk with Excel about workbooks, worksheets, cells, databases, loops and to develop simple and powerful macros.
Section 3: Forms and Controls in VBA for Excel: 10 lessons
The form is a small window that allows the user to submit values that will be used by the macro. To these forms you add controls (command buttons, text boxes, list boxes and others).

Modifying Macros in the Visual Basic Editor of Excel
Links to theTop of the Page