Let's face it — the macro recorder in Excel leaves a lot to be desired. Sometimes it records, sometimes it doesn't. When it does, it isn't always what you want or need. It's maddening, especially when there's some part of the object model you don't know about and are trying to learn. You end up with nothing, or too much code which clutters up your procedure (and you're afraid to remove it because you don't know which parts are required).
If you're looking to learn VBA, or like me you learned how to write VBA using the built-in macro recorder (and a whole lot of Google searches), you might be wondering about some of its shortcomings and how to overcome them. That is what I hope to show in this post.
Without further delay, here are the top reasons you shouldn't use Excel's macro recorder to learn VBA.
Overuse of ActiveSheet, Selection, Activate, Range
When I record myself selecting A1:D20 on a worksheet, I get the following code.
Range("A1:D20").Select
What's wrong with this? Nothing really, as long as I always want to select the range on the currently active sheet (which is assumed to be a sheet where a range can be selected, i.e. not a chart sheet). And since I didn't really do anything, I'm confused about why anything was recorded at all.
Selecting an object before performing some action on it is almost always unnecessary (another bad lesson the macro recorder teaches you). It leads to a few unintended consequences:
- Your VBA code runs (relatively) slower, since VBA now has to pointlessly interact with Excel (over and above what it's already doing to the worksheet).
- Changing the selection means you lose your place, unless you remembered to write code that records the active selection and restores it after your code is finished operating.
The macro recorder isn't smart enough to figure that out, dutifully recording nearly all your actions verbatim.
What I'm suggesting you do is avoid ambiguous or unqualified references such as ActiveSheet, Selection and so on. Each of these has an equivalent (Excel.Worksheet, Excel.Range, etc) which should be used to take advantage of Intellisense.
Here's some more recorded code. I leave it as an exercise for you to figure out what it does, but we can rewrite it as one line (two if you want to duplicate the actions actually performed to record the macro).
Range("C3:C15").Select
Selection.FormulaR1C1 = "1"
Selection.Copy
Range("D3").Select
ActiveSheet.Paste
This could be rewritten as
Range("C3:D15").Value = "1"
or
Range("C3:C15").Value = "1"
Range("C3:C15").Copy Range("D3:D15")
We could rewrite the above code several ways by using OOP. Instead of using ambiguous references such as ActiveSheet, Selection and Range, we could use
Dim wb As Excel.Workbook
Dim wksht As Excel.Worksheet
Dim sourceRange As Excel.Range
Dim destRange As Excel.Range
Set wb = Excel.Workbooks("book1")
Set wksht = wb.Sheets(1)
Set sourceRange = wksht.Range("C3:C15")
Set destRange = wksht.Range("D3:D15")
sourceRange.Copy destRange
This code uses a technique I like to call cascading references (I'm sure it has another name I'm not aware of). That's where you limit the number of "dots" by walking down the object hierarchy from parent to child, setting object references as you go. Otherwise I could just write this:
Excel.Workbooks("book1").Sheets(1).Range("C3:C15").Copy _
Excel.Workbooks("book1").Sheets(1).Range("D3:D15")
which still works but is harder to read and requires a bit more processing power to resolve.
Notice that another "feature" of the macro recorder sets the FormulaR1C1 property (not the Value property), regardless of whether we enter a formula or a value.
Doesn't really teach you the object model structure
Because of odd syntax like Selection-Select pairs, you won't learn the methods and properties of various objects, instead relying on concrete-bound code that methodically selects each item before acting on it. You certainly won't learn the most efficient way to get some things done, such as using variants to read and write to a worksheet.
Notice in the above examples that all our actions are recorded. But ActiveSheet.Paste is the end result no matter how we paste the range. If I press Ctrl-V, or go to Edit » Paste, the code is the same. The macro recorder isn't informed about the Office object model, which contains the CommandBars object that we use to build toolbars and execute button commands like the Paste option on the Edit menu.
The macro recorder seems like an afterthought, more structured for short procedures like formatting charts or worksheet cells.
Another useful article that extends these ideas is Tushar Mehta's Beyond the Macro Recorder.
Too much code!
Here is the code produced when changing the font for a single cell:
With Selection.Font .Name = "Tahoma" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With
The macro recorder doesn't know which options I've set, so it sets them all. All I really needed was Selection.Font.Name = "Tahoma". Strangely, it doesn't do this for other dialogs, such as when I toggle the Edit Directly In Cell feature. All I get is:
Application.EditDirectlyInCell = False
The macro recorder is well known for producing volumes of code, needlessly setting options that aren't involved in the actions performed (I didn't change the Strikethrough option, but the code sets it to False anyway).
Is there a better way?
My suggestion is to take a programming class, either covering generic object oriented concepts or a programming language such as Java or .NET. It teaches you how to code and how to approach different problems in a way that the macro recorder can't. Eventually you can start writing code from scratch, and use the macro recorder only as a supplement.
I rely on search engines rather than the macro recorder to discover new methods and properties, unless I need some code quickly and want to check if the macro recorder can produce any. Or, if I'm just flat out lazy and don't feel like making an effort. But I still use the Internet (and the Object Browser) to search for the appropriate syntax. Lurking in the Microsoft newsgroups is another great way to learn how to write code (if you know who to follow), and a search for "vba best practices" turns up a lot of quality links to sites with best practice advice for VBA.
Another great article about the macro recorder may be found here: Using the macro recorder in Microsoft Excel.
Got any more reasons to avoid the recorder?
About JP
14 Comments:
Hey.. good post JP… one more thing, the macro recorder in 2007 is slightly annoying too. It doesnt record all the events that you are doing. For eg. select a chart, hit ctrl+1 and change some formatting options. When you open the recorded macro, excel doesnt show all the steps you have done.
I completely agree with you on "take a programming class" part. Especially, if you have not learned OO programming during grad or something, it makes lot of sense to learn how to think like a programmer before plunging in to VBA.
But once you are pro, Macro recorder along with Google is your best friend.
Thanks for sharing, Chandoo!
When I was first learning VBA (in Excel 5.0), I tried some stuff with the macro recorder can could figure out some tiny tasks, but it taught me zero about the structure of the language like looping and IF statements. One of my colleagues had worked out a tutorial where you built up from the basics ("Hello, world!") to doing loops and tasks that were just like tasks we had done previously by hand (sometimes painfully). By the end of the day, we had a much better idea of what we needed to know to go home and be more productive.
The first part of the class was learning all of the cool debugging tools like watches and the object browser and how to turn off syntax checking on every line. The macro recorder does not help you learn those.
Nice post.
I like that approach, and the tutorials I've written (and posted here) follow the same track. Thanks for sharing.
I wouldn't say the recorder is useless at teaching you VBA. It's good for helping you either (a) build a skeleton of a long procedure that you can fix up and fill in later, or (b) get bits of syntax which are too obscure for the Object Browser.
It won't teach you about advanced structures, like looping or IF/THEN. It dumps lots of unnecessary statements in the code. It selects everything. But if you understand these limitations, it's not a big deal to make corrections.
I would agree with you, if I didn't feel like the macro recorder slowed my progress and started me off with just plain bad coding habits. Mind you, this was before I knew better.
Before you took my class, you mean? He he he.
JP:
I actually think the Macro Recorder is a great syntax checker. If I don't remember the syntax to create a pivot table, the macro recorder is a helpful partner.
Of course, I trim away all the fatty pieces the macro recorder likes to include.
Good post JP. For me, there has always been a separation between VB (the language, data types, loops, UI features like forms, and so on), and VB*A*, the hook into the Office app that on the one hand can make your productivity shine, but on the other hand invariably demands that you become familiar with the app's object model.
A programming class of most any sort can make one familiar with how procedural languages and OOP work in general, VB included, but when it comes to understanding a specific object model, you are pretty much on your own (except for Google, that is!) Oh, and the VBA Object Browser via F2, which can be helpful, but isn't always.
I agree that recorded macros usually produce undesirable code out of the box, but as a quick and dirty way to expose "just what is that object/property/method I am trying to hold/set/do" they can be exceedingly useful.
One thing I have found useful, but never seen mentioned, is breaking execution to inspect an object's (often lengthy) list of properties in VBA's Variables window. Your technique of "cascading references" makes this even easier.
Wholeheartedly agree. It is a useful tool when you know a bit of VBA in order to quqickly get that bit of information on a certain object/property, but it creates some very bad habits if you use it to create a macro say. It is quicker to cut a formatting macro from scratch than macro record it then tidy it up.
And of course, because VBA is dead in MS' eyes, they don't bother with it any more, hence all of the breaks in 2007.
Bob -
I get the feeling that much of the problem with the 2007 macro recorder is that they just didn't have time to do all the work. Hell, they didn't even finish the charting module. The macro recorder has been filled out in the 2010 beta.
Hi Jon,
That is interesting. I must admit I haven't played much with the macro recorder in 2010, so I must check it out.
I have an issue with Macro recorder of a Pivot Table. After run the macro recorder and save the macro and the project, I tried running the macro to populate the pivot table. There were quite of a few rows missing from the result. Has someone running into the same issue before? Could someone help me troubleshoot this? Thanks in advance.
Here are the macro code:
Sub Download() ' ' Download Macro ' ' Workbooks.Open Filename:= _ "ARRA-projects.xls" ActiveWindow.Visible = False Windows("ARRA-projects.xls").Visible = True ActiveWindow.WindowState = xlMaximized Selection.AutoFilter Cells.Select Selection.Copy Windows("NIH Download Macro 092209.xlsm").Activate ActiveSheet.Paste Windows("ARRA-projects.xls").Activate Columns("J:J").Select Range("J2").Activate ActiveWorkbook.Close Range("A2").Select Sheets.Add ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "Sheet1!R2C1:R9851C25", Version:=xlPivotTableVersion10).CreatePivotTable _ TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion _ :=xlPivotTableVersion10 Sheets("Sheet4").Select Cells(3, 1).Select With ActiveSheet.PivotTables("PivotTable1").PivotFields("Organization ") .Orientation = xlRowField .Position = 1 End With ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _ "PivotTable1").PivotFields("FY Total Cost "), "Sum of FY Total Cost ", xlSum ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _ "PivotTable1").PivotFields("FY Total Cost "), "Sum of FY Total Cost 2", xlSum With ActiveSheet.PivotTables("PivotTable1").DataPivotField .Orientation = xlColumnField .Position = 1 End With Range("C5").Select ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of FY Total Cost 2"). _ Function = xlCount Range("A5").Select ActiveSheet.PivotTables("PivotTable1").PivotFields("Organization ").AutoSort _ xlDescending, "Sum of FY Total Cost " Range("D5").Select ActiveCell.FormulaR1C1 = "1" Range("D6").Select ActiveCell.FormulaR1C1 = "=R[-1]C+1" Range("D6").Select Selection.AutoFill Destination:=Range("D6:D775"), Type:=xlFillDefault Range("D6:D775").Select ActiveWindow.LargeScroll Down:=-19 ActiveWindow.ScrollRow = 16 ActiveWindow.ScrollRow = 13 ActiveWindow.ScrollRow = 9 ActiveWindow.ScrollRow = 5 ActiveWindow.ScrollRow = 3 ActiveWindow.ScrollRow = 1 Range("D4").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone Selection.Borders(xlEdgeBottom).LineStyle = xlNone Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone ActiveCell.FormulaR1C1 = "Rank" Range("D4").Select With Selection .HorizontalAlignment = xlRight .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("B:B").Select Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)" Columns("C:C").ColumnWidth = 12.71 Range("A5").Select End Sub Sub ClearNIHData() ' ' ClearNIHData Macro ' ' Sheets("Sheet4").Select ActiveWindow.SelectedSheets.Delete Cells.Select Range("A2").Activate Selection.Clear Range("A1").Select End SubI removed the link to your file, anyone can click on it and download it.
I can't see why rows are missing from your data, but the source of the pivot cache is hardcoded. If rows are added to the source file, and you re-run the macro to recreate the pivot tables, they won't pick up the added rows.