VBA for Excel Lesson 8: Modifying a Macro  in Excel

This is an excerpt from Pierre Leclerc (www.excel-vba.com)

Note: If you are using Excel 2007 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 icon       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
' Macro recorded 2/7/2010 by Peter Leclerc
'
'

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

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

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

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

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

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

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

    ActiveWindow.SmallScroll Down:=15

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.

Go to the next lesson
Lesson 9: VBA Events in Excel

VBA for Excel Lesson 8: Modifying a Recorded Macro in Excel (2007)

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.

Go to the next lesson
Lesson 9: VBA Events in Excel