Macro recorder

A few people have suggested a working macro recorder would be their top priority for a VBA update.

Personally I think the MR is important, but my top priority would be a decent IDE like VSTA or VSTO.

which would you prefer in a future version of Office

  1. VSTA with, C# etc fully integrated, but poor (but improving) Macro recorder support, or
  2. Just the current VBAIDE but with better macro support?
  3. Some other realistic (constrained) combination?

(I am assuming that the VSTA editor would be in addition to the current VBAIDE as in infopath.)

Just wondering as I would opt for 1 every time. the MR is fantastically useful, especially around charts and shapes etc. But I think its ok for the time being as it is, and a better IDE would be more valuable in the immediate term. Next job after a decent IDE – macro recorder for sure, but in my view not before. What do you think?




24 Responses to “Macro recorder”

  1. Harlan Grove Says:

    A macro recorder shouldn’t be difficult. If (and it may be a big if) each key and menu/ribbon command were tied to an OM method, it’d involve nothing more than looking up key codes or menu codes in a table and returning the corresponding method as text, adding default arguments, then overriding arguments whenever the user changes anything in dialogs.

  2. alastair Says:

    a better macro recorder would make VBA more accessible to the masses, whereas a better IDE would make life better for the few. Before new IDE I would love to see an improvement to some of the somewhat arcane syntax.

  3. Marcus Says:

    I’d also opt for full VB.Net/C# integration. If MS wanted to retire VBA, surely this would be one way to accelerate the process?

    The MR is most suitable for what it is: a good learning tool and a quick way to look-up arcance syntax. It also, unfortunately promotes some poor habits in some code I’m sure we’ve all seen.

    P.S. C# et al integration assumes it comes with a .Net IDE.

  4. Dick Kusleika Says:

    I almost never use the MR anymore. For me, deployment is number one. If .net isn’t a part of the workbook, then it’s no good to me.

    Isn’t .net super-strongly typed? That will make a MR harder to write, I would think. No more code like:


  5. Biggus Dickus Says:

    “For me, deployment is number one. If .net isn’t a part of the workbook, then it’s no good to me.”

    Yep Dick – I have been on the VSTO SDR since VSTO was just on paper and I have consistently said “Deployment, deploymen, deployment” I will accept it when I can simply email an Excel file to someone who has Excel installed and run the macros included in the file. They have been frustrated with me (really?) but I still say it.

    So if VBA is to be continued that’d be fine with me – not because VBA is the greatest thing since sex or simply because that’s what I know.

    But the problem now is “What is going to be done to promote VBA as a legitimate solution provider (remember that phrase) now that VBA has gone through years of being constantly dismissed and “dissed” by major parts of MS and IT departments and REAL developers since the last century….?”


  6. Johan Nordberg Says:

    I would love to have VSTA in Office. To me, that’s the perfect combination of the best of VBA simplicity and .net power. Just like InfoPath 2007 has.

  7. jonpeltier Says:

    The VSTA/better IDE that Simon is in favor of sounds nice to me.

    However, I and many developers use the macro recorder a great deal as an adjunct to the object browser and help files, when trying to determine what syntax is needed for particular tasks. When we were given new shapes in Office 12 (or rather, they were thrust upon us), the new shapes were invisible to the macro recorder, while the help entries on the shapes were pathetic, and the object browser inscrutible. No recorder, no ability to modify shapes.

    In addition, the macro recorder is an important way for the very lowest level end user developers get started, and a broken recorder just raises their barrier to entry.

    To me, therefore, it’s more important to get the macro recorder up to standard.

  8. Simon Says:

    Both the VBAIDE and the MR have been woefully neglected, I think we all agree on that.

    My thinking is that the MR is working good enough for a reasonable intro for beginning devs. And a proper IDE would benefit all, although I accept the more experienced probably have more to gain. At least those working on projects outside their comfort zone in size or complexity would benefit most.( I suspect thats anyone who ever has to look at someones elses code though.)

    I’m surprised the MR is so popular, I only use it for those areas of the object model that are badly documented or inconsistent/confusing (charting and shapes mainly). I guess I don’t do much work in those areas. And of course the recorder could be better in these areas too (even in classic Excel)

    Agreed (completely) on deployment

  9. Doug Glancy Says:

    An improved IDE on a par with .Net would also help beginners with things like automatic indenting, more thorough but less obnoxious syntax checking, and much better IntelliSense. Especially the latter – being able to quickly see the descriptions of properties, methods, etc., in .Net is very helpful in learning both the possibilities and syntax.

    I use the MR to suss out syntax. It sounds like it needs to be improved at least to the point where it covers new objects, solving the type of problem Jon describes with 07 shapes.

    I have to say I feel a bit of a sap even participating in this discussion, but hey, dare to dream!

  10. jonpeltier Says:

    To tell the truth, if we had this discussion before I had first used the Excel 2007 recorder, I would have thought there was nothing wrong with it, and let’s concentrate on the IDE. The 2003 recorder was okay, aside from sometimes spitting out strange code. As Simon said, it’s useful for certain regions of the OM, like charts and shapes. Hmmm, that’s just where the 2007 MR breaks down.

  11. Simon Says:

    Funny that, charts and shapes being a shared component and being badly covered by MR, bad and getting worse. I would have thought getting the MR to work with them would be a double win as the code could be used by Word MR too (does Word/ppt have an MR?)

  12. Charles Says:

    The MR needs to be fixed.
    A better VBA IDE would be nice to have, and if VSTA gets integrated then presumably a better IDE would come with it.

  13. Nick Hebb Says:

    I echo Jon’s comments about using the MR with XL2007 AutoShapes. The help file is so bad that I programmed by trial and error. Ironically, once I figured out how they behaved, the programming is much easier then previous versions.

    Since the Office drawing tools were rewritten from the ground up, my guess is that they just ran out of time to add MR support prior to release.

    I think the importance of the MR has more to do with Microsoft showing commitment to desktop development. Between the Yahoo fiasco (aka, chasing Google), Cloud computing and the focus on Sharepoint Services, the abandonment of VB6, and the lack of a linker for .NET, it’s really hard not to think that Microsoft is abandoning the desktop – or at least seriously neglecting it.

  14. jonpeltier Says:

    Simon: Word has a macro recorder. PowerPoint used to, but since PPT is 98% shapes, and the MR no longer does shapes, they just took it out altogether.

    Nick: I agree that they must have run out of resources before they got to the MR (and to Help for that matter). If you have any insights into the OM for shapes, I and thousands of avid readers would like you to share them. In fact, if you’re up to a guest blog post, I’d be glad to host it for you.

    I also agree that MS has lost its way with all the distractions: search, dot net, new UI, computing in the fog (or is that cloud?), and so forth.

  15. Simon Says:

    ‘computing in the fog’
    brilliant description Jon

  16. Johan Nordberg Says:

    I wonder if the MR really is that helpful for beginners. Let’s say you’d like to see the code to change paper orientation to Landscape. Every tried to do that in the MR? Well. You’ll get about 30 lines of code I think, but in reality you just need one.

    Or if you need some code to put a value from one sheet into another sheet. In the UI you’ll probably selected the cell, copy it, switch sheet, select another cell and paste. The MR will record exactly that, but if you write the same code you’d use the range object and get better code in one line without the need for ScreenUpdating = False. But then again, if you just use the MR, then you’ll never know about how to avoid the fickering…

    But yes. I use the MR from time to time, just to see how Office does it. But most of the time you need to refactor that code anyway.

  17. Marcus Says:

    Spot on Johan. That’s what I was alluding to in reference to the MR promoting poor coding habits such as spreadsheets which flicker incessantly from ranges being selected during copy and paste operations.

  18. Patrick O'Beirne Says:

    ” jonpeltier Says: PowerPoint used to, but since PPT is 98% shapes, and the MR no longer does shapes, they just took it out altogether.”

    I rarely program PPT but when I do the OM is just so rambling that I use PP2002 to record the macro, then strip it down to what I actually need, then move that to PP2007.

  19. Harlan Grove Says:

    A much better resource than either the macro recorder of the so-called documentation is the microsoft.public.excel.programming newsgroup as well as the other Excel newsgroups. Much more real world examples.

    As for the macro recorder’s poor code, granted, but usually it’s the quickest, most efficient way to find out which object one needs to manipulate to perform a particular task.

  20. Simon Says:

    Patrick – your monster is superb, the arms, the ears, the eyes, the feet, brilliant.

  21. jonpeltier Says:

    Patrick: “I rarely program PPTbut when I do the OM is just so rambling …”

    Ever program in Word? The OM was designed by psychotics on crack.

  22. Doug Glancy Says:

    Harlan (or anybody), What has happened to Google Groups anyway? It used to be my favorite resource, as you described, but now when I bring it up there are a few unique discussions, and then they just repeat. Used to be able to bring up half a dozen helpful threads, but it”s gotten so bad I google the web first and then Groups as a second resort.

  23. jonpeltier Says:

    Google Groups used to be very good, back when they inherited the archives from deja-news. Over time, it has lost the ability to separate threads with the same subject, it sometimes loses the start of a thread, and its search capabilities have actually gotten worse. I used to be able to enter my name, *microsoft* as the group, and a few VBA keywords, and I could find a procedure I’d posted years before; lately I can’t seem to find anything. This may be the only thing the Google has screwed up.

  24. R-Chan Says:

    Give me a new VBA IDE, the thing stinks.

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: