Excel macro security protects your computer against viruses that may be passed to your computer via programming in Excel Macros.
Macro security in Excel 2003
In Microsoft Office 2003, there are 4 levels of Excel macro security, which are controlled by options in the Excel menu. These are each described below:
-
High/Very High
These 2 settings only allow macros from trusted sources to run. All other macros do not run. When you open a new Excel workbook, you are not alerted to the fact that it contains macros, so you may not be aware that this is the reason a workbook does not work as expected.
-
Medium
If there are macros in a workbook, this setting causes a popup to be displayed as the workbook is being opened, asking if you wish to allow macros to be run or not.
-
Low
This setting allows all macros to run. When you open a new Excel workbook, you are not alerted to the fact that it contains macros and may not be aware of macros running while you have the file open. Therefore, if you want to run a macro in Excel 2003, the Excel Macro Security Setting needs to be set to Low or Medium.
In order to view or alter the Macro Security Setting in Excel 2003:
- Select Tools> Macro> Security…
- Select one of the security levels and click OK
Macro security in Excel 2007 and 2010
If you want to run macros in Excel 2007 or Excel 2010, you need to save your Excel file as a macro-enabled workbook. Excel recognises macro-enabled workbooks from the file extension xlsm (rather than the usual xlsx for Excel 2007 and 2010 files).
Therefore, if you add a macro to a standard Excel Workbook, and want to be able to run this macro whenever you access the workbook, you will have to save it with the xlsm extension. To do this, select Save As from the menu and then change the type to “Excel Macro-enabled Workbook”.
The different Excel file types provided by Excel 2007 and Excel 2010 make it clear when workbook contains macros, so this in itself is a useful security measure. However, as in previous versions, Microsoft Excel 2007 and Excel 2010, also have optional macro security settings, which are controlled via the options menu. These are:
-
Disable all macros without notification
This setting does not allow any macros to run. When you open a new Excel workbook, you are not alerted to the fact that it contains macros, so you may not be aware that this is the reason a workbook does not work as expected.
-
Disable all macros with notification
This setting prevents macros from running. However, if there are macros in a workbook, a popup is displayed, to warn you that the macros exist and have been disabled.
-
Disable all macros except digitally signed macros
This setting only allows macros from trusted sources to run. All other macros do not run. When you open a new Excel workbook, you are not alerted to the fact that it contains macros, so you may not be aware that this is the reason a workbook does not work as expected.
-
Enable all macros
This setting allows all macros to run. When you open a new Excel workbook, you are not alerted to the fact that it contains macros and may not be aware of macros running while you have the file open. Therefore, if you wish to enable Macros to run from any location on your computer, you need to ensure your Excel Macro Security Setting is set to ‘Enable all macros‘. However, this is not advised, as you may receive Excel Workbooks containing malicious code, that could run unnoticed. The solution is to store Excel files containing Macros that you trust, in specific directories in your computer, known as Trusted Locations.
In order to view or alter the Excel Macro Security Setting in Excel 2007 or Excel 2010 :
Macro Security Settings in Excel 2007
- Select the main Excel menu (by selecting the Excel Logo on the top left of the spread sheet), and from the bottom right of this menu, select Excel Options
- From the window that pops up, select Trust Center Option and from within this, click on the Trust Center Settings… button
- From within the Macro Settings option, select one of the settings and click OK
Macro Security Settings in Excel 2010
- Select the File tab, and from this, select Options
- From the window that pops up, select Trust Center Option and from within this, click on the Trust Center Settings… button
- From within the Macro Settings option, select one of the settings and click OK
N.B.
That when you change your Excel macro security setting, you will need to close down and restart Excel for the new setting to take effect
Trusted Locations in Excel 2007/2010
Excel 2007 and Excel 2010 have trusted locations, which are directories on your computer that you want Excel to ‘trusts’, and therefore omit the usual Macro checks when opening files that are stored in these locations. This means that, if an Excel file is placed in a trusted location, the Macros in this file will be enabled, regardless of the Macro Security Setting.
Microsoft has defined some default trusted locations, which include the following locations on your computer drive:
- Program Files\Microsoft Office\Templates
- Program Files\Microsoft Office\Office12\Startup
- Program Files\Microsoft Office\Office12\Library
These locations are listed in the Trusted Locations option setting in your Excel Workbook, which can be accessed by the following steps:
Trusted Locations in Excel 2007
- Select the main Excel menu (by selecting the Excel Logo on the top left of the spread sheet), and from the bottom right of this menu, select Excel Options
- From the window that pops up, select Trust Center Option and from within this, click on the Trust Center Settings… button
- Select the Trusted Locations option from the left hand menu
Trusted Locations in Excel 2010
- Select the File tab, and from this, select Options
- From the window that pops up, select Trust Center Option and from within this, click on the Trust Center Settings… button
- Select the Trusted Locations option from the left hand menu
If you want to place your Excel Workbook in another location, and still allow Macros to work, you can define your own trusted locations. To do this:
- From within the Trusted Locations option, click on the Add new location… button
- Find the directory that you wish to make a trusted location, and click OK
Warning
It is not advised that you make a large part of your drive, such as the whole of your ‘My Documents’ folder into a trusted location, as this puts you at risk of mistakenly allowing macros from untrusted sources.
Please do not hesitate to contact us, if you are having trouble viewing or accessing this article.
Copyright© 2016 AMT Training
