2.3 Macro Security, Portability, & Workbook Distribution

2.3 Macro Security, Portability, & Workbook Distribution

Automation via Visual Basic for Applications (VBA) is one of Microsoft Excel's most powerful enterprise capabilities. However, because macro code possesses the technical capability to execute arbitrary operating system commands, interact with local file systems, and communicate over networks, it introduces significant security vulnerabilities. On the MO-211 Microsoft Excel Expert examination, candidates are tested on Trust Center macro execution policies, Trusted Locations and Trusted Documents governance, Protected View and Mark of the Web (MOTW) mechanisms, and the administrative procedures required to port VBA modules between workbooks.

---

Trust Center Macro Security Hierarchy

Excel manages macro execution through the Trust Center, the centralized security console for Microsoft 365 Apps. To inspect or modify global macro security settings, navigate through the Backstage interface:

$\text{File} \longrightarrow \text{Options} \longrightarrow \text{Trust Center} \longrightarrow \text{Trust Center Settings...} \longrightarrow \text{Macro Settings}$

Under the Macro Settings heading, Excel presents four distinct security levels:

1. Disable VBA macros without notification: All macros embedded within opened workbooks are completely blocked from execution. Excel displays no warning banner, Message Bar, or prompt. This represents the strictest security posture but prevents users from running legitimate automations without warning.
2. Disable VBA macros with notification (Default Setting): All macros are disabled upon opening by default. However, Excel displays a yellow Security Warning Message Bar directly beneath the ribbon: "SECURITY WARNING Macros have been disabled. [Enable Content]". Clicking Enable Content unblocks macro execution for that editing session and registers the file as a Trusted Document.
3. Disable VBA macros except digitally signed macros: Macros that carry a valid cryptographic digital signature issued by a trusted commercial Certificate Authority (CA) or an enterprise Public Key Infrastructure (PKI) run immediately when the publisher is already trusted. If the certificate originates from a publisher you have not trusted yet, Excel offers you the chance to enable the signed macro and trust that publisher. Unsigned macros are disabled with a security alert — Microsoft documents this setting as "Macros are disabled, and security alerts appear if there are unsigned macros present." This setting therefore never suppresses unsigned code silently; only Disable VBA macros without notification does that.
4. Enable VBA macros (not recommended; potentially dangerous code can run): All macros run automatically without inspection, validation, or security prompts. This setting exposes the host environment to catastrophic malware execution and is strictly prohibited in regulated corporate environments.

Developer Macro Settings

Within the same dialog, the checkbox "Trust access to the VBA project object model" controls whether external automation scripts, COM add-ins, or programmatic routines can access and manipulate the Visual Basic Editor (VBE) code environment. By default, this option is disabled to prevent rogue macros from dynamically injecting malicious code into other opened workbooks.

---

Trusted Locations, Trusted Documents, & Mark of the Web (MOTW)

Rather than lowering enterprise macro security settings globally, Microsoft recommends scoping execution authority using Trusted Locations and Trusted Documents.

Trusted Locations

A Trusted Location is a designated physical or network directory deemed completely secure. Any workbook opened from a Trusted Location bypasses the Trust Center macro inspection engine entirely; all embedded VBA subroutines, custom functions, and event procedures execute immediately without generating warning banners.

To establish a Trusted Location:

1. Navigate to File > Options > Trust Center > Trust Center Settings > Trusted Locations.
2. Click Add new location....
3. Browse to select the desired directory path.
4. Optional: Check "Subfolders of this location are also trusted" to include child directories recursively.
5. Optional: Check "Allow Trusted Locations on my network (not recommended)" if designating an intranet UNC path (\\Server\FinModels).

Trusted Documents

When a user opens a macro-enabled workbook from an untrusted directory and clicks Enable Content on the yellow Message Bar, Excel records that specific file's cryptographic hash in the user's local profile registry as a Trusted Document. On subsequent openings, Excel recognizes the document and executes macros automatically without redisplaying the warning bar. Users can purge this cached trust registry at any time by navigating to File > Options > Trust Center > Trust Center Settings > Trusted Documents and clicking Clear.

Mark of the Web (MOTW) & Protected View

When workbooks are downloaded from the public internet, retrieved from cloud repositories via web browsers, or saved from email client attachments (such as Microsoft Outlook), the operating system flags the file with an NTFS alternate data stream known as Mark of the Web (MOTW) (Zone.Identifier=3).

When Excel opens an MOTW file:

• The workbook launches inside Protected View—a locked, read-only sandbox environment where macros, data connections, and editing tools are disabled to prevent zero-day payload execution.
• In modern Microsoft 365 installations, macros originating from MOTW files are blocked completely by default with a red Security Risk banner: "SECURITY RISK: Microsoft has blocked macros from running because the source of this file is untrusted."
• To legitimately run macros in an MOTW file, the user must save the file locally, right-click the file in Windows File Explorer, select Properties, check the Unblock checkbox located at the bottom of the General tab, and click Apply.

---

Macro Portability: Transferring Modules in the Visual Basic Editor

A primary responsibility of an Excel Expert is transferring established automation routines, custom User-Defined Functions (UDFs), and user forms between workbooks without rewriting code from scratch.

Architecture of VBA Project Components

Inside the Visual Basic Editor (Alt+F11), workbook code is organized into discrete component types within the Project Explorer (Ctrl+R):

• Standard Code Modules (.bas): General macro subroutines and custom worksheet functions.
• UserForms (.frm / .frx): Interactive graphical user interface forms and dialog boxes.
• Class Modules (.cls): Custom object definitions, properties, and application-level event sinks.
• Document Objects (ThisWorkbook, Sheet1): Event procedures tightly coupled to workbook or worksheet lifecycle events (Workbook_Open, Worksheet_Change).

Method 1: Exporting & Importing Module Files

Exporting converts binary module assets into plain-text ASCII files that can be stored in source control repositories or imported into target workbooks:

1. Open the source workbook in Excel and press Alt+F11 to launch the Visual Basic Editor.
2. In the Project Explorer pane (Ctrl+R), right-click the module you wish to export (e.g., Module1).
3. Select Export File... (or press Ctrl+M).
4. Choose a destination folder and save the file (Standard modules save as .bas, UserForms as .frm, Class modules as .cls).
5. Open the target workbook in Excel and switch to its VBE project window.
6. Right-click any folder or module in the target VBAProject tree, select Import File..., and select the exported .bas file.

Method 2: Direct Drag-and-Drop in Project Explorer

When both the source and target workbooks are open concurrently in Excel, modules can be transferred instantly:

1. Launch both workbooks in the same Excel session.
2. Press Alt+F11 to enter the Visual Basic Editor.
3. In the Project Explorer window, ensure both projects are visible: VBAProject (SourceWorkbook.xlsm) and VBAProject (TargetWorkbook.xlsm).
4. Click and hold the desired module (e.g., Module1) under the source project.
5. Drag the module cursor over the target workbook project node and release the mouse button.

Excel creates an instantaneous, independent clone of the module inside the target project.

File Format Preservation: Saving Macro Workbooks

Standard Excel workbooks (.xlsx) use an XML schema that strictly prohibits embedded executable code. If an analyst adds a macro module to a .xlsx file and clicks Save, Excel presents a modal error dialog:

"The following features cannot be saved in macro-free workbooks: • VB project. To save a file with these features, click No, and then choose a macro-enabled file type in the File Type list."

If the user clicks Yes, Excel strips all VBA modules permanently. To preserve macros, the candidate must select No and save the file under a macro-compatible format:

• Excel Macro-Enabled Workbook (.xlsm): Standard OpenXML macro container.
• Excel Binary Workbook (.xlsb): High-performance binary format ideal for massive macro-driven models.
• Excel Macro-Enabled Template (.xltm): Boilerplate template containing macros.