How to enable and disable macros in Excel

This article explores how to enable macros in Excel, covering macro security basics and safe VBA code execution. Macros, like any technology, have dual potential—beneficial automation or malicious use. Excel's default setting disables macros for safety.

• Excel Macro Security
• Enabling Macros for Individual Workbooks
  • Enabling via Security Warning Bar
  • Enabling via Backstage View
  • Understanding Trusted Documents
• Unblocking Microsoft-Blocked Macros
• Temporarily Enabling Macros (Current Session)
• Enabling All Macros via Trust Center
• Excel Macro Settings Explained
• Permanently Enabling Macros in Trusted Locations
• Programmatic Macro Enabling with VBA
• Disabling Macros in Excel

Excel Macro Security

Before enabling macros, understand the potential risks. While VBA automates tasks, malicious macros pose security threats. They can damage files, corrupt data, or harm your Office installation. Excel's default "disable all macros with notification" setting mitigates this. The key is to only enable safe macros: those you've created, from trusted sources, or thoroughly reviewed.

Enabling Macros for Individual Workbooks

Two methods enable macros for specific files: directly from the workbook or via the Backstage view.

Enabling via Security Warning Bar:

The default setting displays a yellow security warning bar when opening macro-enabled workbooks. If you trust the source, click "Enable Content" or "Enable Macros." This designates the file as "trusted." Subsequent openings won't show the warning. Ignoring the warning keeps macros disabled. Accidental disabling is easily reversed by re-opening and clicking "Enable Content."

Enabling via Backstage View:

Alternatively, enable macros through the Backstage view:

1. Click the "File" tab, then "Info."
2. In the "Security Warning" area, click "Enable Content" > "Enable All Content."

This also marks the workbook as trusted.

Trusted Documents in Excel:

Enabling macros via either method creates a trusted document. However, some files (e.g., from unsafe locations or subject to organizational security policies) can't be trusted. Macros are then enabled only temporarily. To avoid this, adjust Trust Center settings or save to a trusted location. Untrusting a document isn't possible; only clearing the Trusted Documents list is. This is done by:

1. Clicking "File" > "Options."
2. Selecting "Trust Center" > "Trust Center Settings."
3. Choosing "Trusted Documents" and clicking "Clear."

Disabling trusted documents prevents automatic trust but still allows one-time macro enabling.

Resolving "Microsoft has blocked macros"

Newer Excel versions block macros from untrusted internet sources. A security risk bar appears instead of the yellow warning. "Learn More" provides explanations but doesn't unblock the macro.

Unblocking a Macro Workbook:

To unblock:

1. Close the workbook.
2. Right-click the file in File Explorer and select "Properties."
3. Check the "Unblock" box and click "OK."

Enabling Macros for One Session

For temporary macro use (e.g., investigating code without trusting the file):

1. Click "File" > "Info."
2. Click "Enable Content" > "Advanced Options."
3. Select "Enable content for this session" and click "OK."

Macros are enabled only for the current session.

Enabling Macros in All Workbooks via Trust Center

The Trust Center controls Excel's macro security settings. To enable all macros by default:

1. Click "File" > "Options."
2. Select "Trust Center" > "Trust Center Settings."
3. Choose "Macro Settings" > "Enable all macros" > "OK."

Caution: This significantly increases vulnerability to malicious code.

Excel Macro Settings Explained

The Trust Center offers several macro settings:

• Disable all macros without notification: Silently disables all macros.
• Disable all macros with notification: (Default) Disables macros with a warning allowing manual enabling.
• Disable all macros except digitally signed macros: Disables unsigned macros, allowing digitally signed ones.
• Enable all macros: Enables all macros (strongly discouraged).
• Trust access to the VBA project object model: Controls programmatic access to the VBA object model (disabled by default).

These settings apply only to Excel.

Enabling Macros Permanently in a Trusted Location

Instead of changing global settings, designate trusted locations. Files within these locations have macros enabled automatically, even with the "Disable all macros without notification" setting.

To add a trusted location:

1. Click "File" > "Options."
2. Select "Trust Center" > "Trust Center Settings."
3. Choose "Trusted Locations" > "Add new location."
4. Browse to the desired folder, optionally include subfolders, add a description, and click "OK."

Use caution when choosing trusted locations to avoid security risks. Remove locations as needed.

Programmatic Macro Enabling with VBA

Direct programmatic macro enabling isn't possible due to security restrictions. Workarounds exist, such as creating a "splash screen" prompting users to enable macros.

Disabling Macros in Excel

To silently disable all macros:

1. Click "File" > "Options."
2. Select "Trust Center" > "Trust Center Settings."
3. Choose "Macro Settings" > "Disable all macros without notification" > "OK."

This article provides comprehensive guidance on managing Excel macros and their security implications.

The above is the detailed content of How to enable and disable macros in Excel. For more information, please follow other related articles on the PHP Chinese website!