Running VBA Code from a Microsoft Access Macro

To use combo boxes effectively, learn about the following properties:

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 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:

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.

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.

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

Create a new module

From the Create ribbon, choose 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:

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:

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

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:

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.