Last updated on April 19, 2023 By Victor Chan
Macros are a powerful feature in Excel that can help you automate repetitive tasks, save time, and improve your productivity. But macros can also pose a security risk if they come from untrusted sources or contain malicious code. That's why Excel disables macros by default and warns you before running them. You must manually enable macros to use them.
In this article, you'll learn how to enable and disable macros in Excel for Windows and Mac, how to adjust the macro security settings, and how to create a trusted location for your macro-enabled workbooks. You'll also learn some best practices for working with macros safely and effectively.
A macro is a series of commands or instructions that can be executed with a single click or keystroke. Macros can perform various actions, such as formatting cells, inserting formulas, copying data, creating charts, and more.
You can create VBA macros in Excel by using the Macro Recorder or by writing VBA (Visual Basic for Applications) code in the Visual Basic Editor. You can also import macros from other sources, such as online forums, blogs, or books.
Macros are stored in macro-enabled workbooks, which have the .xlsm file extension. You can also store VBA macros in a personal macro workbook, which is a hidden workbook that loads every time you open Excel. This way, you can access your VBA macros from any workbook.
Why are VBA macros disabled in Excel?
Macros are disabled in Excel because they can potentially run harmful code on your computer or network. For example, a malicious macro could delete or corrupt your files, steal your data, install malware, or access your personal information.
Excel uses a security feature called the Trust Center to protect you from unsafe VBA macros. The Trust Center allows you to control how VBA macros are handled in Excel and what level of security you want to apply.
By default, Excel disables all VBA macros with notification. This means that when you open a workbook that contains VBA macros, you'll see a yellow security warning bar at the top of the sheet. You can choose to enable or disable VBA macros for that workbook by clicking on the warning bar.
If you don't see the warning bar, it means that either there are no VBA macros in the workbook or that the workbook is already trusted by Excel.
There are two ways to enable and disable macros in Excel for Windows: through the security warning bar or through the Backstage view.
The easiest way to enable and disable macros is via the security warning bar that appears when you open a macro-enabled workbook. Here's how:
Another way to enable and disable macros is via the Backstage view. Here's how:
Just a heads-up about VBA macros in Office apps. They're sometimes used by not-so-nice people to spread malware and ransomware, so Microsoft decided to make Office safer. Currently this only affects Office on devices running Windows, and for these apps: Access, Excel, PowerPoint, Vision, and Word.
They changed how Office for Windows apps handle macros in files from the internet. From 2022 or 2023 (depending on which channel you use), if you open a file that has macros and comes from the internet (like an email attachment), you'll see a friendly message like this:
You can learn the full details from Microsoft here: Internet Macros Blocked
The process of enabling and disabling macros in Excel for Mac is similar to Windows, but with some minor differences.
When you open a macro-enabled workbook for Mac, you'll see a pop-up dialog that warns you that the file contains macros. You can choose to enable or disable macros for that workbook by clicking on the dialog.
When you open a macro-enabled workbook for Mac, you'll see a pop-up dialog that warns you that the file contains macros. You can choose to enable or disable macros for that workbook by clicking on the dialog.
By default, Excel disables all macros with notification. This means that you'll see a warning message every time you open a workbook that contains macros, and you can choose whether to enable or disable them.
However, you can change this setting to suit your preferences and needs. For example, you can disable all macros without notification, disable all macros except digitally signed ones, or enable all macros (not recommended).
To change macro security settings in Excel, you need to access the Trust Center. The Trust Center is like a hub for managing security and privacy settings in Excel and other Microsoft Office programs.
Here's how to change macro security settings in Excel:
Note: There is one more setting available in the Trust Center, “Trust access to the VBA project object model”.
When this option is enabled, it allows VBA code to interact with and manipulate the VBA project object model. This can be useful for automating tasks such as adding, modifying, or deleting VBA modules, user forms, or references.
However, enabling this setting can also expose your computer to potential security risks, as malicious code could potentially access and modify your VBA projects.
If you’re a VBA beginner, I strongly advise you not to turn this setting on.
A trusted location is a folder that contains files that you trust to be safe. Files in a trusted location are not checked by the Trust Center and do not open in Protected View or Application Guard. All macros in a trusted location are enabled automatically without any warning messages.
You can create your own trusted locations to store your macro-enabled workbooks or workbooks from reliable sources. However, you should be careful when choosing a trusted location and make sure that only you or authorized people can access it. You should also avoid using network locations or removable media as trusted locations, as they can be more vulnerable to attacks.
To create a trusted location for macros in Excel, follow these steps:
You can now save and open your macro-enabled workbooks in the trusted location without any security warnings. Just be careful of what you save there, especially files from an internet source that you don’t trust.
Macros are a useful feature in Excel that can help you automate tasks and enhance your workbooks. However, macros can also pose a security risk if they come from untrusted sources or contain harmful code.
By default Excel disables macros for your protection and you need to enable macros to use them. This applies to Excel for Windows, Excel for Mac, and Excel 365.
To protect yourself from unsafe macros, you should use the Trust Center to control how macros are handled in Excel and what level of security you want to apply. You should also use trusted locations to store your macro-enabled workbooks or workbooks from reliable sources.
By following these tips, you can work with macros safely and effectively in Excel.
Stay safe!
Hi, I'm Victor!
Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?
Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.
That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.
Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!
Recommended Posts
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.
Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!
Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.
Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.
RECOMMENDED READING
Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.