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:
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.