As you become more proficient in Microsoft Access, you begin to realize that there is a lot of duplicate effort and repetitive tasks that are well suited for automation. In Microsoft products, automation is done through the creation of macros in a specific type code language called Visual Basic for Applications, or VBA.

Macros are typically buttons that users can press to execute repetitive tasks or procedures, such as formatting or filling out specific forms for a new customer.


End users can simply click a button and Microsoft will execute the VBA code you have programmed to begin the process. The bigger a database becomes and the more reliant a business is on the data it contains, the more you need to automate a process to ensure consistency and accuracy. Macros give you the ability to simplify your processes. From updating field types to indexing tables, there are a lot of actions that can be automated so that your maintenance time is significantly reduced. You can even write macros that will run a regular search and removal of duplicate records.

VBA is a very basic language that can be learned with a mix of recording actions and working in the code. However, like any language, you should continue to work in a sandbox or an experimental area until you are confident that your code works.

Recording a Macro versus Writing a Macro

The best way to start learning VBA is through experience. Before you even open a book or visit a website to learn about macros, you can get an idea of how the code works by recording a macro.

The difference between recording a macro and writing a macro from scratch is that recording a macro will provide all of the possibilities in the code. For example, if you were to format the font of a form, the recorded macro would show you every possible option for the font properties. If you were to write the same macro from scratch, you would probably only include the single element that you want to change, such as changing the font size to 12 or the type to italics.

Benefits of Recording Macros

When you start writing macros, you should try recording a few macros. If you aren’t sure where to start, think about repetitive actions that you do to a report every time you generate that report. Is there something that you want to change in the formatting? Would you like to create a similar report with a few modifications for meetings? Reports will be the easiest starting point because you probably already have to manipulate the reports after they are generated.

For at least the first few months, it is best to stick to recording macros to start them. There is a lot of generic code that goes into VBA, and recording a macro will take care of all of the stuff that has to be there, saving you a lot of time typing it out. Even after you become adept with VBA, you will likely find that you begin with the recording.

Benefits of VBA Coding

There are two distinct advantages to writing code from scratch (or the majority of it after the initial recording): less unnecessary code and comments.

When you are first writing macros, recording your macros is going to be great because it will give you all the code you need. The problem is that you are going to have to comb through a lot of code you don’t need to find the code that you need to change to get what you want. Recording a macro tends to be flawed and limited. For example, if you run a find for the word “Key” during a recording, the macro will only search for the word key. If you code the macro to look for the next word on the list, instead of specifying the word for the find, you will be able to use that macro longer and for more than one project.

Comments are another very important and too often forgotten part of coding in VBA. Because VBA is simpler than many other languages, it is one that many people learn, including people who don’t know how to create comments or see the value in them. Even if you record a macro, you should go back in and add comments so that the macro can be updated later if the situation changes. One of the most frustrating parts of coding is going into spaghetti code and trying to figure out what each call does. It’s time consuming and painful. By adding a brief comment at the beginning of a new process in the code, you can save yourself and coworkers a lot of headaches later when you have to make changes.

Security When Dealing with Macros

Something to keep in mind with macros is that they are a potential security risk. If you receive a file that you open and it warns you that the database has macros, you need to contact the sender to make sure those macros are alright before you enable them. If you add macros to files that you will sending through email or another form of communication, make sure you tell the recipient. Typically, it is best to remove the macros before you send the files as the recipients likely will not need the macros.