What is a macro ?

Basic Description

The purpose of a macro is to automate frequently used tasks. Although some macros are simply a recording of your keystrokes or mouse clicks. When you record a macro, all steps that are needed to complete the actions that you want to record are recorded by the macro recorder. Navigation on the Ribbon is not included in the recorded step. The first character of the macro name must be a letter. Following characters can be letters, numbers, or underscore characters. Spaces are not allowed in a macro name; an underscore character works well as a word separator. If you use a macro name that is also a cell reference, you may get an error message that the macro name is not valid.

You are not limited to the Built-In Functions that are provided in Excel. You can also invent your own functions by writing Excel macros to perform specific tasks. You can even add your own macros to the Excel function menu, so that they are available to you in the same way as Excel’s built-in functions. A macro is a piece of computer code, that is written for Excel, using the Visual Basic for Applications (VBA) programming language. The basics of the VBA programming language are covered in the Excel VBA Tutorial in the next module.

 

Trust Center and Macros

If the Trust Center detects a problem with developer signature or certificate authority (CA), the macro is disabled by default, and the Message Bar appears to notify you of a potentially unsafe macro. If you click Options on the Message Bar, a security dialog box opens, giving you the option to enable the macro. See the next section for how to make a secure decision before you click an option.

macro warning

 

Setting up Excel to Allow Macros

Excel has built-in security, to protect against viruses that may be passed to your computer via Excel Macros. If you want to run macros in your Excel workbook, you may need to ensure that you have the correct security settings. If you want to run macros in Excel 2007, Excel 2010 or Excel 2013, 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 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 the latest versions of Excel make it clear when 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.

 

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• 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

In Excel 2010 or 2013:• 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

Note that 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

Excel 2007, Excel 2010 & Excel 2013 have trusted locations, which are directories on your computer that Excel ‘trusts’, and therefore 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 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

In Excel 2010 or Excel 2013:•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.

 

Macro Security

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 Option Description
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 is displayed, to 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.

 

 

Limitations of Macros

Although the Excel macro recording feature is a very simple way of creating VBA code, it can only be used for very basic macros. This is because it cannot make use of any of the VBA features that store information in memory, such as : Defined Constants, Variables and Arrays, If Statements, Loops, Calls to Built-In Functions or Other Procedures.

Also, the recording feature can only produce subroutines (not functions), as it cannot return a value. These subroutines cannot be passed any arguments, although they are able to identify the current active cells, ranges or worksheets, and values stored in the cells of the workbook. It should also be noted that the code generated is not always the most efficient code possible for the required actions.

While Excel’s automatically generated VBA code is fine for simple macros, if you want to produce more complex macros, you will need to learn to write VBA code. However the Excel Macro Recording feature is an excellent tool to start you with code that you can adapt or insert into your more complex macros.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

code

clearPost Comment