The Access Wizard Newsletter Tips Tricks and Traps for Access Users and Developers
December 2006

Macros – Training Wheels for VBA Programmers

When I was a young boy (many, many moons ago), I was really happy to graduate from a tricycle to a bicycle. It meant that I was growing up and able to do more things. My first bike, however, required training wheels. Like most little kids I couldn't master the art of balancing very well-it took time and practice. Training wheels on the bike helped me to slowly gain the skills necessary to ride a bike without falling off.

Just as training wheels are a good start to riding a bike, macros in a program like Access, can be an aid in learning to code. But just as you wouldn't expect to be using training wheels on your bike after a year, you shouldn’t use macros long term. Macros are intended to be an aid to get you started, and nothing more. This month we'll take a look at macros – focusing on the good stuff and working towards eventually weaning you off macros and into true VBA coding.

In this issue
  • Tip of the Month – Use Macros to Generate VBA Code
  • Macros – The Good
  • The Bad
  • And The Ugly
  • Conclusion

  • Macros – The Good

    Macros have many similarities to training wheels. They can get you started on the road to automating complex processes; they're easy to generate and easy to implement; and they may instill confidence. To create a macro, click on the Macro tab in the database window. Click on the New button and then poke around and experiment. I'm not going to take the time to walk you through the use of macros because although they have a place in Access database programming, it's very limited. What they're good at is automating straight-forward processes with no variability for situations where there's minimal chance for error. Once a macro is created, it can be run by clicking on the macro in the database window, run from VBA code, or even called from another macro.

    The neat thing about macros is that they are easy to use and using them requires no knowledge of coding or syntax – None! Zero! Zip! Nada! Not a bad starting point. This combination of easy to use and no syntax is a highly desirable feature.

    The Bad

    There are three major problems with macros, two of which I place in the "bad" column. The first: you can't have conditional logic. What this means is that you don't get to say things like "if A happens, then do B, otherwise do C. You can only use straight line logic (e.g. first do A, then do B, then do C).

    Although there are many things that can be accomplished within a database that are linear (e.g. first do A, then B, then C), frequently your logic has to flexible enough to make decisions based on conditions. This just isn't possible with macros.

    The second bad thing about macros is that there are no opportunities for handling errors. What happens if something doesn’t go as planned? With VBA code you can plan for handling errors so that if something goes wrong you can react -- see the articles from the last couple of months for details on error handling. With macros, if something goes wrong, the program stops and annoys the user with cryptic messages. There is no flexibility to insert more information for graceful error handling.

    And The Ugly

    The truly horrific aspect of macros is that they are almost impossible to debug. There's no way of tracing the program as it runs, and it's difficult to see where the problem may lie since the macro process cannot be viewed in its entirety from the macro generation window.

    Since errors in logic and coding cause the most trouble, this inability to easily debug macros makes them useless for anything other than the simplest tasks.


    Although macros have their place (see this month's tip for a great example), for the most part their limitations far outweigh their positive points. Although you can start with macros to get your feet wet, once you want to do anything that is moderately complex that can't be accomplished with the standard Access tools, you'll want to lose the training wheels and move into VBA coding.

    Tip of the Month – Use Macros to Generate VBA Code

    One of the neatest features of macros is that they can help you generate VBA code. If you've ever written macros in Excel or Word, you know that those programs can turn your keystrokes into code with a couple of clicks of the mouse. Unfortunately, there's no identical functionality in Access. There is, however, a little known feature of macros that can do wonders to help you with coding and learning syntax.

    If you can write a macro to do something, you can easily turn that macro into VBA code. Once the macro is complete and saved take the following steps:

    1. Right click on the saved macro and choose Save As from the pop-up box
    2. Give the converted macro a name
    3. Save the macro as a Module in the bottom part of the pop-up, then hit OK

    At this point Access will pop you into the VBA coding window with the macro converted into VBA code. You can look at the generated syntax and incorporate it into your program from there.

    Enjoy this quick and easy way to learn VBA syntax.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Forward email

    This email was sent to jim@custom-software.biz, by jim@custom-software.biz

    Custom Software | © Copyright Custom Software All Rights Reserved | Westford | MA | 01886