VBA: Recording and Running Simple Macros, Saving Modes and Usage Tips

If you regularly perform repetitive tasks in Microsoft Office applications like Excel or Word, automating those tasks with macros can save you significant time and effort. Macros, written using Visual Basic for Applications (VBA), allow you to automate sequences of commands — whether it’s formatting data, running calculations, or customizing workflows.

This article explains how to record and run simple macros, how to save files containing VBA macros correctly, and practical tips to use macros effectively even when your files are saved in simpler formats.

What is a Macro in VBA?

A macro is a set of programming instructions that automatically performs certain tasks. VBA is the programming language used in Microsoft Office apps to write these macros. While writing complex VBA code requires programming knowledge, Recording Macros gives beginners an easy entry point to automation without writing code manually.

How Recording Macros

Activating the Developer Tab in Excel: A screenshot of the Excel Options window, highlighting the process of customizing the ribbon to include the Developer tab for advanced functionalities.

Step 1: Enable the Developer Tab

To access macro features, you first need to show the Developer tab in Excel or Word:

• Go to File > Options > Customize Ribbon.

• Check the box for Developer in the right pane.

• Click OK to enable the Developer tab.

The image showcases an Excel spreadsheet with a macro recording in progress, as indicated by the highlighted "Stop Recording" button under the "Developer" tab. The spreadsheet contains a green cell displaying "CentreofPower.com," and on the right, a Visual Basic for Applications (VBA) editor window reveals a script labeled "Formatting_cells" designed to format cells with specific properties and settings.

Step 2: Start Recording a Macro

1. Go to the Developer tab.

2. Click Record Macro.

3. In the dialog box:

   • Enter a macro name (no spaces).

   • Assign a shortcut key (optional).

   • Choose where to store the macro:

     • This Workbook (Excel) or This Document (Word) — macro will be saved with the current file.

     • Personal Macro Workbook — macro available in all Excel files on your PC.

   • Add a description (optional).

4. Click OK.

   
   

   Now, every action you perform will be recorded.

Step 3: Perform Your Actions

Carry out the tasks you want to automate. For example, format cells, enter formulas, or insert text. VBA will capture these steps.

Step 4: Stop Recording

Once finished, return to Developer tab and click Stop Recording to save your macro.

How to Run a Recorded Macro

To run your macro:

1. Go to Developer > Macros.

2. Select your macro from the list.

3. Click Run.

Alternatively, use the assigned shortcut key if you set one.

Excel Developer tab and VBA editor open for macro programming and automation tasks in a spreadsheet.

VBA Code Editing (Optional)

If you want to view or edit the macro code:

• Click Developer > Visual Basic.

• The Visual Basic for Applications editor opens.

• Locate your macro under Modules.

• Edit the VBA code as needed.

Saving Files with Macros: Different Modes and Tips

1. Save in Macro-Enabled Format:

When your file contains VBA macros, it must be saved in a macro-enabled format to preserve and run macros properly.

• For Excel: Save as .xlsm (Excel Macro-Enabled Workbook).

• For Word: Save as .docm (Word Macro-Enabled Document).

Saving as .xlsx or .docx will strip the macros, making them unavailable.

2. Saving a File in Simple Mode (without macro enabled extension)

If you save your file in a simple mode (e.g., .xlsx or .docx), the embedded macros are removed and won’t run. Any macro you recorded or wrote will be lost.

Tips if you must use simple modes:

• Export and import your VBA code outside of the document using .bas files.

• Maintain a macro-enabled template (.xltm or .dotm) separate from data files.

• Use Personal Macro Workbook in Excel to keep macros universally available regardless of file.

Using Macros When File is Saved in Simple Mode

If you accidentally save in simple mode but want to keep running your macro:

• You can copy the macro code from the VBA editor before saving, then re-import it after opening the file again in a macro-enabled format.

• Alternatively, store your macros in a Personal Macro Workbook (Excel) which loads in the background for all files without needing macros inside each file.

• For Word, consider using global templates (normal.dotm) to keep reusable macros accessible irrespective of document save format.

Important Security Considerations

• Always enable macro security settings appropriately — macros can contain malicious code.

• When opening macro-enabled files from unknown sources, prompt users or use protected view.

• Digitally sign your macros for trusted execution in corporate environments.

Tips for Beginners

• Start by recording simple repetitive tasks.

• Test macros on sample data before applying to important files.

• Comment your VBA code for clarity.

• Backup macro-enabled files regularly.

• Learn basic VBA gradually to customize recorded macros for better efficiency.

Conclusion

Recording and running simple macros is a powerful way to automate repetitive Office tasks with minimal technical knowledge. However, knowing how to save your files correctly in macro-enabled formats is essential to preserve functionality. When working with simple save modes, consider externalizing your VBA code or using global macro workbooks/templates to keep automation working smoothly.

Start experimenting today by recording your first macro, and watch how automation can save you time and boost your productivity!