Introducing Macros

After you have worked with your Excel 2007 documents for awhile, you will probably discover some series of actions you perform repeatedly. Although many of these actions (such as saving your changes and printing) can be accomplished quickly, some sequences involve many steps and take time to accomplish by hand. For example, you might want to highlight a number of cells in a worksheet to emphasize an aspect of your data. Instead of highlighting the cells by hand every time you present your findings, you can create a macro, or series of automated actions, to do the highlighting for you.

Macro Security in Excel 2007

It's possible for unscrupulous programmers to write viruses and other harmful programs using the Microsoft Visual Basic for Applications (VBA) programming language, so you need to be sure that you don't run macros from unknown sources. In versions of Office Excel prior to Excel 2007, you could define macro security levels to determine which macros, if any, your workbooks would be allowed to run, but there was no workbook type in which all macros were disallowed. Excel 2007 has several file types you can use to control whether the workbook will allow macros to be run. The following table summarizes the macro-related file types.




Regular Excel 2007 workbook; macros are disabled


Regular Excel 2007 workbook; macros are enabled


Excel 2007 template workbook; macros are disabled


Excel 2007 template workbook; macros are enabled

When you open a macro-enabled workbook, the Excel 2007 program-level security settings might prevent the workbook from running the macro code. When that happens, Excel 2007 displays a security warning on the Message Bar.

Clicking the Options button displays the Microsoft Office Security Options dialog box.

If you are expecting a workbook that contains macros and recognize the source of the workbook, you can select the Enable This Content option button and then click OK to enable the workbook macros. Please take the time to verify the workbook's source and whether you expected the workbook to contain macros before enabling the content.

You can change your program-level security settings to make them more or less restrictive; to do so, click the Microsoft Office Button, click the Excel Options button, and then, in the Excel Options dialog box, click the Trust Center category. On the page that appears, click the Trust Center Settings button to display a dialog box of the same name.

The Excel 2007 default macro security level is Disable All Macros With Notification, which means that Excel 2007 displays a warning on the Message Bar, but you can enable the macros by clicking the Options button and selecting the Enable This Content option button. Selecting the Disable All Macros Without Notification option does exactly what the label says. If Consolidated Messenger's company policy is to disallow all macros in all Excel 2007 workbooks, you would select the Disable All Macros Without Notification option.


Because it is possible to write macros that act as viruses, potentially causing harm to your computer and spreading copies of themselves to other computers, you should never choose the Enable All Macros security setting, even if you have virus-checking software installed on your computer.

Examining Macros

The best way to get an idea of how macros work is to examine an existing macro. To do that, display the View tab. In the Macros group, click the Macros button and then click View Macros.


In the Macro dialog box, you can display the macros available in other workbooks by clicking the Macros In box and selecting a workbook by name or by selecting All Open Workbooks to display every macro in any open workbook. If you select either of those choices, the macro names displayed include the name of the workbook in which the macro is stored. Clicking This Workbook displays the macros in the active workbook.

The Macro dialog box has a list of macros in your workbook. To view the code behind a macro, you click the macro's name and then click Edit to open the Microsoft Visual Basic Editor.

Excel 2007 macros are recorded using VBA. The preceding graphic shows the code for a macro that highlights the cell range C4:C7 and changes the cells' formatting to bold. After introductory information about the macro (its name and when it was created), the first line of the macro identifies the cell range to be selected (in this case, cells C4:C7). After the macros selects the cells, the next line of the macro changes the formatting of the selected cells to bold, which has the same result as clicking a cell and then clicking the Bold button in the Font group on the Home tab.

To see how the macro works, you can open the Macro dialog box, click the name of the macro you want to examine, and then click Step Into. The Microsoft Visual Basic Editor appears, with a highlight around the instruction that will be executed next.

To execute an instruction, press F8. The highlight moves to the next instruction, and your worksheet then changes to reflect the action that resulted from executing the preceding instruction.

You can run a macro without stopping after each instruction by opening the Macro dialog box, clicking the macro to run, and then clicking Run. You'll usually run the macro this way; after all, the point of using macros is to save time.

In this exercise, you examine a macro in the Visual Basic Editor, move through the first part of the macro one step at a time, and then run the entire macro without stopping.

USE the VolumeHighlights workbook in the practice file folder for this topic. This practice file is located in the My Documents\Microsoft Press\Excel SBS\Macros folder.

BE SURE TO start Excel 2007 before beginning this exercise.

OPEN the VolumeHighlights workbook.


On the Message Bar, click Options.

The Microsoft Office Security Options dialog box appears.


Select the Enable this content option button.


Click OK.

The security warning disappears, and macros are enabled.


On the View tab, in the Macros group, click the Macros button down arrow and then click View Macros.

The Macro dialog box appears.


Click the HighlightSouthern macro and then click Edit.

The Visual Basic Editor opens, with the code for the HighlightSouthern macro displayed in the Module1 (Code) window.


Click the Visual Basic Editor Close button.

The Visual Basic Editor closes, and VolumeHighlights.xlsm reappears.


On the View tab, in the Macros group, click the Macros button down arrow and then click View Macros.

The Macro dialog box appears.


Click the HighlightSouthern macro and then click Step Into.

The macro appears in the Visual Basic Editor, with the first macro instruction highlighted.


Press the F8 key.

Excel 2007 highlights the next instruction.


Press the F8 key.

The macro selects the Atlantic row in the table.


Press the F8 key twice.

The macro changes the Atlantic row's text color to red.


Click the Visual Basic Editor Close button.

A warning dialog box appears, indicating that closing the Visual Basic Editor will stop the debugger.


Click OK.

The Visual Basic Editor closes.


On the View tab, in the Macros group, click the Macros button down arrow and then click View Macros.

The Macro dialog box appears.


Click the HighlightSouthern macro.


Click Run.

The Macro dialog box disappears, and Excel 2007 runs the entire macro.


On the Quick Access Toolbar, click the Save button.

Excel 2007 saves your work.

CLOSE the VolumeHighlights workbook.

MicrosoftR Office ExcelR 2007 Step by Step
MicrosoftВ® Office ExcelВ® 2007 Step by Step (Step By Step (Microsoft))
ISBN: 073562304X
EAN: 2147483647
Year: 2004
Pages: 143
Authors: Curtis Frye

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: