VBA: Recording and Running Simple Macros in VBA, Saving Modes and Advanced Usage Tips for Simple Workbook Formats

Automating repetitive tasks using macros in Microsoft Office applications like Excel, Word, and PowerPoint can significantly boost your productivity. Whether you're a beginner recording simple macros or a power user customizing VBA code, understanding how to save files with macros and use macros when saved in simple (non-macro) formats is crucial to keeping your automation effective.

In this article, we’ll explore:

• Recording and running simple macros across MS Office apps.

• The correct saving modes to preserve VBA code.

• How to use macros even when files are saved in simple formats like .xlsx, .pptx, or .docx.

• Advanced tips including Quick Access Toolbar integration, very hidden sheets, and other macro usage possibilities.

Recording and Running Simple Macros: A Quick Overview

Before diving into saving modes, here’s a quick refresher on recording and executing macros:

• Recording a macro: Enabled via the Developer tab (Record Macro), it captures your actions to automate later.

• Running a macro: Use the Developer tab’s Macros button or assign shortcut keys.

• Editing macros: Use the VBA editor (Alt + F11) to tweak recorded code or write new VBA procedures.

This process is consistent across Excel, Word, and PowerPoint, though PowerPoint macros are less used and require saving in macro-enabled formats.

Saving a workbook as an "Excel Macro-Enabled Workbook" using the "Save As" feature in the file menu of Microsoft Excel.

Macros saving formats: Supported Formats in MS Office

Excel

• .xlsm — Excel Macro-Enabled Workbook: Saves all VBA macros and editable contents.

• .xlsb — Excel Binary Workbook: Supports macros, offers compression for faster load times.

• .xltm — Excel Template Macro-Enabled: Saves macro-enabled templates.

• .xlsx — Excel Workbook: Saves without macros (macros are stripped on save).

Word

• .docm — Macro-Enabled Document: Saves VBA macros with the document.

• .dotm — Macro-Enabled Template: For templates with macros.

• .docx — Word Document: Macro-free format (macros removed upon saving).

PowerPoint

• .pptm — Macro-Enabled Presentation: Contains macros and VBA code.

• .potm — Macro-Enabled Template: Template with macros.

• .pptx — Regular PowerPoint Presentation: No macros saved.

What Happens When You Save in Simple Formats Like .xlsx or .pptx?

When you save a file in a simple (macro-disabled) format, all embedded VBA code and recorded macros are removed from the saved file. However, there are ways to keep macros functional or accessible even if you frequently use simple formats:

Using Macros When the File is Saved in Simple (Non-Macro) Formats: Workarounds and Possibilities

1. Add Macros to the Quick Access Toolbar (QAT)

• You can add frequently used macros to the Quick Access Toolbar in Excel or Word.

• This works because macros stored in Personal Macro Workbook (Excel) or global templates (Word’s Normal.dotm) are available independently of the active file.

• Even if the file you open is .xlsx or .docx (macro-free), you can still run global macros via QAT shortcuts.

Demonstrating macro integration in Excel: Customizing the Quick Access Toolbar and utilizing VBA to automate cell formatting, highlighted by CentreofPower.com.

Step-by-step: Adding Macros to Quick Access Toolbar in Excel

1. Press Alt + F8 to open the Macro dialog box.

2. Select the macro you want to add.

3. Click Options to assign a shortcut key (optional), then click OK.

4. Close the Macro dialog.

5. Right-click anywhere on the Quick Access Toolbar (usually at the top-left).

6. Select Customize Quick Access Toolbar.

7. In the Choose commands from: dropdown, select Macros.

8. Find your macro in the list, select it, and click Add >>.

9. Click Modify to choose an icon and rename the button if you want.

10. Click OK to save.

Now your macro is available via the QAT for all workbooks when stored in your Personal Macro Workbook.

2. Store VBA Code in the Personal Macro Workbook or Global Templates

• Excel:

  
  

  The Personal Macro Workbook (PERSONAL.XLSB) loads in the background every time Excel opens. Any macros stored here are globally available.

Excel macro recording and editing: the left side shows the Macro Recording tool and cell selection, while the right side displays the VBA editor with the recorded code for adjusting cell formatting and alignment in Excel.

Step-by-step: Creating and Storing Macros in Personal Macro Workbook

1. Open Excel.

2. Go to the Developer tab.

3. Click Record Macro.

4. In the Store macro in: dropdown, choose Personal Macro Workbook.

5. Name your macro and record actions.

6. When you’re done, click Stop Recording.

7. Excel will prompt to save the Personal Macro Workbook on exit if it’s the first time.

8. Save it to ensure macros are available globally.

9. Word:

   
   

   Macros saved in the global template (Normal.dotm) are accessible in all Word documents.

Creating and Assigning a Macro in Microsoft Word: The image demonstrates the steps involved in recording a macro, assigning it to a button, and adding it to the Quick Access Toolbar using the Developer tab. The process includes naming the macro, selecting storage options, and using the Visual Basic Editor for customization.

Step-by-step: Saving Macros in Word Global Template

1. Open Word.

2. Go to Developer > Record Macro.

3. Set Store macro in: to All Documents (Normal.dotm).

4. Perform the actions and stop recording.

5. Restart Word to apply.

6. PowerPoint:

   
   

   Unlike Excel and Word, PowerPoint does not have a global macro workbook, but you can create add-ins or macro-enabled templates for reuse.

3. Use “Very Hidden” Sheets in Excel for Macro Storage and Data

• Very Hidden sheets can only be made visible via VBA, enhancing security.

Excel VBA environment showcasing a macro development setup with sheet visibility options highlighted, and a worksheet labeled “CentreofPower.com” visible on Sheet1.

Step-by-step: Hiding a Sheet as Very Hidden in Excel

1. Press Alt + F11 to open the VBA Editor.

2. In the Project Explorer, select the worksheet you want to hide.

3. Press F4 to open the Properties Window.

4. Set the Visible property to xlSheetVeryHidden.

5. Save the workbook as .xlsm (macro-enabled).

This sheet won't be visible from Excel’s UI but is accessible via VBA code, which can be used even when some sheets are hidden.

4. Export and Import VBA Modules Separately

You can export VBA modules as files and import them later.

Exporting a VBA module in Excel: The Developer tab is accessed to open Visual Basic for Applications, subsequently exporting a module as a .bas file through the right-click context menu.

Step-by-step: Exporting a VBA Module

1. Open the VBA Editor (Alt + F11).

2. Right-click the module in Project Explorer.

3. Select Export File….

4. Choose a save location and save as .bas file.

Step-by-step: Importing a VBA Module

1. Open VBA Editor.

2. Right-click on your project.

3. Click Import File….

4. Select the .bas file to import.

This is useful when working with simple .xlsx files but wanting to reinstate macros when needed.

5. Use Add-ins for Macro Distribution

Create an Excel Add-in .xlam file to package macros, loaded independently.

Selecting "Excel Add-In" as the file type in the "Save As" dialog box for an Excel workbook, indicating the preparation for saving a VBA project.

Step-by-step: Creating and Loading an Add-in

1. Open Excel and create your macros.

2. Save as Excel Add-in (.xlam) via File > Save As.

3. Close and reopen Excel.

4. Go to File > Options > Add-ins.

5. At the bottom, select Excel Add-ins and click Go.

6. Click Browse, navigate to your .xlam file and add it.

7. Ensure it’s checked, then click OK.

Your macros from the Add-in now work in any open workbook.

6. Calling Macros from External Applications

For advanced scenarios, use external scripting languages to automate macros.

Example: Running VBA Macro via PowerShell

powershell

$Excel = New-Object -ComObject Excel.Application

$Workbook = $Excel.Workbooks.Open("C:\Path\To\File.xlsm")

$Excel.Run("MacroName")

$Workbook.Save()

$Excel.Quit()

Important Notes and Best Practices

• Always save your working macro-enabled files as .xlsm, .docm, or .pptm if you want to keep code embedded.

• Using Personal Macro Workbook, global templates, or add-ins are best if you need macros without embedding in every file.

• Backup your macro code externally using export/import to prevent loss.

• Macro-enabled files may prompt security warnings — digitally sign your code if possible.

• Briefly test macros after saving to different formats to verify functionality.

• Document your VBA projects for easier maintenance.

Summary Table: Saving Modes & Macro Availability Across MS Office

Application	Macro-Enabled Save Format	Macro-Free Save Format	Macros Usable in Simple Format?	Notes
Excel	.xlsm, .xlsb, .xltm	.xlsx	Yes, via Personal Macro Workbook, Add-ins, QAT macros	Macros are lost in .xlsx but global macros work
Word	.docm, .dotm	.docx	Yes, via Normal.dotm and QAT	Global template macros accessible regardless of doc format
PowerPoint	.pptm, .potm	.pptx	Limited, use add-ins or macro-enabled templates	No personal macro workbook, macros embedded in templates

Conclusion

Recording and running simple macros is a great starting point to automate your Office tasks.

To preserve and maximize macro usability:

• Always save macro-enabled files in correct formats.

• Use Personal Macro Workbook, global templates, or add-ins to run macros when working with simple (.xlsx, .docx) files.

• Explore very hidden sheets and external VBA module management to enhance your macros’ robustness and security.

• Leverage the Quick Access Toolbar to easily access your macros globally.

With these step-by-step instructions and best practices, you can design macros that adapt to your workflow needs — regardless of your file save format.