The article looks at how to turn on macros in Excel, explains the basics of macro security and shows how to adjust the security settings to run VBA codes safely.
Like almost any technology, macros can be used for both good and evil. Therefore, in Microsoft Excel, all macros are disabled by default. This tutorial covers different ways to enable macros in Excel and explains potential risks associated with that.
Before you go enable macros in your worksheets, it's important to understand how dangerous they can possibly be.
Though VBA codes are very effective in automating complex and repetitious tasks, they are a significant source of risk from the security point of view. A malicious macro that you run unwittingly may damage or completely delete files on your hard drive, mess up your data, and even corrupt your Microsoft Office installation. For this reason, Excel's default setting is to disable all macros with notification.
How to avoid these dangers? Just follow one simple rule: enable only safe macros – ones that you've written or recorded yourself, macros from trusted sources, and VBA codes that you have reviewed and fully understand.
There are two ways to turn on macros for a certain file: directly from the workbook and through the Backstage view.
With the default macro settings, when you first open a workbook containing macros, the yellow security warning bar appears at the top of the sheet right under the ribbon:
If the Visual Basic Editor is open at the time you are opening the file with macros, the Microsoft Excel Security Notice will be displayed:
If you trust the source of the file and know that all the macros are secure, click the Enable Content or Enable Macros button. This will turn on the macros and make the file a trusted document. The next time you open the workbook, the security warning won't appear.
If the source of the file is unknown and you don't want to enable macros, you can click the 'X' button to close the security warning. The warning will disappear, but macros will remain disabled. Any attempt to run a macro will result in the following message.
If you've disabled macros accidentally, simply re-open the workbook, and then click the Enable Content button on the warning bar.
Another way to enable macros for a specific workbook is via the Office Backstage view. Here's how:
As with the previous method, your workbook will become a trusted document.
Enabling macros through either a message bar or Backstage view makes the file a trusted document. However, some Excel files cannot be made trusted documents. For examples, files opened from an unsafe location such as the Temp Folder, or if the system administrator has set the security policy in your organization to disable all macros without notification. In such cases, the macros are only enabled for a single time. On the next opening of the file, Excel will prompt you to enable the content again. To avoid this, you can change your Trust Center settings or save the file to a trusted location.
Once a particular workbook becomes a trusted document, there is no way to un-trust it. You can only clear the Trusted Documents list. For this, do the following:
This will make all previously trusted files untrusted. When you open such a file, the security warning will show up.
Tip. If you do not want to make any documents trusted, tick the Disable Trusted Documents box. You will still be able to turn on macros on opening a workbook, but only for the current session.
In some situations, it stands to reason to enable macros only for a single time. For example, when you received an Excel file with VBA code that you'd like to investigate, but you do not wish to make this file a trusted document.
The following instructions will guide you through the steps to enable macros for the duration that the file is open:
This turns on macros for one time. When you close the workbook, and then reopen it, the warning will appear again.
Microsoft Excel determines whether to allow or disallow VBA codes to run based on the macro setting selected in the Trust Center, which is the place where you configure all the security settings for Excel.
To get macros enabled in all Excel workbooks by default, this is what you need to do:
Bellow we will briefly explain all macro settings in the Trust Center to help you make an informed decision:
When changing the Trust Center settings, please keep in mind that they apply only to Excel, not to all Office programs.
Instead of manipulating the global macro settings, you can configure Excel to trust specific locations on your computer or local network. Any Excel file in a trusted location opens with macros enabled and without security warnings, even if the Disable all macros without notification option is selected in the Trust Center settings. This lets you run macros in certain workbooks when all other Excel macros are disabled!
An example of such files in the Personal Macro Workbook – all VBA codes in that workbook are available for you to use whenever you start Excel, regardless of your macro settings.
To view the current trusted locations or add a new one, carry out these steps:
Done! You can now place your workbook with macros in your own trusted location and do not bother about Excel's security settings.
Tips and notes:
On Excel forums, many people ask if it is possible to enable macros programmatically on opening a workbook and disable them before exiting. The immediate answer is "No, it's not possible". Because macro security is critical for the security of Excel, Microsoft designed any VBA code to only be triggered by a user click.
However, when Microsoft closes a door, the user opens a window :) As a workaround, someone suggested a way to force the user to enable macros with a kind of "splash screen" or "instruction sheet". The general idea is as follows:
You write a code that makes all the worksheets but one very hidden (xlSheetVeryHidden). The visible sheet (splash screen) says something like "Please enable macros and re-open the file" or provides more detailed instructions.
If macros are disabled, the user can only see the "Splash Screen" worksheet; all other sheets are very hidden.
If macros are enabled, the code unhides all the sheets, and then makes them very hidden again when the workbook closes.
As already mentioned, Excel's default setting is to disable macros with notification and allow users to enable them manually if they want to. If you'd like to disable all macros silently, without any notification, then choose the corresponding option (the first one) in the Trust Center.
That's how you can enable and disable macros in Excel. I thank you for reading and hope to see you on our blog next week!
Table of contents