Converting a Macro to VBA Code

Now that you have discovered all the limitations of macros, you might be thinking about all the macros you've already written that you wish you had developed by using VBA code. Or, after seeing how easy it is to do certain tasks by using macros, you might be disappointed to learn how limited macros are. Fortunately, with Access 2003 it's easy to convert an Access macro to VBA code, and after you have converted the macro to VBA code, you can modify the code just as you would any VBA module. Follow these steps to convert an Access macro to VBA code:

  1. Open the macro you want to convert in Design view.

  2. Choose File | Save As. The Save dialog box appears.

  3. Select Module from the As drop-down (see Figure 13.19).

    Figure 13.19. Saving a macro as a VBA module.


  4. Click OK. The Convert Macro dialog box, shown in Figure 13.20, appears.

    Figure 13.20. Using the Convert Macro dialog box to indicate whether Access will add error handling and comments to the VBA module.


  5. Indicate whether you want to add error handling and comments to the generated code and then click Convert.

  6. When you get an indication that the conversion is finished, click OK.

  7. The converted macro appears under the list of modules, with "Converted Macro:" followed by the name of the macro. Click Design to view the results of the conversion.

Figure 13.21 shows a macro that Access has converted into distinct subroutines?one for each macro name. The macro is complete, with logic, comments, and error handling. Access converts all macro conditions into If...Else...End If statements and all the macro comments into VBA comments. It adds basic error-handling routines to the code.

Figure 13.21. A converted macro as a module.



When you convert a macro to a VBA module, the original macro remains untouched. Furthermore, all the objects in the application will still call the macro. To effectively use the macro conversion options, you must find all the places where the application calls the macro and replace the macro references with calls to the VBA function.

    Part III: Creating Your Own Database and Objects
    Part V: Advanced Topics