21- Working with macros

(Open Exercise file) Back To Excel Index Page Back To Edmond Home Page

211- Why use macros? Play Video

While there are a number of features and tools that can be used to optimize and speed up workflow, there are still series of tasks and commands that many users will find very repetitive and time consuming. However, it is possible to create a custom macro that will execute such a sequence of commands on your behalf with just one click of a button. In the steps below, we’ll show how easy it is to construct a standard macro in Excel.

Step 1: Make sure the Developer tab is shown on the Excel ribbon. If it is not, you can add it by clicking on the Office button and then selecting Excel Options.

In the Excel Options window, select Popular from the list of categories in the left hand panel. Put a check in the box next to Show Developer tab in the Ribbon.

Click the OK button to return to Excel. The Developer tab will now be present on the ribbon.

212- Creating a macro Play Video

Record a macro:

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.

1. On the Developer tab, in the Code group, click Record Macro.

2. In the Macro name box, enter a name for the macro.

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.

3. To assign a CTRL combination shortcut key to run the macro, in the Shortcut key box, type any lowercase letter or uppercase letter that you want to use.

4. The shortcut key will override any equivalent default Excel shortcut key while the workbook that contains the macro is open. For a list of CTRL combination shortcut keys that are already assigned in Excel.

5. In the Store macro in list, select the workbook in which you want to store the macro.

6. To include a description of the macro, in the Description box, type the text that you want.

7. Click OK to start recording.

8. Perform the actions that you want to record.

9. On the Developer tab, in the Code group, click Stop Recording.

10. You can also click Stop Recording, on the left side of the status bar.

213- Macro security Play Video

Change macro security settings:

You can change macro security settings in the Trust Center, unless a system administrator in your organization has changed the default settings to prevent you from changing the settings.

1) On the Developer tab, in the Code group, click Macro Security.

2) In the Macro Settings category, under Macro Settings, click the option that you want.

3) You can also access the Trust Center in the Excel Options dialog box. Click the Microsoft Office Button , and then click Excel Options. In the Trust Center category, click Trust Center Settings, and then click the Macro Settings category.

Macro security settings and their effects:

The following list summarizes the various macro security settings.

Disable all macros without notification: Click this option if you don't trust macros. All macros in documents and security alerts about macros are disabled. If there are documents that contain unsigned macros that you do trust, you can put those documents into a trusted location. Documents in trusted locations are allowed to run without being checked by the Trust Center security system.

Disable all macros with notification: This is the default setting. Click this option if you want macros to be disabled, but you want to get security alerts if there are macros present. This way, you can choose when to enable those macros on a case by case basis.

Disable all macros except digitally signed macros: This setting is the same as the Disable all macros with notification option, except that if the macro is digitally signed by a trusted publisher, the macro can run if you have already trusted the publisher. If you have not trusted the publisher, you are notified. That way, you can choose to enable those signed macros or trust the publisher. All unsigned macros are disabled without notification.

Enable all macros (not recommended, potentially dangerous code can run): Click this option to allow all macros to run. Using this setting makes your computer vulnerable to potentially malicious code and is not recommended.

Trust access to the VBA project object model:  This setting is for developers and is used to deliberately lock out or allow programmatic access to the VBA object model from any Automation client. In other words, it provides a security option for code that is written to automate an Office program and programmatically manipulate the Microsoft Visual Basic for Applications (VBA) environment and object model. This is a per user and per application setting, and denies access by default. This security option makes it more difficult for unauthorized programs to build "self-replicating" code that can harm end-user systems. For any Automation client to be able to access the VBA object model programmatically, the user running the code must explicitly grant access. To turn on access, select the check box.

Back To Top Back To Excel Index Page (Open Exercise file)

Back To Edmond Home Page

Copyright © Edmond Hakmeh 2009. All Rights Reserved