Easily install an Office VBA macro 

Here’s how to easily install VBA code or macro in Microsoft Office.  Adding some code you’ve seen on a web site or been given is simple once you know the little tricks. Often people are given some Office VBA code to use with Word, Excel, PowerPoint or Outlook but without instructions for how to install and use the code.  It’s something that programmers assume everybody knows!

What is VBA and what can it do?

Visual Basic for Applications (VBA) is a programming language that users can use to extend Office applications. Basically, any operation that you might perform with a keyboard, mouse or dialog box can also be done through VBA.

VBA macros can be used to:

• Automate recurring tasks or combination of tasks.
• Customize a commonly repeated specialised task – for example a complex Find or Replace.
• Add a new function in Excel (called a UDF – User Defined Function)
• Replace an in-built function with a customized one.
• Automatically do things when a document is opened, saved or closed.
• Automatically do things when the program is opened or closed.

Install VBA in Word, Excel, PowerPoint or Outlook

Let’s say someone has given you some VBA code for your Word, Excel or PowerPoint document or to do more in Outlook.  Here’s how to install that code:

Firstly, open your office app (Word, Excel or PowerPoint) where you want to install the macro AND the document or template that the code should go.  The doc or template should be macro-enabled, see below.

If necessary, make the Developer tab visible.

Start VBA Editor

Open the VBA editor from Developer | Visual Basic or use the shortcut Alt + F11

With the opening view of the VBA editor, the important bits are on the left. Select the workbook or template where you want to install the macro from the Project Explorer window.

Normal – is the Normal.dotx template that most documents are based on.

Project – each document is a project.

There’s a lot going on in that left-side pane and we’re going to ignore almost all of it to focus on the basic task of adding VBA code to a document (doc, sheet or deck).

Insert a Module

This is the step that trips up novices.  You’d think that VBA code is just added to the document but it’s not.  Insert a Module to the document and then add the code into the Module.

Right-click on the document, choose Insert | Module.  To add code that applies to all Word documents, add a module to the Normal template.

Now there’s a Modules section added under the document and a blank pane appears.

Add code to the Module

Here you’ll be able to paste the VBA code  you’ve been given. The editor will nicely color code for easier reading and show some mistakes in red.  Comment lines (starting with a ‘ character) are in green.

If there are many VBA code blocks (Sub, Function) then the pull-down list at top right is very handy.

VBA code pasting tip and warning

When pasting in code, there can be problems with the end of lines.  The pasted code might have line breaks instead of a full new line (Enter key or CR/LF).  If you’re seeing strange errors for seemingly OK code lines, that could be the reason.  Delete the existing end of line and replace by pressing the Enter key.

Only install macros from trusted sources, some potentially harm your computer or steal your data. 

Make sure you understand the code or, at the very least, be clear that the code doesn’t have any calls to external services or unusual lines.

Save the code

Press the Save button on the toolbar or Ctrl + S to save the code and the document/template.

If the doc/template isn’t macro enabled, you’ll get a warning to change the document file extension.

Macro-enabled Office document extensions

VBA code will only be saved and run from a macro-enabled version of the Office document with the correct file extension.  Instead of ending in ‘X’, macro documents end with ‘M’.

The main macro enabled document and template extensions are:

Word – .docm or dotm

Excel – .xlsm or .xltm

PowerPoint – .pptm or .potm

Run the VBA code

To test the code of a Subroutine (Sub), use the Run, Pause and Stop buttons on the toolbar.

It’s important to note that some macros will need additional configuration before they can be used.

This is a very simple explanation and skips over a lot of the power in VBA and the VBA Editor.

Copy VBA Macros between Microsoft Word documents
Understand Office VBA code with ChatGPT
Inside the Office VBA/Mark of the Web changes
Easy Office document hacking with VBA Stomping or Purging