|
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.
| 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.
|
| Conclusion |
|
|
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:
- Right click on the saved macro and choose Save
As from the pop-up box
- Give the converted macro a name
- 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.
|
|
