Manufacturing Information Solutions Forum Index Manufacturing Information Solutions
Your Place for Support and Discussions
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Convert macros to VBA code

 
Post new topic   Reply to topic    Manufacturing Information Solutions Forum Index -> Microsoft Access
View previous topic :: View next topic  
Author Message
mistux
Site Admin


Joined: 25 Jun 2004
Posts: 1042
Location: South Bend, Indiana USA

PostPosted: Thu Sep 18, 2008 12:19 pm    Post subject: Convert macros to VBA code Reply with quote

You can use Office Access 2007 to automatically convert macros to VBA modules or class modules. You can convert macros that are attached to a form or report, whether they exist as separate objects or as embedded macros. You can also convert global macros that are not attached to a specific form or report.

Convert macros that are attached to a form or report

This process converts to VBA any macros that are referred to by (or embedded in) a form or report (or any of its controls) and adds the VBA code to the form or report's class module. The class module becomes part of the form or report and moves with the form or report if it is moved or copied.

1.In the Navigation Pane, right-click the form or report, and then click Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.).

2. On the Database Tools tab, in the Macro group, click either Convert Form's Macros To Visual Basic or Convert Report's Macros To Visual Basic.

3. In the Convert form macros or Convert report macros dialog box, select whether you want Access to add error handling code to the functions it generates. Also, if you have any comments in your macros, select whether you want to have them included as comments in the functions. Click Convert to continue.

If no class module exists for the form or report, Access creates one and adds a procedure to the module for each macro that was associated with the form or report. Access also changes the event properties of the form or report so that they run the new VBA procedures instead of the macros.

To view and edit the VBA code:

1. While the form or report is still open in Design view, if the property sheet is not already displayed, press F4 to display it.

2. On the Event tab of the property sheet, click in any property box that displays [Event Procedure], and then click . To view the event properties for a specific control, click the control to select it. To view the event properties for the entire form or report, select Form or Report from the drop-down list at the top of the property sheet.

Access opens the Visual Basic Editor and displays the event procedure in its class module. You can scroll up or down to view any other procedures that are in the same class module.

Convert global macros

1. In the Navigation Pane, click the name of the macro that you want to convert.

2. On the Database Tools tab, in the Macro group, click Convert Macros To Visual Basic.

3. In the Convert Macro dialog box, select the options that you want, and then click Convert.

Access converts the macro and opens the Visual Basic Editor.

To view and edit the VBA code:

1. In the Visual Basic Editor, if the Project Explorer pane is not displayed, on the View menu, click Project Explorer.

2. Expand the tree under the name of the database in which you are working.

3. Under Modules, double-click the module Converted Macro- macro name.

The Visual Basic Editor opens the module.

Attach a VBA function to an event property

When you convert a global macro to VBA, the VBA code is placed in a standard module. Unlike a class module, a standard module is not part of a form or report. You will most likely want to associate the function with an event property on a form, report, or control so that the code runs precisely when and where you want. To do this, either you can copy the VBA code into a class module and then associate it with an event property, or you can make a special call from the event property to the standard module by using the following procedure.

1. In the Visual Basic Editor, make a note of the function name. For example, if you converted a macro named MyMacro, the function name will be MyMacro().

2. Close the Visual Basic Editor.

3. In the Navigation Pane, right-click the form or report with which you want to associate the function, and then click Design View.

4. Click the control or section with which you want to associate the function.

5. If the property sheet is not already displayed, press F4 to display it.

6. On the Event tab of the property sheet, click the event property box with which you want to associate the function.

7. In the property box, type an equal sign (=) followed by the name of the function — for example, =MyMacro(). Be sure to include the parentheses.

8. Save the form or report by clicking Save on the Quick Access Toolbar.

9. In the Navigation Pane, double-click the form or report, and test it to see that the code runs as it should.

You now know the basic steps to add VBA code to your database. This article provides just the basics of how to get started; there are many excellent reference books and online resources that can help you build your programming skills.
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic    Manufacturing Information Solutions Forum Index -> Microsoft Access All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group