Excel Macro Security
Excel macro security protects your
computer against viruses that may be passed to your computer via Excel Macros.
Macro security has changed
significantly between Excel 2003 and Excel 2007. Therefore, this topic is described
separately for recent and older versions of Excel:
Macro Security in Current Versions of
Excel (2007 & later):
If you want to run macros in current
versions of Excel, you need to save your Excel file as a macro-enabled
workbook. Excel recognizes macro-enabled workbooks from the file extension
.xlsm (rather than the usual .xlsx extension).
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 need to save it with the xlsm extension..
To do
this, select Save As from the 'File' tab of the
Excel ribbon. This will bring up the 'Save As' dialog box. Within the dialog
box, change the 'Save As Type' to "Excel Macro-Enabled Workbook"
(see right).
The different Excel file extensions
make it clear when a workbook contains macros, so this in itself is a useful
security measure. However, Excel also has optional macro security settings,
which are controlled via the options menu.
The four macro security settings are
listed in the following table:
'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 pop-up window will
warn you that the macros exist and have been disabled.
|
'Disable
all macros
except digitally signed macros' |
-
|
This setting 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. |
'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. |
If you
Choose the second setting, 'Disable all macros with notification' you
are provided with an option, when you open the file, to allow the macros to
run. This option is presented to you in a yellow band at the top of your
spreadsheet, as shown below:
Therefore, you just need to click on
this button if you want to allow the macros to run.
Accessing the Excel Macro Security
Settings
If you want to view or alter the
Excel Macro Security Setting in Excel 2007, 2010 or 2013:
In Excel 2007:
·
Select the main Excel menu (by
selecting the Excel Logo on the top left of the spreadsheet), and from the
bottom right of this menu, select Excel Options, to bring
up the 'Excel Options' dialog box;
·
From the 'Excel Options' dialog
box, select the 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.
|
In Excel 2010 or 2013:
·
Select the File tab,
and from this, select Options, to bring up the 'Excel Options'
dialog box;
·
From the 'Excel Options' dialog
box, select the 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.
|
Note: when you change your Excel macro security setting, you will
need to close down and re-start Excel for the new setting to take effect.
Trusted Locations in Current Versions
of Excel (2007 & later)
Current versions of Excel (Excel 2007
and later) have trusted locations, which are directories on your computer that
Excel 'trusts'. Therefore, Excel omits 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 are listed in the Trusted
Locations option setting in your Excel Workbook. This can be accessed
by the following steps:
In Excel 2007:
·
Select the main Excel menu (by
selecting the Excel Logo on the top left of the spreadsheet), and from the
bottom right of this menu, select Excel Options;
·
From the 'Excel Options' dialog box
that pops up, select the Trust Center Option, and from
within this, click on the Trust Center Settings...button;
·
Select the Trusted
Locations option from the left hand menu.
|
In Excel 2010 or Excel 2013:
·
Select the File tab,
and from this, select Options;
·
From the 'Excel Options' dialog box
that pops up, select the 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.
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:
'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 pop-up 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.
Once you have changed a macro
security setting in Excel 2003, you will need to close and restart Excel for
this change to take effect.
No comments:
Post a Comment