Study guide
2.3 Macro Security, Portability, & Workbook Distribution
Microsoft Excel Expert Microsoft 365 Apps
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Exam Overview & MO-211 Fundamentals
1.1 MO-211 Exam Structure, Scoring, & Performance-Based Strategy 1.2 Excel Environment & Microsoft 365 Architecture
2 Chapter 2: Managing Workbooks & File Options
2.1 Workbook Versioning, AutoRecover, & File Properties 2.2 External Data Referencing & Cross-Workbook Formulas 2.3 Macro Security, Portability, & Workbook Distribution
3 Chapter 3: Workbook Protection & Collaboration Settings
3.1 Cell Locking, Range Permissions, & Worksheet Protection 3.2 Workbook Structure Protection & Editing Restrictions 3.3 Formula Calculation Options & Iterative Calculations
4 Chapter 4: Advanced Data Entry & Series
4.1 Flash Fill Patterns & Text Extraction Techniques 4.2 Advanced Fill Series (Linear, Growth, Date, & Step Configurations) 4.3 Dynamic Sequence & Random Data Generation (RANDARRAY, SEQUENCE)
5 Chapter 5: Custom Formatting & Data Validation
5.1 Custom Number Formatting Syntax & Masking Rules 5.2 Data Validation Criteria & Custom Formula Rules 5.3 Validation Input Messages, Error Alerts, & Data Circle Auditing
6 Chapter 6: Data Grouping, Summarization, & Cleansing
6.1 Data Grouping, Ungrouping, & Multi-Level Outlining 6.2 Subtotal Insertion & Summary Function Calculations 6.3 Duplicate Record Removal & Text-to-Columns Cleansing
7 Chapter 7: Advanced Conditional Formatting & Filtering
7.1 Formula-Based Conditional Formatting & Logical Tests 7.2 Data Bars, Color Scales, Icon Sets, & Rule Hierarchy 7.3 Advanced Filtering with Complex Criteria Ranges
8 Chapter 8: Logical Operations & Advanced Multi-Condition Functions
8.1 Nested IF, IFS, & SWITCH Logical Structures 8.2 Compound Boolean Logic: AND, OR, NOT, & XOR 8.3 Multi-Criteria Aggregations: SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, & MINIFS
9 Chapter 9: Advanced Lookup Functions & Variable Optimization
9.1 Modern Lookups with XLOOKUP & XMATCH 9.2 Classic Matrix & Two-Way Lookups: INDEX & MATCH 9.3 Formula Optimization & Variable Assignment with LET
10 Chapter 10: Date, Time, Text, & Auditing Functions
10.1 Current Date, Time, & Day-of-Week Functions: NOW, TODAY, & WEEKDAY 10.2 Business Date Calculations: WORKDAY, NETWORKDAYS, EDATE, & EOMONTH 10.3 Modern Text Extraction & Array Splitting: TEXTSPLIT, TEXTJOIN, TEXTBEFORE, TEXTAFTER 10.4 Error Handling & Formula Auditing Tools
11 Chapter 11: Dynamic Array Functions & Spill Operations
11.1 Array Filtering & Extraction: FILTER Function 11.2 Multi-Level Sorting: SORT & SORTBY Functions 11.3 Deduplication & Sequence Arrays: UNIQUE & Array Reshaping
12 Chapter 12: Data Analysis, What-If Tools, & Financial Functions
12.1 Summarizing Multiple Ranges with the Consolidate Feature 12.2 What-If Analysis: Goal Seek & Scenario Manager 12.3 Financial Modeling Functions: PMT, NPER, & Forecast Logic
13 Chapter 13: Macro Automation & VBA Fundamentals
13.1 Recording Macros with Absolute vs. Relative References 13.2 Macro Storage, Shortcut Assignment, & Form Control Buttons 13.3 Managing & Modifying VBA Code in the Visual Basic Editor
14 Chapter 14: Advanced & Specialized Visualizations
14.1 Statistical Visualizations: Histograms & Pareto Charts 14.2 Hierarchy & Financial Visualizations: Waterfall, Funnel, Treemap, & Sunburst 14.3 Distribution & Multi-Axis Charts: Box & Whisker & Dual-Axis Combo Charts
15 Chapter 15: PivotTable Construction & Field Configuration
15.1 PivotTable Data Architecture, Layouts, & Style Configuration 15.2 Field Summarization, Number Formatting, & Custom Calculations 15.3 Value Display Modes: % of Total, Running Total, & Differences
16 Chapter 16: PivotTable Grouping, Slicers, & PivotCharts
16.1 Date & Numeric Field Grouping Strategies 16.2 Calculated Fields & Calculated Items in PivotTables 16.3 Slicers, Timelines, Report Connections, & PivotCharts
2.3 Macro Security, Portability, & Workbook Distribution
Key Takeaways Explain This Topic AI
Excel's default macro security setting in Trust Center is 'Disable VBA macros with notification', which alerts users via the Message Bar and blocks execution until explicitly approved.
Workbooks opened from designated Trusted Locations bypass macro security checks completely, executing VBA code automatically without displaying warning banners or sandbox restrictions.
Files downloaded from the internet or received via email carry the Mark of the Web (MOTW) attribute (ZoneId=3), triggering Protected View and blocking macros by default until unblocked in file properties.
Code modules can be ported between workbooks by exporting and importing standard .bas, .frm, and .cls files, or by dragging modules across projects in the Visual Basic Editor (VBE) Project Explorer.
Saving a macro-enabled workbook as standard .xlsx permanently strips all embedded VBA code modules without warning bypass, requiring .xlsm or .xlsb formats for macro preservation.
Last updated: September 2026
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: File ⟶ Options ⟶ Trust Center ⟶ Trust Center Settings... ⟶ Macro Settings \text{File} \longrightarrow \text{Options} \longrightarrow \text{Trust Center} \longrightarrow \text{Trust Center Settings...} \longrightarrow \text{Macro Settings} File ⟶ Options ⟶ Trust Center ⟶ Trust Center Settings... ⟶ Macro Settings
Under the Macro Settings heading, Excel presents four distinct security levels:
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.
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.
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.
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.
Trust Center Macro Setting Unsigned Macro Behavior Signed Macro Behavior User Prompt Displayed?
- Disable without notification Blocked silently Blocked silently No
- Disable with notification (Default) Blocked until approved Blocked until approved Yes (Yellow Message Bar)
Digitally signed only Blocked with security alert Runs if publisher trusted Yes for unsigned; also if publisher unknown
- Enable all macros Runs automatically Runs automatically No (Unsafe)
- 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:
Navigate to File > Options > Trust Center > Trust Center Settings > Trusted Locations .
Click Add new location... .
Browse to select the desired directory path.
Optional: Check "Subfolders of this location are also trusted" to include child directories recursively.
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:
Open the source workbook in Excel and press Alt+F11 to launch the Visual Basic Editor.
In the Project Explorer pane ( Ctrl+R ), right-click the module you wish to export (e.g., Module1 ).
Select Export File... (or press Ctrl+M ).
Choose a destination folder and save the file (Standard modules save as .bas , UserForms as .frm , Class modules as .cls ).
Open the target workbook in Excel and switch to its VBE project window.
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:
Launch both workbooks in the same Excel session.
Press Alt+F11 to enter the Visual Basic Editor.
In the Project Explorer window, ensure both projects are visible: VBAProject (SourceWorkbook.xlsm) and VBAProject (TargetWorkbook.xlsm) .
Click and hold the desired module (e.g., Module1 ) under the source project.
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.