Macro Recorder Bloat

The macro recorder is a great tool for quickly finding out how to code something without poring over reference manuals. However, there is a drawback. The macro recorder doesn't know what you plan to do next, so it records at a minute level of detail. The other problem with the macro recorder is that it captures every move that the user makes, but many of these actions can be done in VBA without having to replicate selecting cells, copying and pasting, and other user actions.

Here is some code that was captured by the macro recorder. The user simply wants to copy a value from a cell that has a formula, and paste the value to another cell.

    Range("B1").Select
    Selection.Copy
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    

What would we change?

First, it is not necessary to Select a range to either determine its value or assign it a value. There are a few reasons why you would want to avoid Select:

1. A Select causes an action in the worksheet. This takes time to execute. For long and complex actions this really slows things down.
2. It is visible to a user (although we can hide it from the user if we wish).
3. It adds clutter to code.

We can directly copy a range without Select, and we can paste to a range without Select:

    Range("B1").Copy
    Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    

But for this, we don't even need to do a copy and paste! An Excel user has no other way to get data from one cell to another, but we can do a simple assignment in VBA:

    Range("A1").Value = Range("B1").Value
    

Compare that single line of code to the code generated by the macro recorder. Here's another example. Suppose you want to modify the page layout to be landscape. Here is the code generated by the macro recorder:

    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.7)
        .RightMargin = Application.InchesToPoints(0.7)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With

All we did was change from Portrait to Landscape, but the macro recorder has included every possible attribute of the page layout. To make the one change we wanted, we can simplify to this code:

    ActiveSheet.PageSetup.Orientation = xlLandscape

You'll see that we left the qualification to ActiveSheet here. If you want to create a button that runs this macro for whatever sheet you're looking at, then using ActiveSheet is exactly what you need.

These are the primary artifacts left by the macro recorder that you will want to clean up in your code, but there are others.