Part IV: Professional Database Development
1078
Note
The VBA language supplies a DoCmd object that accomplishes many macro actions. Under the surface, DoCmd
runs a macro task to accomplish the same result provided by a macro action. You could, for example, specify
DoCmd.Close to run the Close macro action and close the currently active form.
Choosing between macros and VBA
Although macros sometimes prove to be the solution of choice, VBA is the tool of choice at other
times. You’ll probably want to use VBA rather than macros when you want tol (^) Create and use your own functions. In addition to using the built-in functions in
Access, you can create and work with your own functions by using VBA code.
l (^) Use Automation to communicate with other Windows applications or to run system-
level actions. You can write code to see whether a file exists before you take some action,
or you can communicate with another Windows application (such as a spreadsheet), pass-
ing data back and forth.
l (^) Use existing functions in external Windows Dynamic Link Libraries (DLLs). Macros
don’t enable you to call functions in other Windows DLLs.
l (^) Work with records one at a time. If you need to step through records or move values
from a record to variables for manipulation, code is the answer.
l (^) Create or manipulate objects. In most cases, you’ll find that creating and modifying an
object is easiest in that object’s Design view. In some situations, however, you may want
to manipulate the definition of an object in code. With a few VBA statements, you can
manipulate virtually any and all objects in a database, including the database itself.
l (^) Display a progress meter on the status bar. If you need to display a progress meter to
communicate progress to the user, VBA code is the answer.
l (^) Macros are required by Access Web databases. Access 2010 supports a new type of
database that is used only in SharePoint 2010. A “Web-enabled” database requires the use
of macros instead of VBA for all of its forms and reports.
Converting existing macros to VBA
After you become comfortable with writing VBA code, you may want to rewrite some of your
application macros as VBA procedures. As you begin this process, you quickly realize how men-
tally challenging the effort can be as you review every macro in your various macro libraries. You
can’t merely cut the macro from the macro window and paste it into a module window. For each
condition, action, and action argument for a macro, you must analyze the task it accomplishes and
then write the equivalent statements of VBA code in your procedure.
Fortunately, Access provides a feature that converts macros to VBA code automatically. One of the
options in the Save As dialog box is Save As Module. You can use this option when a macro file is
highlighted in the Macros object window of the Database window. This option enables you to con-
vert an entire macro group to a module in seconds.