How to Enable Macros in Excel?

H

Thinking about How to enable Macros in Excel? We all know that Microsoft Excel is a versatile piece of software that can be used for a variety of tasks such as text wrapping, organizing, formatting, cell splitting, and formula-based data calculation. Let us now delve deeper into Macros.

One of the most powerful but seldom used functions of Excel is the Macros. It enables you to create automated tasks and custom logic. Macros help to save time on predictable, repetitive tasks and standardize document formats – many times without writing complicated code.

If you regularly work with spreadsheets or database systems, it can become extremely tedious to enter the same formula over and over again. It gets more arduous when it is a multi-stage process. Macros are primarily used to automate repetitive tasks by mapping a sequence of keystrokes or recording a sequence of commands to perform a specific activity.

Let us dive a little deeper into Excel Macros now. 

What is an Excel Macro?

Microsoft Windows owners have been working with Excel for years to create budgets, filter data, and do a whole host of other activities. Anyone familiar with the program will appreciate the various functions it provides. But at the same time, users who work with Excel continuously start to develop an aversion to routine tasks or repetitive actions that they are forced to do.

It is here that Excel Macros come into the picture. It automatically executes pre-defined routine commands or simply adds new functions to the spreadsheet or algorithms for data analysis. 

Put simply, a macro is a Visual Basic for Applications (VBA) code saved inside a document.

To create macros, there’s an integrated macro-recording tool that uses the Visual Basic for Application (VBA) script language. Even though macros are written in VBA, there is no need for any programming knowledge as Excel can record simple macros and offer assistance during creation.

Why are Macros Sometimes Harmful?

Most people assume that an Excel function designed to automate tasks would be fairly harmless. But this is far from the truth because malicious VBA code (to create macros) can sometimes be embedded in Excel documents and distributed online. They can cause great harm to your system.

For example, macros can use the VBA SHELL command to run arbitrary commands and programs or use the VBA KILL command to delete files on your hard drive.

After a malicious macro is loaded into an Office application like Excel (through an infected document), it can use features like “AutoExec” to automatically start Excel or “AutoOpen” to automatically run whenever you open a document. This way, the macro virus can integrate itself into Excel, infecting future documents.

Always be cautious when downloading an Office document from the web and never open any documents from unreliable sources.

How does Microsoft Office Protect Against Macros-Based Viruses?

Excel’s default setting is to disable all macros with notification. Only macros signed with a trusted certificate can run. From Office 2007, files with macros are much easier to detect. By default, standard Office documents are saved with the “x” suffix. For example, .docx, .xlsx, and .pptx. Documents with these file extensions are not allowed to contain macros. Additionally, documents with a file extension ending with “m” — that’s .docm, .xlsm, and .pptm — are allowed to contain macros.

Modern versions of Microsoft Office are even more restrictive. In order to keep users safe, MS Office on Windows 10 is configured, by default, to block macros in documents received from the Internet. These could be – 

  • Documents downloaded from websites or storage providers (like OneDrive, Google Drive, or Dropbox)
  • Documents attached to emails that were received from outside the organization
  • Documents opened from public shares hosted on the internet (such as files downloaded from file-sharing sites). 

These features are only available if you have a Microsoft 365 subscription and an updated version of Windows 10.

Also Read
How to insert Tick mark in Excel
How to add drop down in Excel

How do you Avoid the Dangers of Macros?

You can generally modify the macro security settings of Excel and determine the following:

  • Which macros can run
  • Under what circumstances can macros run in your document

In any case, please note the following:

  • You will not be able to change the macro security settings if an administrator in your organization has changed the default settings with the purpose of preventing modifications. If this is the case, get in touch with this administrator.
  • Any changes that you make to the macro security settings in Excel are only applicable to Excel. They don’t apply to other Microsoft Office applications.

Just follow one simple rule: enable only safe macros or the ones that you’ve written or recorded yourself.

How to Enable Macros in Excel

How do you Enable Macros for Individual Workbooks?

There are two ways to switch on macros for a certain workbook:

Directly From the Workbook

When you open an Excel file that has macros, a yellow message bar appears with a shield icon and an ‘Enable Content’ button.

To use this particular method, first, you need to see whether the Developer tab has been added. If it is added, then you can directly start from the Developer tab.

If not, first add the Developer tab in the ribbon and then start. Here are the steps –

  • Go to the File tab > Click on Options.
  • Right-click anywhere on the ribbon.
  • A dialog box will appear. Click on the ‘Customize the Ribbon’ option in the left panel menu of the dialog box.
  • On the right-hand side of the panel, click on Developer under the ‘Main tabs’ and click the OK button. This will add the Developer Tab to the Excel ribbon.
  • Now click on the Developer Tab and go to the Macro Security option under the code group.
  • In the Trust Centre screen, click on the Macro Settings in the left panel. To enable macros, select the radio button corresponding to ‘Enable all macros (not recommended; potentially dangerous code can run)’ option under the Macro Settings section.

However, if ‘Disable all macros with the notification’ is selected, and an Excel document that contains macros is re-opened, the yellow message bar with a shield icon and Enable content button appears on top of the excel sheet.

Another way is when you open the Excel file containing macros with your Visual Basic Editor. In that case, the Microsoft Excel Security Notice will be displayed as follows:

  • Now, if you trust the source of the file and know that all the macros are secure, click on Enable Content or Enable Macros button. This will turn on the macros and make the file a trusted document. The security warning will not appear when you open the workbook next time.)
  • However, 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.

Also Read
How to freeze rows and columns in Excel
How to compare two columns in Excel

Enable Macros in the Backstage View

Another way to enable macros is via the Office Backstage view. Here’s how to do it:

  • Click the File tab, and then click Info on the left menu
  • In the Security Warning area, click Enable Content > Enable All Content.

This will make your workbook become a trusted document.

How to Enable Macros for one Session?

In some situations, you might want to enable macros only for a single time. For example, if you received an Excel file with macros or a VBA code that you’d like to investigate, but you do not wish to make this file a trusted document.

In such cases, use the following instructions to enable macros for the duration that the file is open. When you close the file and then reopen it, the warning appears again.

  • Click the File tab > Info
  • In the Security Warning area, click Enable Content > Advanced Options
  • In the Microsoft Office Security Options dialog box, select Enable content for this session, and click OK.

How to Enable Macros in all Workbooks via the Trust Center?

Microsoft Excel determines whether to allow or disallow VBA codes to run based on the macro setting selected in the Trust Center (the place where you configure all the security settings for Excel).

Trusted documents are files that have been marked as trusted by enabling active content in them. Active content (macros, ActiveX controls, data connections, and so on) opens without the message bar warning after you mark the file as trusted.

For a trusted document, there is no prompt when you open the file, even if new active content was added or changes were made to the existing active content. However, the prompt does appear if the file has been moved since you last trusted the file.

After a document is trusted, it does not open in Protected View. Therefore, you should trust documents only if you trust the source of the file.

However, if your device is managed by your work, the system administrator might prevent anyone from changing settings.

To have macros enabled in all Excel workbooks by default, this is what you need to do:

  •  Click the File tab, and then click Options at the very bottom of the left bar.
  • On the left-side pane, select Trust Center, and then click on the Trust Center Settings.
  • In the Trust Center dialog box, click Macro Settings on the left, select Enable all macros and click OK.

You can also access the Trust Center in the Developer tab. 

  • On the Developer tab, in the Code group, click on Macro Security.
  •  In the Macro Settings category, under Macro Settings, click the option that you want.

Note:

  • The option that has been set via the Trust Center becomes the new default macro setting and applies globally to all your Excel files.
  • If you want to enable macros for only specific workbooks, save them in a trusted location instead.
  • Enabling all macros in all workbooks will make your computer vulnerable to potentially malicious codes.
  • Any changes that you make in the Macro Settings category in Excel apply only to Excel and do not affect any other Microsoft Office program.

Also Read
How to change Date Format in Excel
How to remove Blank rows in Excel

How to Enable Macros Permanently only in Certain Trusted Locations?

This is a safer alternative to enabling 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 will open 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!

A good example of such files is in the Personal Macro Workbook in Excel. – all VBA codes in that workbook are available for you to use whenever you start Excel, regardless of your macro settings. This is a special file named Personal.xlsb in Excel 2007 – 2019 or Personal.xls in earlier versions. The Personal.xlsb file is stored in the XLSTART folder in the following location –

C:\Users\User Name\AppData\Roaming\Microsoft\Excel\XLSTART

To reach the XLSTART folder in Windows Explorer, check the Hidden items box on the View tab.

To view the current trusted locations or add a new one, carry out these steps:

  • Click File > Options.
  • On the left-hand pane, select Trust Center, and then click Trust Center Settings.
  • In the Trust Center dialog box, select Trusted Locations on the left side. You will see a list of the default trusted locations.
  • These locations are important for the correct work of Excel add-ins, macros, and templates and should not be changed. 
  • To set up your trusted location, click Add new location.

In the Microsoft Office Trusted Locations dialog box, do the following:

  • Click the Browse button to navigate to the folder that you want to make a trusted location.
  • If you wish any subfolder of the selected folder to be trusted too, check the option ‘Subfolders of this location are also trusted’.
  • Type a short note in the Description field.
  • Click OK.

Following are trusted locations, by default, for macros to run from.

  • Program Files (x86)\Microsoft Office\Templates\
  • AppData\Roaming\Microsoft\Excel\XLSTART\
  • Program Files (x86)\Microsoft Office\Office14\XLSTART\
  • AppData\Roaming\Microsoft\Templates\
  • Program Files (x86)\Microsoft Office\Office14\STARTUP\
  • C:\Program Files (x86)\Microsoft Office\Office14\Library\

You can add, remove or modify trusted locations from the Trusted Locations screen.

Note:

  • Please be very careful when choosing a trusted location because Excel automatically enables all macros in all workbooks that are stored in trusted locations. This will create a vulnerability in your security system.
  • Never make any temporary folder a trusted source. Also, be cautious with the Documents folder. Rather create a subfolder and designate it as a trusted location.
  • If you’ve mistakenly added a certain folder to the list of trusted locations, select it and click the Remove button.
  • There are situations when you can’t make a file a trusted document. Active content can be disabled if:
    • The system administrator has set a security policy to disable a certain type of active content for your organization (for example, Disable all macros without notification
    • You have changed your Trust Centre settings for one or more active content types.

How to Enable Macros for Windows 10?

Windows 10 has been designed to be more secure than other versions of Windows. One way Microsoft has made this operating system more secure is by restricting what software is allowed to run in it.

Under Windows 10, when an Excel workbook is downloaded from the internet that contains macros, the operating system places a warning on the file. It disables the macros from running until the flag is removed. Macros are disabled by default, but you can easily enable them. When you open an Excel file on Windows 10, you will see a warning message similar to the one below indicating that macros have been disabled.

If you open an Excel file that contains macros, Microsoft Office will send a security warning via the message Bar at the top of the document. There is no way to enable this content from inside Excel. You must close the file and modify it using the following steps before being able to work on the spreadsheet.

  • Open the Excel file in Windows Explorer and right-click it. 
  • Choose “Properties”.
  • Notice the security message and click “Unblock” to allow macros to be enabled.
  • Open the file in Excel and enable macros. Now use the file as normal.

Note: You will need to follow this process for every Excel workbook.

How to Enable Macros for Excel 2010, 2013, and 2016?

• In “options” under the File tab, click “Trust Center.”

• In “Trust Center settings,” click “Macro settings” and choose any of the four options depending on the extent of permission to be granted. Click “Ok.”

How to Enable Macros in Excel 2007?

  • In Excel 2007, click on Options as shown in ① in an Excel file. 
  • This will open the Security Alert dialog box.
  • Now enable this content as shown by ② 
xlf-security-options-2007

Excel Macro Settings Explained

We will briefly explain all macro settings in the Trust Center to help you make an informed decision:

  • Disable all macros without notification – All macros are disabled; no warning will show up. You won’t be able to run any macros except the ones stored in trusted locations.
  • Disable all macros with notification (default) – Macros are disabled, but you can enable them on a case-by-case basis.
  • Disable all macros except digitally signed macros – Unsigned macros are disabled with notifications. Macros digitally signed with a special certificate by a trusted publisher are allowed to run.
     
  • Enable all macros (not recommended) – All macros are allowed to run, including potentially malicious codes.
  • Trust access to the VBA project object model – This setting controls programmatic access to the object model of Visual Basic for Applications. It’s disabled by default to prevent unauthorized programs from changing your macros or building self-replicating harmful codes.

Things to Remember

The following points must be remembered while enabling macros:

  • The file containing the VBA code must be saved as a “macro-enabled workbook.” Saving the file with some other extension does not save the VBA code and thus, does not enable the macros.
  • The option “enable all macros” runs all macros without any further warning or consent. Since the macros of both trusted and non-trusted sources are enabled, it might become dangerous.
  • The option “Disable all macros without any notification” does not inform the reason behind why macros are not running. Hence, select “Disable all macros with notification” to view the warnings.
  • It is safe to enable only those macros that come from trusted sources or have been self-recorded by the user. All other macros that come from unknown and non-trusted sources pose an inherent security risk.

Conclusion

Microsoft Excel is a component of the Microsoft Office suite of software. It is an electronic spreadsheet that allows us to add numerous rows and columns and is used for data organization, graphically representing data, and performing various calculations like addition, multiplication, average determination and more.

By now, you probably have a good understanding of what macro is and what macro security settings are most appropriate for a particular situation. Additionally, you’ve also seen how you can enable macros in Excel in different situations. The different methods highlighted in this post should be able to cover most of the situations that you find in your day-to-day work.

But remember the mantra, though enabling macros can be beneficial for the user, running unknown macros can be equally harmful.

Easysheet is a blog that features incredible Excel hacks, tips, and tricks. Our goal is to make Excel accessible to beginners and provide them with remedies using EasySheet.

We are the best resource for anyone in need of assistance with Microsoft Office products, such as Excel, Word, PowerPoint, Publisher, and more.

About the author

Gaurav Singh Rawat

Add comment