Chapter 12: Macros

A macro is a recorded set of steps that Excel can repeat with one click or keystroke. If you format a weekly report the same way every Monday, you can record yourself doing it once and play it back from then on. Behind the scenes, Excel writes each step in VBA (Visual Basic for Applications), a programming language. MO-211 doesn't expect you to program. It expects you to record, name, run and make simple edits to macros, to copy them between workbooks, and to manage the security settings that control them.

12.1 Macro security and file formats

12.1.1 The Developer tab

Macro commands are on the Developer tab, which is hidden at first. To show it, choose File › Options › Customize Ribbon, check Developer in the list on the right, and click OK.

The View › Macros button also has Record Macro, View Macros and Use Relative References, so you can record without the Developer tab. A Record Macro button also appears at the left end of the status bar.

12.1.2 Enabling macros in a workbook

Macros can do almost anything Excel can do, including harmful things, so Excel blocks them by default in files you didn't create. When you open a workbook with macros, a yellow Security Warning bar says Macros have been disabled:

• Click Enable Content to enable the macros and mark the file as trusted, so you won't be asked again.
• Or choose File › Info › Enable Content › Advanced Options and select Enable content for this session to enable them only until you close the file.

A file downloaded from the internet or received by email may show a red Security Risk bar instead, with no Enable button. To allow its macros, close it, right-click the file in File Explorer, choose Properties, check Unblock and click OK, then reopen it.

12.1.3 Macro settings

File › Options › Trust Center › Trust Center Settings › Macro Settings sets what Excel does with macros in files that aren't trusted:

Trusted Locations in the Trust Center lists folders whose files always open with macros enabled. Files in trusted locations skip the warning.

12.1.4 Macro-enabled file formats

A regular workbook (.xlsx) can't store macros. If you save a workbook with macros as .xlsx, Excel warns that the macros will be lost. Use one of these formats instead:

Choose the format from Save as type in File › Save As.

12.2 Recording and naming macros

12.2.1 Planning the recording

The recorder captures every step you take, including mistakes and unnecessary clicks. Before you record:

• Know the steps. Practice them once, or write them down.
• Decide where to start. If the macro should always format A1:F1, it doesn't matter which cell is active when you start. If it should format "the row the user is on," you'll need relative references (see below).
• Decide where to store the macro.

12.2.2 Recording

1. Choose Developer › Record Macro (or View › Macros › Record Macro).
2. Fill in the Record Macro dialog box:
   • Macro name: the name of the macro (see the rules below).
   • Shortcut key: optional. Type a letter to run the macro with Ctrl+that letter. Type a capital letter (hold Shift) to use Ctrl+Shift+the letter. A shortcut overrides Excel's own shortcut while the workbook is open, so Ctrl+Shift combinations are safer.
   • Store macro in: This Workbook keeps the macro in the current file. New Workbook puts it in a new file. Personal Macro Workbook saves it in a hidden workbook, PERSONAL.XLSB, that opens whenever Excel does, so the macro is available in every workbook.
   • Description: optional notes about what the macro does.
3. Click OK. The recorder is now running.
4. Do the steps.
5. Choose Developer › Stop Recording (or click the square Stop button on the status bar).

12.2.3 Naming rules

Macro names follow rules much like range names:

• They must start with a letter.
• They can contain letters, numbers and underscores, but no spaces or other punctuation.
• They can't look like a cell reference (such as AB12) or use a word VBA reserves.

FormatReport, Format_Report and AddTotals2 are fine. Format Report and 2Totals are not.

12.2.4 Absolute and relative references

By default the recorder uses absolute references. If you click C5 while recording, the macro always selects C5, no matter which cell was active when you ran it.

With Use Relative References turned on (Developer › Use Relative References, which stays highlighted while it's on), the recorder records movement relative to the starting cell. If you start in A1 and click C5, the macro moves two columns right and four rows down from wherever you run it.

Use relative references for macros that should work on whichever cell or row is selected, such as "format the current row as a total." Turn the setting on before you make the steps that should be relative. You can switch it on and off during a recording.

12.3 Running and editing macros

12.3.1 Running a macro

There are several ways to run a macro:

• Press its shortcut key.
• Choose Developer › Macros (or View › Macros, or Alt+F8), select the macro and click Run.
• Click a button or shape it's assigned to. Insert a shape (Insert › Shapes), right-click it, choose Assign Macro and pick the macro. You can also use a button from Developer › Insert › Button (Form Control).
• Click a Quick Access Toolbar button. Add one from File › Options › Quick Access Toolbar, choosing Macros in the Choose commands from list.

12.3.2 The Macro dialog box

Developer › Macros (Alt+F8) lists the macros in open workbooks. Besides Run:

• Step Into opens the macro in the Visual Basic Editor and runs it one line at a time.
• Edit opens the macro's code in the Visual Basic Editor.
• Delete removes the macro.
• Options changes the macro's shortcut key and description.

The Macros in list chooses whether to show macros from all open workbooks or just one.

12.3.3 The Visual Basic Editor

Click Edit in the Macro dialog box, or press Alt+F11, to open the Visual Basic Editor (VBE). The Project Explorer on the left lists each open workbook as a VBAProject. Recorded macros are stored in Modules, usually Module1. Double-click a module to see its code.

A recorded macro looks something like this:

Sub FormatHeader()
'
' FormatHeader Macro
' Bold, center and fill the heading row.
'
' Keyboard Shortcut: Ctrl+Shift+H
'
    Range("A1:F1").Select
    Selection.Font.Bold = True
    Selection.HorizontalAlignment = xlCenter
    With Selection.Interior
        .Color = 15773696
    End With
End Sub

How to read it:

• Sub FormatHeader() starts the macro, and End Sub ends it. The word after Sub is the macro's name.
• Lines starting with an apostrophe (') are comments. They're notes for people and are ignored when the macro runs. The recorder adds the description and shortcut as comments.
• Each other line is one step. Range("A1:F1").Select selects A1:F1, and the next lines format the selection.

12.3.4 Making simple edits

MO-211 asks for simple edits, such as:

After editing, close the editor (or press Alt+Q) to return to Excel. You don't have to save separately in the editor. Saving the workbook (as .xlsm) saves the code.

12.4 Copying macros between workbooks

A macro stored in one workbook works only while that workbook is open. To make it part of another workbook, copy its module.

12.4.1 Dragging a module

1. Open both workbooks.
2. Press Alt+F11 to open the Visual Basic Editor. If the Project Explorer isn't showing, choose View › Project Explorer (Ctrl+R).
3. Expand the source workbook's project and its Modules folder.
4. Drag the module (such as Module1) onto the destination workbook's project.

A copy of the module, with all its macros, appears in the destination workbook. Save the destination as a macro-enabled workbook.

12.4.2 Copying the code

You can also copy just one macro:

1. In the source module, select everything from Sub through End Sub and copy it (Ctrl+C).
2. In the destination project, choose Insert › Module, or open an existing module.
3. Paste (Ctrl+V).

12.4.3 Exporting and importing

To move a module through a file, right-click it in the Project Explorer and choose Export File, which saves a .bas file. In the other workbook's project, right-click and choose Import File.

12.5 Practice

Sub AddTitle()
' Inserts a title row above the data.
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Weekly Sales Report"
    Selection.Font.Size = 14
    Selection.Font.Bold = True
End Sub