Microsoft AccessRunning VBA Code from a Microsoft Access Macro

Background on Macros

There are many Microsoft Access users who are comfortable writing macros to automate tasks but not familiar with module code and its Visual Basic for Applications (VBA) programming language. That's okay. Everyone has to start somewhere.

The VBA programming language is used across the Microsoft Office products. In addition to Access, you can use it in Excel spreadsheets, Word documents, PowerPoint presentations and Outlook. VBA is also very similar to language used in Visual Basic 6. In fact, the syntax and module editor (Integrated Development Environment or IDE) is practically identical.

In MS Access, macros are completely different from modules. Macros and modules appear as separate objects in the database container:

Macros and Modules in the Access Database Container

Macros are structured actions that are easily created by choosing options from drop down lists. After selecting an action, it's easy to see the available options and choose appropriately.

There's no need to learn a programming language and it's very easy to automate tasks such as running queries, printing reports, opening table and forms, etc. Putting a macro in design mode shows the individual commands:

Microsoft Access Maco Design

If you name a macro Autoexec, Microsoft Access automatically runs it when your database opens. You can also open Microsoft Access from a command line and specify a macro name after the /x switch to run it when it opens.

For instance, you can run the macro named MyMacro like this:

C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE /x MyMacro

This assumes the 32-bit version of Access 2016 is located in its default location. To locate Access, use Windows Explorer and search for msaccess.exe.

For a complete list of command line switches visit this Microsoft page on Office Command Lines.

Background on VBA Modules and Procedures

VBA lets you create modules with more advanced functionality than macros. You'll need to know a little more about programming to write VBA code as it's not structured like macros since the editor lets you type anything, though Intellisense is available to simplify entering built-in commands. Modules are composed of functions and subs (procedures) which perform the tasks you define. A Function is a procedure that returns a value while a Sub performs its task without returning a value. Procedures can be passed parameters to execute different behavior based on those values.

We have many examples of procedures throughout our web site and our Total Visual SourceBook program is a commercial library with over 3600 procedures. Here are some examples.

Note that Excel doesn't have macros but often refers to VBA functions are macros.

Running a Function from a Macro

Let's create a simple function and call it from a macro.

Create a new module

From the Create ribbon, choose Module:

Create a Microsoft Access Module

Add a Function

Paste this text into your module. This is a function that shows a message with the current time.

Public Function ShowCurrentTime()
  MsgBox "The time is " & Now(), , "Sample VBA Function"
End Function

To run it, just put your cursor on the first line and press F5 to see the message box with the current time.

You can also run it from the Immediate Window by pressing Ctrl G to open the Immediate Window and typing:

? ShowCurrentTime()

In our example, the function doesn't return a value but it still needs to be a Function rather than Sub to be callable from a macro.

To call a function from a macro, it cannot be defined as a Private function. Private procedures are only visible in the module. Without "Private" it is public and visible to other objects including macros. Using Public in our example makes it explicit.

Save the Module

From the menu, select File Save and give it a name:

Save module

By default, it's Module1, but you can rename it.

Run the Function from a Macro

To run this function from a macro, simply add a RunCode action and refer to the function:

RunCode

Make sure to include the () after the function name. Save the macro. When you run the macro, the function is called.


Total Access EmailerRun a Total Access Emailer Function from a Macro

Total Access Emailer is our Microsoft Access Emailing program. The add-in version of Total Access Emailer lets you create and run email blasts interactively.

The Professional Version lets you run the email blasts through VBA. The program's Code Generator lets you easily create functions and set parameters interactively so you can insert it into your projects and invoke it from events such as a button click or other VBA code.

Because it's a function, you can also run it from a macro. This page illustrates the programmatic interface and the Code Generator which creates a function named RunEmailBlast(). If you paste that into a module and change the first line from Private to Public, you can run it from a macro by calling that function name:

Run Total Access Emailer function from macro

Additionally, you can invoke the Total Access Emailer function, TotalAccessEmailer, directly from RunCode and pass its required parameters.

For instance, this would run email blast 23 with the options specified by the other parameters as defined in the code generator page.

TotalAccessEmailer(23, False, "", "", True, True)

By creating a macro to call that function, every time you run the macro, email blast 23 is run. You can add multiple RunCode commands to run a series of email blasts.

Table Design

Query Design

Form Design

Form Tips and Mistakes

Module VBA to Forms and Controls

Form Navigation Caption

Use a RecordsetClone

Synchronize Two Subforms

Multiple OpenArgs Values

Late Bind Tab Subforms

Subform Reference to Control Rather than Field

Tab Page Reference

Shortcut Keys


Combo Box Top Tips

Properties and Validation

Select First Item

Cascading Combo Boxes

Zip, City, State AutoFill

Report Design

Suppress Page Headers and Footers on the First Page of Your Report

Add the NoData Event

Annual Monthly Crosstab Columns

Design Environment

Add Buttons to the Quick Access Toolbar

Collapse the Office Ribbon for more space

VBA Programming

Run VBA Code from a Macro

Basics: Forms and Controls

Use Nz() to Handle Nulls

Avoid Exits in the Body of a Procedure

Shortcut Debugging Keys

Set Module Options

Math Rounding Issues

Rename a File or Folder

Avoid DoEvents in Loops

Age Calculations

Weekday Math

Send Emails with DoCmd.SendObject

Source Code Library

Microsoft Access Modules Library

Microsoft Access Modules

VBA Error Handling

Error Handling and Debugging Techniques

Error Number and Description Reference

Basic Error Handling

Pinpointing the Error Line

Performance Tips

Linked Database

Subdatasheet Name

Visual SourceSafe

Deployment

Runtime Downloads

Simulate Runtime

Prevent Close Box

Disable Design Changes

Broken References

Remote Desktop Connection Setup

Terminal Services and RemoteApp Deployment

Reboot Remote Desktop

Missing Package & Deployment Wizard

Avoid Program Files Folder

Microsoft Access Front-End Deployment

System Admin

Disaster Recovery Plan

Compact Database

Compact on Close

Database Corruption

Class Not Registered Run-time Error -2147221164

Inconsistent Compile Error

Decompile Database

Bad DLL Calling Convention

Error 3045: Could Not Use

Converting ACCDB to MDB

SQL Server Upsizing

Microsoft Access to SQL Server Upsizing Center

Microsoft Access to SQL Server Upsizing Center

When and How to Upsize Access to SQL Server

SQL Server Express Versions and Downloads

Cloud and Azure

Cloud Implications

MS Access and SQL Azure

Deploying MS Access Linked to SQL Azure

Visual Studio LightSwitch

LightSwitch Introduction

Comparison Matrix

Additional Resources

Connect with Us

 

Free Product Catalog from FMS