There’s an interesting discussion at Smurf about the macro recorder. I rarely use it, but I used to use it quite often. I actually just used it last week for something to do with FormatConditions, but I don’t remember the details. Intellisense or the Object Browser usually gets me where I want to go.
The number one reason people say they use the recorder is get object names and syntax. It’s great for that, no doubt. But is that the best delivery mechanism for syntax? I would think good documentation would be more useful and efficient than the macro recorder.
There is one aspect to the macro recorder that you’ll never get from the best documentation: A direct mapping from the UI to code. If you don’t know about the FormatConditions object, then you may not have any luck determining how to manipulate conditional formatting in code. If you know how to do it in the UI, however, the recorder will at least give you a clue.
If VBA had perfect documentation (complete, good/relevant examples, context) would that change how much you use the macro recorder?
But most likely we’ll get poor documentation and either poor or no macro recorder.
Both in Firefox v2 and in IE7 I get:
You did not select an answer. If you wish to vote in this poll, please select an answer and try again.
Probably more have this problem, because viewing the answers just gives all zeros
Niek Otten
“If VBA had perfect documentation” = “If pigs could fly”.
I use the object browser and intellisense extensively, both of which are of decent quality, and the macro recorder still helps a great deal. What VBA help needs is good, relevant examples.
Even if they had perfect documentation, the MR is still easier to get object names. There’s still a bit of research with docs if you don’t know which object to use for a particular task.
That poll doesn’t work for me either. Same problem as Niek.
My answer is #3. Even with perfect documentation, recording a quick macro would be a faster way to identify the objects, properties, and methods.
I love the macro recorder, especially for formatting sheets by code.
Poll didnt work, but I would probably use the same, depending on new features.
I use it sometimes to get me started on coding up an object where I don’t remember the details of the properties and methods. More times than not though when I record a macro it is because I can not remember what index number equals what colour. At some point I should just make myself a chart of the colours and their index numbers.
As for my answer. I would pick 3. Even if they had the documentation based on my ability to sift through the existing documentation I could just record a macro in one tenth the time it would take to look it up.
Poll should be fixed now. I’m never using pollhost again. What’s that one that you use J-Walk?
I voted 3, but I’m just a schlocky end user programmer who has read up and learned enough to get the things I need done for the business needs. Even so, I almost always go in and clean up the messy MR code and try to make it more efficient. I use the documentation seldom because it’s pretty unhelpful to me. A search on the Excel VBA newsgroup almost always gets me an answer more quickly. I’m self taught and undedicated to the programming task, and it always surprises me when I can’t shorten some of the statements but have to stick with the .Select that the MR recorded.
Perfect Documentation ?
Would that be for the Programmer, who mostly knows all the syntax and structures or for the novice guy having a play and wanting to learn ?
The macro recorder is great for the later
ps: Wouldn’t Perfect Documentation put all you Excel book writers out of a job !
Yes, for the programmer. That’s who I’m hearing say how important the MR is. I agree it is invaluable for the novice – at least is was for me.
Follow up question: What languages do you know other than VBA? Do those have some sort of recorder?
Of course another way to reduce the usage of the macro recorder would be to reduce its functionality, so it didn’t record anything done to graphs or shape objects for instance.
If you wanted to be really tricky you could also change the way some of the shape adjustments work, so you couldn’t even record it in a previous version.
Goodness knows why anyone would want to do that, but it seems like someone at Microsoft thought it would be a good idea.
As for other languages, there’s Fortan which doesn’t have a macro recorder (not sure how that would work), and the various incarnations of the Lotus 123 macro/script language, which did have an OK macro recorder in later versions.
I’ve also played with oo Basic, which was sufficiently horrible to keep me forking out the dough to Microsoft.
I agree with Doug. The MR records to much things you have to delete later on because it only adds useless text to your code. E.g. a lot of select statements if you are searching for the correct way to do something.
In some cases there is also a shorter way to write things down. E.g. [Cell] instead of Range(“Cell”). Of course it is a bit harder to read for a novice this way.
When I started VBA it was very useful. Now I only use it when I don’t know the details of the properties and methods, like Jim.
Reminds me of the utter disdain I have for Wrox books that include a reference to an application’s object model. In the Excel VBA Programmer’s Reference, for example, almost half the book is taken up with a less than useful object model reference. It’s pretty impossible to do a decent job of this in the first place, since you’d need a book the size of the full Oxford English Dictionary (and probably larger) and several authors to fully document every object with examples. The authors have nonetheless managed to do a great job with the rest of the book, in the “limited” space provided.
Jim – “More times than not though when I record a macro it is because I can not remember what index number equals what colour.”
Ha ha, me too. This is probably my most frequent use of the recorder.
Doug – I don’t think the reduced functionality of the MR or reducing MR usage were objectives in the changes to the MR in 2007. The whole shape “experience” was changed in 2007, and I think the coders were too pressed for time to work on something extraneous like the recorder.
Harry – You selected these things along the way to editing them, right? The macro recorder recorded all of your actions. You visited the dialog, right? The macro recorder recorded all the settings in the dialog.
Oh, and don’t get into the habit of using [Cell] in place of Worksheets(“Some Sheet”).Range(“Cell”).Value, because some day you’re going to try to expand the code to cover something other than the active sheet, and it will take hours to figure out why the results are incorrect. Shortcuts are okay in the Immediate Window, but a bad habit in module code.
Jon, you are right. I already experienced the problem you mention about [Cell]. At the moment most of things I do are indeed covering only one (active) sheet and easy tasks.
Compared to many people on this site, I am just a beginner. Reading this stuff to learn form it. Hope to find time to try more complex projects. So thanks for the advice.
Doug, I’ve had the exact opposite experience with using macros recorded in old Excel in XL07. In my case, it was formatting a pivot table. I recorded the line in XL03 and it works just fine in XL07.
The thing is, there is no way to manually create such a pivot table in XL07 as the whole autoformatting process was revamped. If you MR an autoformat in XL07 it’ll record something totally different from the above. Yet the above still works, I assume for backwards compatibility purposes. I wonder why backwards compatibility was deemed important for some functions and not for others.
I know C#, Visual Studio has a macro recorder, never used it. It records your actions within VS.
Its like snorkeling(RM) versus scuba diving. It gives you a sense of the water and conditions but doesn’t allow you to stay under too long….
I’ve always found it quicker (and easier) to just record what I want to do and then clean it up afterwards.
Funny but I never use it to find color codes/indexes. I just google “mcritchie color” and it’s always #1 on the list.
I mostly use it for things like the Find method and I find :-) that recording a find is often quicker than doing the typing.
I use it when I need to produce a macro that uses a bit of Excel functionality I’ve not put in code before. Or when I can be bothered reading my own documentation or finding a suitable example file in my collection.
I did the colour chart thing very early on in my macro/VBA education as a way to practise loops and cell references. Excel + macros recorder + VBA are an excellent route into other stuff. I’m a scripter not a programmer but VBA allows to to believe otherwise sometimes!
The Macro recorder has always been my friend when I have been too lazy or tired or needed a quick fix code. Not that I don’t use the intellisence or object browser or help. I always recommend it’s use for beginners couple with slelcting key words and pressing F1
I used to use the MR quite a bit, for little snippets of code, objects, properties, etc. I don’t use it much anymore. Mostly I tend to look for online help, the OB or just intellisense with trial/error. I can usually find what I want a lot faster on my own than recording a macro and wading through the code created. Sometimes it is nice though. I think I’ve used the MR once in the last 3 months, and that was for conditional formatting (it still gets me, lol). I’ll go to Chip Pearson’s site first before I record a macro though (generally), it’s all there anyway. LOL!
In the spirit of answering the question, if there was a near-perfect documentation of Excel help – which there never will be – I would not use the MR personally. I prefer help. I like good documentation, examples, related topics/items, a quick search capability and good menus. But since that will never happen, as long as we have a macro recorder for VBA, I’ll still use it occasionally. :)
Zach – yes, I was exaggerating a little for the sake of a cheap shot at Microsoft :). Recording a macro in an earlier version is still the best way I know of to discover how to adjust shapes through VBA. It’s annoying that you then have to adjust some things to work properly in 2007, but there aren’t all that many changes.
I think that the macro recorder becomes even more important when switching over to .NET because the new .NET syntax will confuse even the best VBA programmers initially. So, even an expert VBA programmer would find a good .NET macro recorder to be a superb tool, especially at first.
Ideally, a .NET macro recorder would have to allow for recording in VB.NET or C# (or certainly in VB.NET, at least), and preferably would allow for choosing to record either with ‘Option Strict On’ or with ‘Option Strict Off’. (Or perhaps simply detecting the current ‘Option Strict’ setting within the document into which the recording is being made.)
I know that this is a high standard, but, ideally, the macro recorder would also record with better syntax than it does today. Improved output is even more necessary with .NET, which is strong typed when using VB.NET with ‘Option Strict On’ or when using C#. In VBA the current macro recorder will typically create output that looks like the following:
Selection.Copy
Range(“B1:B10”).Select
ActiveSheet.Paste
The same commands would look pretty much the same when called using VB.NET with ‘Option Strict Off’:
Application.Selection.Copy
Application.Range(“B1:B10”).Select
Application.ActiveSheet.Paste
However, I think I would prefer to see code such as:
range1 = Application.Range(“A1:A10”)
range1.Select
range1.Copy
Dim range2 As Excel.Range
range2 = Application.Range(“B1:B10”)
range2.Select
range2.PasteSpecial(xlPasteAll)
I know that this is longer, but it is much more explicit, and a lot closer to what coders should be using for their own code. This could be vastly harder to create, however, than simply scripting selections and actions, I don’t know. It does look a lot harder. But I think that it could really be worth it.
I could also envision a setting for “Ignore Selections” in which case commands such as .Select are simply ignored. Typically one would not want to use .Select in their code no more than one would want to use SendKeys(). There is a place for it, but it is rare. With the option “Ignore Selections” set to True, I the recorded results could look as follows:
range1 = Application.Range(“A1:A10”)
range1.Copy
Dim range2 As Excel.Range
range2 = Application.Range(“B1:B10”)
range2.PasteSpecial(xlPasteAll)
Of course, one could ignore the range variable declarations and boil it all down to the following:
Application.Range(“B1:B10”).PasteSpecial(xlPasteAll)
And this would be fine, but I think there is an advantage, especially for the learning coder, to see a variable explicitly declared, set, and then used. Certainly if a given object is to be used more than once, then I think that it should be created once, assigned to a variable, and then that variable used multiple times.
The macro recorder gets really interesting with .NET — and more important — if one could record with ‘Option Strict On’, in which case CType() declarations would be required, and hopefully created correctly by the macro recorder as needed.
A C# macro recorder would be even more complicated — and quite possibly not necessarily worth the effort in the first version since the majority of the audience that would require a macro recorder would be VBA programmers transitioning over to .NET. That said, C# coders could use it for two important reasons: (1) the syntax for calling the Microsoft Office object model from C# is not that intuitive, even for a C# coder, and (2) a C# coder is less likely to be proficient with the object model in the first place than would be a VBA coder.
If one has limited resources and cannot roll out all of these capabilities, at least not in the first version, then deciding which aspects have the best “bang for the buck”, could make for some difficult decisions. But I think as we transition towards .NET, the macro recorder will become vastly more important again. It will verge on critical for any coder in the initial months of switching over from VBA, or any coder first learning to program on the Microsoft Office platform when using .NET.
A near-perfect documentation of the Excel OM would still in some cases be slower than the macro recorder. If you know the object you want and have an idea of the methods or properties, the documentation would be sufficient. If you are doing something unfamiliar, however, nothing beats a quick recorded macro.
Where I’ve seen problems is when someone spends ten minutes recording a 700-line macro. That’s when it’s easy to become swamped by the extraneous lines of code.
Said it before, I’ll say it again. Object oriented languages are over-kill as an application scripting language.
You have a rough idea what you want to do.
You record a macro in 2 secs get a glimpse of the awful code and it jogs your memory enough to piece together the syntax for a rough Google query. It returns a load of rubbish with pages requesting your loyal registration and pop ups, but jogs your memory enough to just get on with it. Half way through your choosen New-Wheel invention you remember where you last wrote this code and go hunting for the file and tediously fail to find it. Shortly after finishing your new wheel shaped masterpiece you click F12 and go searching for the folder that best suits this new magnum opus only to stumble upon last years doppleganger which has a few clever extra bits that you considered doing but ran out of time.
Long live VBA long live the macro recorder.
Jan – Been there, done that.
Hello
Happy to find your blog.
Sorry! my question is not related to macros. if you will make us so happy if you find a solution for it.
well, We have an Excel file with about 20 tabsheets, each contains numbers, texts, pictures and some formulas to each other and another excel file. It was always about 2-7 M-bite but after we lost the file one time and we used an archived history file (saved by protection setting automatically by Excel) it is now 150 M-bite! about 10 times bigger! and very slow.
I tried to move the sheets to a new book but didn’t work and it was still about 140 M-bite. then I tried by copy and use paste special such as “formula and numbers formatting”+”column width”+ “formatting”. but the formulas referred to previous file not the new file. (I hope my sentence be clear, as English is not my native language. I tried to save as the file as a new file but it just helped to get ride of 30 M-bite. and it is still 129 M-bite.
The file is shared with keeping history for 10 days.
Thank you