Replacing Macros with VBA Code

3 4

By now, you might have the impression that macros aren't all that useful in Access 2002. The reasons macros seem to be less useful have more to do with the greatly increased power of VBA code than with any deficiencies in macros. Macros have remained as useful as they were in Access 1, but because VBA code has become so much more powerful, macros now appear quite limited in comparison. If you've been using macros since the early days of Access, your old macros will still work-you won't need to convert them to code. But if you're just starting out with Access 2002, there's little reason to use macros to automate a database, much as there isn't much (if any) reason to learn MS-DOS commands if you're working in Microsoft Windows 2000 or Windows XP with Microsoft Office XP.

Comparing Macros with VBA Code

These are some of the specific areas in which VBA code has more functionality than macros:

• Event procedures are part of forms and reports, not separate objects, and so when you import a form or a report into another database, its event procedures are imported with it. Macros are separate objects in the database and must be imported separately.
• VBA provides flexibility in error handling. Macros don't support error handling at all.
• In VBA, you can specify arguments when calling a procedure. Macro arguments are fixed and can't be changed when the macro is run.
• You can't work with Data Access Objects (DAO) recordsets or ActiveX Data Objects (ADO) recordsets in macros, whereas VBA code can handle both.
• Macros don't support complex logical structures such as Select Case statements or nested If...Then constructs.
• VBA code can be used to work with Word documents, Excel worksheets, and other objects, using Automation code. Macros have no corresponding functionality.
• You can't use macros in Access add-ins.

In addition to these macro limitations, the ease-of-use improvements brought about by the introduction of code behind forms in Access 2.0 and the new methods of creating menus and toolbars using the CommandBars collection (introduced in Access 97 and discussed in Chapter 13, "Customizing Access Toolbars and Menus [Command Bars]") make macros less useful and VBA code easier to write and more powerful.

The actions used in writing macros generally correspond to methods used in VBA code, although there are some exceptions. Table 14-3 matches up macro actions with the corresponding VBA methods. (Macro actions that are no longer useful but that have been retained for backward compatibility are noted.) If you want to write VBA code to replace your old macros, this table will help you find the right VBA method to use in your code. The next section offers more detail.

In general, macro actions correspond to methods of the DoCmd object, a component of the Access object model. The DoCmd object doesn't correspond directly to any element of the Access interface; it's simply a way to run a miscellaneous assortment of methods. As Access has evolved from version 1 to version 2002, more and more methods previously run from the DoCmd object have changed into methods of objects such as forms and controls. For example, the old GoToControl macro action/DoCmd object method has been replaced by the SetFocus method of the Control object.

Table 14-2. Macro actions and DoCmd methods

Macro action	DoCmd method	Description	Comments
AddMenu	AddMenu	Creates a menu	Although AddMenu is still listed as a DoCmd method in Access 2002, since Access 97, users have been able to create command bars (menu bars and toolbars) by using the Customize dialog box in the interface or by using the CommandBars collection in the Office object model.
ApplyFilter	ApplyFilter	Applies a filter to a form or report
Beep	Beep	Sounds a beep
CancelEvent	CancelEvent	Cancels an event
Close	Close	Closes an object, usually a form or report
CopyDatabaseFile	CopyDatabaseFile	Copies a database file
CopyObject	CopyObject	Copies a database object, such as a form or report
DeleteObject	DeleteObject	Deletes a database object, such as a form or report
	DoMenuItem	Runs a menu item	Retained for compatibility with earlier versions of Access. There has not been a DoMenuItem macro action since Access 95. The RunCommand method (with one of its numerous constants) supersedes the functionality of the older DoMenuItem method.
Echo	Echo	Makes screen updates visible if set to Yes or invisible if set to No.	Retained for compatibility with earlier versions of Access. The Echo method of the Application object is the preferred method.
FindNext	FindNext	Finds the next record that meets the criteria specified by the previous FindRecord action
FindRecord	FindRecord	Finds the first record that matches criteria specified in its arguments
GoToControl	GoToControl	Sets the focus on the specified control	Retained for compatibility with earlier versions of Access. Use the SetFocus method of an object instead.
GoToPage	GoToPage	Goes to a specific page of a form	Although still valid, this method is rarely used, as multipage forms have generally been replaced with multipage tab controls.
GoToRecord	GoToRecord	Goes to a specific record on a form	Still valid for moving to specific records on forms (first, last, next, previous), but in VBA code, GoToRecord has been generally replaced by the Find and Seek methods of DAO recordsets or the Find and Seek methods of ADO recordsets.
Hourglass	Hourglass	Toggles the mouse pointer between an hourglass and the regular shapes
Maximize	Maximize	Sizes the active window to fill the entire Access window
Minimize	Minimize	Reduces the active window to a small title bar at the bottom of the main Access window
MoveSize	MoveSize	Changes the position and/or size of the active window	Retained for compatibility with earlier versions of Access. Use the new Move method of a form or report instead.
MsgBox		Displays a message box	Has no corresponding DoCmd method. Use the MsgBox function instead.
OpenDataAccessPage	OpenDataAccessPage	Opens a data access page
OpenDiagram	OpenDiagram	Opens a database diagram in an Access project
OpenForm	OpenForm	Opens a form
OpenFunction	OpenFunction	Opens a user-defined function in a SQL Server database for viewing in Access
OpenModule	OpenModule	Opens a module in Design view
OpenQuery	OpenQuery	Opens a select query, or runs an action query
OpenReport	OpenReport	Prints a report, or opens it in print preview
OpenStoredProcedure	OpenStoredProcedure	Opens a stored procedure in an Access project
OpenTable	OpenTable	Opens a table
OpenView	OpenView	Opens a view in an Access project
OutputTo	OutputTo	Outputs data in an Access object to one of a number of formats
PrintOut	PrintOut	Prints the active database object	Both the action and the method allow you to specify a page range and the number of copies to print.
Quit	Quit	Exits Access	Retained for compatibility with earlier versions of Access. Use the Application object's Quit method instead.
Rename	Rename	Renames a database object
RepaintObject	RepaintObject	Completes pending screen updates for a database object
Requery	Requery	Updates data in a database object	Retained for compatibility with earlier versions of Access. Replaced by the Requery method of forms and other objects.
Restore	Restore	Restores a maximized or minimized window to its previous size
RunApp		Runs an application	Has no corresponding DoCmd method. Use the Shell function or the CreateObject function to run other applications.
RunCode		Runs a function	Has no corresponding DoCmd method. Call the function directly in VBA code.
RunCommand	RunCommand	Runs a command
RunMacro	RunMacro	Runs a macro
RunSQL	RunSQL	Runs a SQL statement
Save	Save	Saves a database object
SelectObject	SelectObject	Selects a database object	Because some actions can be performed only on the currently selected object, this action should be run first to ensure that the appropriate object is selected.
SendKeys		Issues one or more keystrokes	Has no corresponding DoCmd method. Use the SendKeys statement instead
SendObject	SendObject	Sends the specified database object as an e-mail message or as an attachment to an e-mail message	Generally, it's preferable to create an Outlook Mail item by using the CreateObject function and then fill it with Access data and send it by using components of the Outlook object model.
SetMenuItem	SetMenuItem	Sets the state of menu items on a menu bar	Retained for compatibility with menus created using the old menu macro method (prior to Access 97).
SetValue		Sets the value of a control	Has no corresponding DoCmd method. You can set the value of a field or control directly in VBA code.
SetWarnings	SetWarnings	Toggles on or off warnings about deleting objects or modifying data with action queries
ShowAllRecords	ShowAllRecords	Shows all records (removes a filter)
ShowToolbar	ShowToolbar	Displays or hides a toolbar
StopAllMacros		Stops all macros	Has no corresponding DoCmd method. This action is not needed in VBA code.
StopMacro		Stops the current macro	Has no corresponding DoCmd method. This action isn't needed in VBA code.
TransferDatabase	TransferDatabase	Exports or imports a database file
TransferSpreadsheet	TransferSpreadsheet	Exports or imports a worksheet file
TransferSQLDatabase	TransferSQLDatabase	Exports or imports a SQL Server database file
TransferText	TransferText	Exports or imports a text file

Converting Macros to VBA Code

You can save a macro and then run the Macro Converter utility to convert it to VBA code, but this utility isn't as helpful as it might be. The Macro Converter simply translates each macro action into the corresponding code (usually a method of the DoCmd object), which often leads to inefficient code using obsolete methods such as GoToControl with the control name when the SetFocus method of the control would be more appropriate.

Even more unfortunate, macros are not converted to the appropriate VBA event procedures, but instead are converted to functions that must in turn be called from event procedures. Additionally, converted macro procedures often use the CodeContextObject property, which is used for determining the object in which the code is executing, and which you'll probably never see outside of a converted macro procedure.

InsideOut

Because of the serious limitations of the Macro Converter, requiring extensive rewriting to turn converted macro code into efficient VBA code, I recommend that you just write VBA code (event procedures and functions) from scratch to replace old macros, using Table 14-3 as a reference to the appropriate VBA methods.

See Chapter 20, "Customizing Your Database Using VBA Code," for more details on writing VBA procedures.

However, if you have an AutoKeys or AutoExec macro, leave them be-these special macros still have uses that can't be duplicated in VBA code.