Chapter 1

Recording and Running Macros in the Office Applications

In this chapter, you'll learn the easiest way to get started with Visual Basic for Applications (VBA): recording simple macros using a Macro Recorder that is built into the Office applications. Then you'll see how to run your macros to perform useful tasks.

I'll define the term macro in a moment. For now, just note that by recording macros, you can automate straightforward but tediously repetitive tasks and speed up your regular work. You can also use the Macro Recorder to create VBA code that performs the actions you need and then edit the code to customize it—adding flexibility and power. In fact, VBA is a real powerhouse if you know how to use it. This book shows you how to tap into that power.

In this chapter you will learn to do the following:

• Record a macro
• Assign a macro to a button or keyboard shortcut
• Run a macro
• Delete a macro

What Is VBA and What Can You Do with It?

Visual Basic for Applications is a programming language created by Microsoft that can be built into applications. You use VBA to automate operations in applications that support it. All the main Office applications—Word, Excel, Outlook, Access, and PowerPoint—include VBA, so you can automate operations through most Office applications.

And please don't be put off by the notion that you'll be programming: As you'll see shortly, working with VBA is nearly always quite easy. In fact, quite often you need not actually write any VBA yourself; you can merely record it—letting the Office application write all the VBA “code.” The phrase automate operations in applications is perhaps a bit abstract. VBA allows you to streamline many tasks, avoid burdensome repetition, and improve your efficiency. Here are some examples:

• You can record a macro that automatically carries out a series of actions that you frequently perform. Let's say that you often edit Word documents written by a co-worker, but she sets the zoom level to 100. You prefer a zoom level of 150. All you need to automatically fix this is this VBA code:

  ActiveWindow.ActivePane.View.Zoom.Percentage = 150

  And don't worry, you need not even know these programming terms like ActiveWindow or View.Zoom. When you turn on the Macro Recorder, then perform these actions (clicking View, then clicking Zoom, then setting the percentage), all your actions are translated into the necessary VBA code. You write no code at all.

• You can write code that performs actions a certain number of times and that makes decisions depending on the situation in which it is running. For example, you could write code that takes a series of actions on every presentation that's open in PowerPoint.
• You can have your macros interact with the user by displaying forms, or custom dialog boxes, that enable the user to make choices and specify settings while the macro is running. For example, you might display a set of formatting options—showing captioned controls such as check boxes and option buttons—that the user can select. Then when the user closes the dialog box, your macro takes appropriate actions based on the user's input.
• You can take actions via VBA that you can't take (or take easily) by directly manipulating the user interface. For example, when you're working interactively in most applications, you're limited to working with the active file—the active document in Word, the active workbook in Excel, and so on. By using VBA, you can manipulate files that aren't active.
• You can make one application manipulate another application. For example, you can make Word place a table from a Word document into an Excel worksheet.

The Difference between Visual Basic and Visual Basic for Applications

VBA is based on Visual Basic, a programming language derived from BASIC. BASIC stands for Beginner's All-Purpose Symbolic Instruction Code. BASIC is designed to be user-friendly because it employs recognizable English words (or variations on them) rather than the abstruse and incomprehensible programming terms found in languages like COBOL. In addition to its English-like diction, BASIC's designers endeavored to keep its punctuation and syntax as simple and familiar as possible.

Visual Basic is visual in that it offers efficient shortcuts such as drag-and-drop programming techniques and many graphical elements.

Visual Basic for Applications is a version of Visual Basic tailored to Microsoft Office applications. The set of objects (features and behaviors) available in each application differs because no two applications share the same features and commands.

For example, some VBA objects available in Word are not available in Excel (and vice versa) because some of Word's features, like the Table of Contents generator, are not appropriate in Excel.

However, the large set of primary commands, fundamental structure, and core programming techniques of VBA in Word and VBA in Excel are the same. So you'll find that it's often quite easy to translate your knowledge of VBA in Word to VBA in Excel (or indeed in any VBA-enabled application).

For example, you'd use the Save method (a method is essentially an action that can be carried out) to save a file in Excel VBA, Word VBA, or PowerPoint VBA. What differs is the object involved. In Excel VBA, the command would be ActiveWorkbook.Save, whereas in Word VBA it would be ActiveDocument.Save and in PowerPoint it would be ActivePresentation.Save.

VBA always works with a host application (such as Access or Word). With the exception of some stand-alone programs that are usually best created with Visual Studio Tools for Office, a host application always needs to be open for VBA to run. This means that you can't build standalone applications with VBA the way you can with Visual Basic .NET or Visual Studio Tools for Office (VSTO). If you wish, you can hide the host application from the user so that all they see is the interface (typically user forms) that you give to your VBA procedures. By doing this, you can create the illusion of a stand-alone application. Whether you need to employ this technique will depend on the type of programming you do.

www.microsoft.com/visualstudio/eng/products/visual-studio-express-products

Understanding Macro Basics

A macro is a sequence of commands you or a user can repeat at will. That's exactly the definition of a computer program. Macros, however, are generally short programs—dedicated to a single task. Think of it like this: A normal computer program, such as Photoshop or Internet Explorer (IE), has many capabilities. IE can prevent pop-up ads, block websites, display full-screen when you press F11, and so on. A macro is smaller, dedicated to accomplishing just one of these tasks, such as displaying full-screen.

In some applications, you can set a macro to run itself automatically. For instance, you might create a macro in Word to automate basic formatting tasks on a type of document you regularly receive incorrectly formatted. As you'll see in Chapter 6, “Working with Variables, Constants, and Enumerations,” in a discussion of the AutoExec feature, you can specify that a macro run automatically upon opening a document of that type.

A macro is a type of subroutine (sometimes also called a subprocedure). Generally, people tend to use the shorter, more informal terms sub, procedure, and routine. In the Visual Basic Editor, each of your macros starts with the word Sub. Note that a macro is a single procedure, whereas a computer program like IE is a collection of many procedures.

A macro used to be defined as recorded code rather than written code, but most people today use the word in its wider sense, so it can include written code as well. For example, if you record a macro and then edit it to make it more efficient, or to add commands to make it take further actions, most people still consider it a macro.

In an Office application that supports the VBA Macro Recorder (such as Word or Excel), you can create macros in two ways:

• Turn on the Macro Recorder and just perform the sequence of actions you want the macro to perform. Clicks, typing, dragging, dropping—whatever you do is recorded.
• Open the Visual Basic Editor and type the VBA commands into it.

There's also a useful hybrid approach that combines recording with editing. First record the sequence of actions, and then later, in the Visual Basic Editor, you can view and edit your macro. You could delete any unneeded commands. Or type in new commands. Or use the editor's Toolbox feature to drag and drop user-interface elements (such as message boxes and dialog boxes) into your macro so users can make decisions and choose options for how to run it. Macros are marvelously flexible, and the VBA Editor is famously powerful yet easy to use.

Once you've created a macro, you specify how you want the user to trigger it. In most applications, you can assign a macro to the Ribbon, to the Quick Access Toolbar, or to a shortcut key combination. This makes it very easy to run the macro by merely clicking an icon or pressing a shortcut key (such as Alt+R). You can also optionally assign your macro to a Quick Access Toolbar button or keyboard shortcut when you first record the macro, via a dialog box that automatically appears when you begin a recording. You'll see how all this works shortly. It's simple. (To assign a macro to the Ribbon, first record it, then right-click the Ribbon and choose Customize The Ribbon. Locate and click the Choose Commands From drop-down box, then click the Macros entry to display all your macros.)

Recording a Macro

The easiest way to create VBA code is to record a macro using the Macro Recorder. Only Word and Excel include a Macro Recorder.

You switch on the Macro Recorder, optionally assign a trigger that will later run the macro (a toolbar button or a shortcut key combination), perform the actions you want in the macro, and then switch off the Macro Recorder. As you perform the actions, the Macro Recorder translates them into commands—code—in the VBA programming language.

Once you finish recording the macro, you can view the code in the Visual Basic Editor and change it if you wish. If the code works perfectly as you recorded it, you never have to look at it—you can just run the macro at any time by clicking the toolbar button or key combination you assigned to the macro.

Displaying the Developer Tab on the Ribbon

Before going any further, ensure that the Developer (programmer) tab is visible in your Ribbon. This tab is your gateway to macros, VBA, and the VBA Editor. By default, the Office applications do not display this tab. (Access doesn't even have this tab. Word, Excel, PowerPoint, and Outlook do.) To add this tab to your Ribbon, click the File tab, and then click Options. Click Customize Ribbon. In the list box on the right, click Developer to select it. Click the OK button to close the Options dialog box.

In the following sections, you'll look at the stages involved in recording a macro. The process is easy, but you need to be familiar with some background if you haven't recorded macros before. After the general explanations, you'll record example macros in Word and Excel. (Later in the book you'll examine and modify those macros, after you learn how to use the Visual Basic Editor. So don't delete them.)

Planning the Macro

Before you even start the Macro Recorder, it's sometimes a good idea to do a little planning. Think about what you will do in the macro. In most cases, you can just record a macro and not worry about the context. You can just record it with a document open and some text visible. But in some situations you need to ensure that a special context is set up before you start the recording. For example, you might want to create a macro in Word that does some kind of editing, such as italicizing and underlining a word. To do this, you'll want to first have the blinking “insertion” cursor on a word that's not italicized or underlined. You don't want to record the actions of moving the insertion cursor to a particular word. That would make your macro specific to this document and this word in that document. You usually want a macro to work well with more than just one particular document.

Your macro is intended to just italicize and underline whatever word is currently under the blinking cursor in any document. Nevertheless, most simple macros can be recorded without any special planning. Just record whatever you want the macro to do.

Some macros should perform any necessary setup themselves. The setup will be part of the macro. In these cases, you should make sure the application is in the state that the macro expects before you start recording the macro. For example, if, to do its job, a macro needs a blank active workbook in Excel, the macro itself should create that blank workbook rather than using whichever workbook happens to be active at the time. This saves a step when the macro runs. So to do this, start recording before launching a blank active workbook.

http://technet.microsoft.com/en-us/library/ee857085(v=office.15).aspx

Starting the Macro Recorder

Start the Macro Recorder by clicking the Developer tab on the Ribbon and then clicking the Record Macro button. You can also click the Macro Record button on the status bar at the bottom of the application. (With this approach, you don't have to open the Developer tab. Just click the button on the status bar.)

As soon as you start the Macro Recorder, the Record Macro dialog box opens. You see that this new macro has been given a default macro name (Macro1, Macro2, and so on). You can accept that default name or change it. There's also an optional description to fill in if you wish.

To stop the Macro Recorder, you can click the Stop Recording button in the Developer tab. You can alternatively stop the recording by clicking the square button that appears during recording on the status bar, down on the bottom left of the application's window. Once the Recorder is stopped, the square button is replaced with an icon that you can click to start recording a new macro. In Word for the Mac, click the REC indicator rather than double-clicking it.

The appearance of the Record Macro dialog box varies somewhat from one application to another because the dialog box must offer suitable options to accommodate the varying capabilities particular to each application. In each case, you get to name the macro and add a description of it. In most cases, you can also specify where to save the macro—for example, Word offers two options. For global use (making the macro available to all Word documents), store it in the file named normal.dotm. Or, if it is merely to be used in the currently active document, choose to store it in a file with the document's name and the .dotm filename extension. An ordinary Word template has a .dotx filename extension, but macros are stored in a file with the filename extension .dotm.

Other applications differ somewhat in how the dialog works when you begin recording a macro. For example, Excel allows you three options: to store macros in the current workbook, or in a new workbook, or for use with all Excel workbooks, in the Personal Macro Workbook. That's the equivalent of Word's Normal.dotm file, and Excel's Personal Macro workbook is saved in a file named Personal.xlsb.

The Record Macro dialog box also lets you specify how you want the macro triggered. Word displays buttons you can click to either open a dialog for entering a shortcut key combination or open the Word Options dialog where you can create a button for this macro that will appear on the Quick Access Toolbar. Excel limits you to Ctrl+ shortcut key combinations as a way of launching macros, so there is no button to display a full keyboard shortcut dialog like the one in Word. Excel has only a small text box where you can enter the key that will be paired with Ctrl as the shortcut.

Most of the Microsoft applications that host VBA have the Developer tab from which you control macro recording, launch the Visual Basic Editor, and otherwise manage macros. Access, however, groups several of its macro-related tools in a Database Tools tab (which is visible by default) and also has a Macro option on its Create tab.

Figure 1.1 shows the Record Macro dialog box for Word with a custom name and description entered. Figure 1.2 shows Word's version of the Developer tab on the Ribbon.

FIGURE 1.1 In the Record Macro dialog box, enter a name for the macro you're about to record. Type a concise but helpful description in the Description box. This is the Record Macro dialog box for Word.

FIGURE 1.2 You can use the Developer tab on the Ribbon to work with macros.

Here's what the primary Visual Basic features on the Ribbon's Developer tab (or Access's Database Tools tab) do:

Naming the Macro

Next, enter a name for the new macro in the Macro Name text box in the Record Macro dialog box. The name must comply with the following conventions:

• It must start with a letter; after that, it can contain both letters and numbers.
• It can be up to 80 characters long.
• It can contain underscores, which are useful for separating words, such as File_Save.
• It cannot contain spaces, punctuation, or special characters, such as ! or *.

Sub AltH()
' Applies Heading 1 style
    Selection.Style = ActiveDocument.Styles("Title1")
End Sub

INVALID MACRO NAMES

Word and Excel, the two Office applications that permit macro recording, raise objections to invalid macro names when you click the OK button to start recording the macro. If you enter an invalid macro name in the Record Macro dialog box, these applications let you know—in their own way. Word displays a brief, rather cursory message, while Excel gives more helpful info. Figure 1.3 shows how these applications respond to an invalid macro name once it's entered.

FIGURE 1.3 The dialog boxes supplied by Word and Excel showing invalid macro names.

DESCRIBING YOUR MACROS

Type a description for the macro in the Description text box. Recall that this description is to help you (and anyone you share the macro with) identify the macro and understand when to use it. If the macro runs successfully only under particular conditions, you can note them briefly in the Description text box. For example, if the user must make a selection in the document before running the macro in Word, mention that.

You now need to choose where to store the macro. Your choices with Word and Excel are as follows:

STORING YOUR MACROS

Word and Excel automatically store recorded macros in a default location in the specified document, template, workbook, or presentation:

Choosing How to Run a New Macro

Continuing our exploration of the Record Macro dialog box shown in Figure 1.1, at this point, after you've named the macro, typed a description, and chosen where to store it, it's time to choose how to trigger the macro. In other words, which way do you want to run the macro: via a shortcut key or the Quick Access Toolbar button? Good typists generally prefer shortcut keys, but buttons provide at least a visual hint of the macro's purpose, and hovering your mouse on the button also displays the name of the macro.

Shortcut keys and buttons are handy for people who record a moderate number of macros and don't organize them in complex ways—moving them from one module to another. If you create a great number of macros and feel the need to move them into other modules, assigning a shortcut key or button prior to recording becomes less useful. This is because moving a macro from one module to another disconnects any way you've assigned for running the macro.

This limitation means that it makes sense to assign a way of running a macro—prior to recording—only if you're planning to use the macro in its recorded form (as opposed to, say, using part of it to create another macro) and from its default location. If you plan to move the macro or rename it, don't assign a way of running it now. Instead, wait until the macro is in its final form and location, and then assign the means of running it. See “Specifying How to Trigger an Existing Macro,” later in this chapter, for details.

Personally, I don't have more than a couple dozen macros that I use all the time, so I avoid the complications described in the previous paragraph and the sidebar on managing your macros. Instead, I just add shortcut keys when I first create the macro, and leave them all in a single version of Normal.dotm. However, if you face more complicated situations—such as managing a big set of macros for a company—you might want to manage your macros with modules.

To assign a way to run the macro, follow the instructions in the next sections.

You don't have to assign a button or keyboard shortcut prior to recording a macro. You can do it later, or at any time. In Word, Access, Excel, and other Office 2013 applications, you use the Options dialog box to assign a button on the Quick Access Toolbar to a macro. PowerPoint and Access do not permit you to assign keyboard shortcuts to macros, but for applications that do permit this—such as Word and Excel—you use the Customize Keyboard dialog box to assign a shortcut key to a macro. Excel limits you to Ctrl+ or Ctrl+Shift key combinations.

RUNNING A MACRO FROM THE RIBBON

Although it's not available in the Record Macro dialog box, you can add a macro to the Ribbon, like this:

1. Right-click anywhere on the Ribbon.
2. Click Customize The Ribbon on the menu. The Word Options dialog box appears.
3. In the Choose Commands From drop-down list, select Macros.
4. Click a macro's name to select it in the list.
5. Click an existing tab in the list of tabs in the right dialog box where you want to locate your macro.
6. Then click the New Group button and specify the name of your custom group.
7. Click the rename button to give your new group a name.
8. Click OK to close the Rename dialog box.
9. Click the Add button to add your macro.
10. Click the rename button to give your macro an easily understood name, and optionally an icon.
11. Click OK to close the Rename dialog box.
12. Click OK to close the Word Options dialog box.

RUNNING A MACRO FROM THE QUICK ACCESS TOOLBAR

Here's how to use the Word Options dialog box to assign a macro to a button on the Quick Access Toolbar:

1. Right-click anywhere on the Quick Access Toolbar (it's the set of icons in the upper-left corner, above the Ribbon), and a menu will appear. (This toolbar will be just below the Ribbon if you've previously selected the Show Quick Access Toolbar Below The Ribbon option from this menu.)
2. Click Customize Quick Access Toolbar on the menu. The Word Options dialog box appears.
3. In the Choose Commands From drop-down list, select Macros.
4. Click a macro's name to select it in the list, as shown in Figure 1.4.
5. Click the Add button to insert this macro's name in the Customize Quick Access Toolbar list, as shown in Figure 1.4.

   FIGURE 1.4 Choose a way to run the macro in Word's Options dialog box.

   

6. Word adds a button to the toolbar for the macro, giving it the macro's fully qualified name (its location plus its name), such as Normal.NewMacros.CreateDailyReport. This name consists of the name of the template or document in which the macro is stored, the name of the module that contains the macro, and the macro's name, respectively. You don't need all this information displayed when you hover your mouse pointer over the button.
7. So rename the button or menu item: Click the Modify button at the bottom of the Customize Quick Access Toolbar list (see Figure 1.5). Whatever macro is highlighted (currently selected) in the list of toolbar items will be the one you're modifying.

   MACRO BUTTON LABELS NEED NOT MATCH THEIR OFFICIAL NAMES

   Notice that a macro's button name (displayed as its tooltip caption when you hover your mouse over it) doesn't have to bear any relation to the macro's actual name as it appears in the Visual Basic Editor or the Macro dialog.

   FIGURE 1.5 Word gives the menu item or toolbar button the full name of the macro. Use this Modify Button dialog to change the name to something shorter and better.

   

8. While you're modifying the macro's name, you might also want to choose a different button icon that visually cues you about the macro's purpose. To do that, just double-click whatever icon you want to use, then click OK.

RUNNING A MACRO VIA A SHORTCUT KEY COMBINATION

To assign the macro to a key combination, follow these steps:

1. Right-click the Ribbon and choose Customize The Ribbon from the menu that appears. This opens the Word Options dialog.
2. Click the Customize button next to Keyboard Shortcuts in the bottom left of the Word Options dialog box.
3. Scroll down the Categories list box until you see Macros, then click Macros to select it.
4. Click to select the name of the macro you want to assign a shortcut key combination to.
5. Check the Current Keys list box to see if a key combination is already assigned. If it is, you can press the Backspace key to clear the key combination if you wish, or you can employ multiple key combinations to launch the macro.
6. In the Press New Shortcut Key field, type the key combination you want to use to trigger the macro (see Figure 1.6).
7. Check to see if this key combination is already used for another purpose. If so, you can reassign it, or you can choose a different combination by pressing the Backspace key in the Press New Shortcut Key field.
8. Be sure to click the Assign button when you're finished. Just closing this dialog does not assign the key combination.

FIGURE 1.6 Set a shortcut key combination for the macro in the Customize Keyboard dialog box.

A key combination in Word can be any of the following:

• Alt plus either a function key or a regular key not used as a menu-access key.
• Ctrl plus a function key or a regular key.
• Shift plus a function key.
• Ctrl+Alt, Ctrl+Shift, Alt+Shift, or even Ctrl+Alt+Shift plus a regular key or function key. Pressing Ctrl+Alt+Shift and another key tends to be too awkward for practical use.

RUNNING A MACRO THE OLD-FASHION WAY

A clumsy, rarely used way to run a macro is to click the Developer tab in the Ribbon. To see how this works, follow these steps:

1. Click the Macros icon.
2. Click the name of the macro in a displayed list.
3. Finally, click the Run button.

By the way, you can also run a macro from within the Visual Basic Editor by pressing F5. This is how you test macros while you're editing them.

ASSIGNING A WAY TO RUN A MACRO IN EXCEL

When you're recording a macro, Excel allows you to assign only a Ctrl shortcut key, not a button, to run it. If you want to assign a Quick Access Toolbar button to the macro, you need to do so after recording the macro (using the Customize feature as described shortly).

To assign a Ctrl shortcut key to run the macro you're recording, follow these steps:

1. Start recording the macro, then click the Shortcut Key text box to display the blinking insertion cursor. Press the shortcut key you want to use. (Press the Shift key at the same time if you want to include Shift in the shortcut.)
2. In the Store Macro In drop-down list, specify where you want the Macro Recorder to store the macro. Your choices are as follows:
   • This Workbook stores the macro in the active workbook. This option is useful for macros that belong to a particular workbook and do not need to be used elsewhere.
   • New Workbook causes Excel to create a new workbook for you and store the macro in it. This option is useful for experimental macros that you'll need to edit before unleashing them on actual work.
   • Personal Macro Workbook stores the macro in the Personal Macro Workbook, a special workbook named PERSONAL.XLSB. By keeping your macros and other customizations in the Personal Macro Workbook, you can make them available to any of your procedures—in that way, the Personal Macro Workbook is similar to Word's Normal.dotm. If the Personal Macro Workbook does not exist yet, the Macro Recorder creates it automatically.
3. Click the OK button to start recording the macro.

ASSIGNING A WAY TO RUN A MACRO IN POWERPOINT

PowerPoint does not let you record macros, but you can assign a way to run macros written in the Visual Basic Editor, as discussed in the section “Specifying How to Trigger an Existing Macro” later in this chapter.

ASSIGNING A WAY TO RUN A MACRO IN OUTLOOK

Outlook doesn't let you record macros, and by default macros are disabled. To enable macros in Outlook, click the Developer tab on the Ribbon, then click the Macro Security icon (it's on the left in the Code section of the Ribbon). The Trust Center dialog box opens. Click the Notification For All Macros option or the Enable All Macros option. To see how to assign a way to run macros, see the section “Specifying How to Trigger an Existing Macro” later in this chapter.

RECORDING THE ACTIONS IN A MACRO

When you close the Record Macro dialog box, the Macro Recorder begins recording the macro. The Macro Recorder displays the Stop Recording icon (a white square) in the status bar at the bottom left of the screen (and a Stop Recording button in the Developer tab on the Ribbon). In addition, a small symbol of a cassette tape appears in the mouse pointer (these tapes were used in the old days, prior to the invention of the CD).

Now you should perform the sequence of actions you want to record. What exactly you can do varies from application to application, but in general, you can use the mouse to select items, make choices in dialog boxes, and select defined items in documents (such as cells in spreadsheets). You'll find a number of things that you can't do with the mouse, such as select items within a document window in Word. To select items in a Word document window, you have to use the keyboard (Shift+arrow keys, for example). You can select cells with the mouse in Excel during recording.

In Word, if you need to perform any actions that you don't want recorded, pause the Macro Recorder by clicking the Pause Recording button on the Ribbon. The button changes to Resume Recording. Click the button again to start recording again.

To stop recording, click either the Stop Recording button on the Ribbon, or the other one on the status bar.

The Macro Recorder has now recorded your macro and assigned it to a key combination or button, if you made that choice.

Running a Macro

To run a macro you've recorded, you can use four methods to run it within the application:

• If you assigned a Quick Access Toolbar button, use that.
• If you added your macro to the Ribbon, you can use that.
• If you specified a shortcut-key-combination macro, use it.
• A less convenient approach is to press Alt+F8 to display the Macros dialog box, select the macro, and then click the Run button. (Alternatively, you could double-click the macro name in the list box.)

The macro runs, performing the actions in the sequence in which you recorded them. For example, suppose you create a macro in Excel that selects cell A2 in the current worksheet, boldfaces that cell, enters the text Yearly Sales, selects cell B2, and enters the number 100000 in it. The Macro Recorder recognizes and saves those five actions. VBA then performs all five actions, step-by-step, each time you run the macro—albeit quite rapidly.

Some applications (such as Word) let you undo most actions executed via VBA after the macro stops running (by pressing Ctrl+Z or clicking the Undo button on the Quick Access Toolbar, undoing one command at a time); other applications do not.

Recording a Sample Word Macro

In this section, you'll record a sample macro in Word that you can work with later in the book. This macro selects the current word, cuts it, moves the insertion point one word to the right, and pastes the word back in. This is a straightforward sequence of actions that you'll later view and edit in the Visual Basic Editor.

Follow these steps to record the macro:

1. Create a new document by pressing Ctrl+N.
2. Start the Macro Recorder by clicking the Developer tab on the Ribbon, then clicking the Record Macro button. Or click the Macro Record button on the status bar at the bottom of the application. (With this approach, you don't have to open the Developer tab. Just click the button on the status bar.)
3. In the Macro Name text box, enter Transpose_Word_Right.
4. In the Store Macro In drop-down list, make sure All Documents (Normal.dotm) is selected, unless you want to assign the macro to a different template. (This and future examples in this book assume this macro is located in Normal.dotm, so do store it there.)
5. In the Description box, enter a description for the macro (see Figure 1.7). Be fairly explicit and enter a description such as Transposes the current word with the word to its right. Created 5/5/13 by Nanci Selest-Gomes.

   FIGURE 1.7 Creating the sample macro in Word

   

6. Assign a method of running the macro, as described in the previous section, if you want to. Create a toolbar button or assign a keyboard shortcut. (The method or methods you choose is strictly a matter of personal preference.) If you'll need to move the macro to a different module (or a different template or document) later, don't assign a method of running the macro at this point.
7. Click the OK button to dismiss the Word Options dialog box or the Customize Keyboard dialog box (or just click the OK button to dismiss the Record Macro dialog box if you chose not to assign a way of running the macro). Now you're ready to record the macro. The Stop Recording option appears on the Ribbon and on the status bar, and the mouse pointer has a cassette-tape icon attached to it.

   

8. As a quick demonstration of how you can pause recording, click the Pause Recording button on the Ribbon. The cassette-tape icon disappears from the mouse pointer, and the Pause Recording button changes into a Resume Recording button. Enter a line of text in the document: The quick brown fox jumped over the lazy dog. Position the insertion point anywhere in the word quick, and then click the Resume Recording button on the Ribbon to reactivate the macro recorder.
9. Record the actions for the macro as follows:
   1. Use Word's extend selection feature to select the word quick by pressing the F8 key twice.
   2. Press the Esc key to cancel Extend mode.
   3. Press Shift+Delete to cut the selected word to the Clipboard.
   4. The insertion point is now at the beginning of the word brown. Press Ctrl+right arrow to move the insertion point right by one word so that it's at the beginning of the word dog.
   5. Press Shift+Insert to paste in the cut word from the Clipboard.
   6. Press Ctrl+left arrow to move the insertion point one word to the left. This restores the cursor to its original position.
10. Click the Stop Recording button on the Ribbon or status bar. Your sentence now reads, “The brown quick fox jumped over the lazy dog.”

You can now run this macro by using the toolbar button or keyboard shortcut that you assigned (if you chose to assign one). Alternatively, click the Macros button in the Developer tab and run the macro from the Macros dialog box. Try positioning the insertion point in the word brown and running the macro to restore the words in the sentence to their original order.

At this point, Word has stored the macro in Normal.dot. If you don't save macros until you exit Word (or until an automated backup takes place), Word doesn't, by default, prompt you to save them then. It just does so automatically. But it's best to click the Save button in the File tab to store Normal now. That way, if Word or Windows crashes, you will avoid losing the macro.

Recording a Sample Excel Macro

In the following sections, you'll record a sample Excel macro. This macro creates a new workbook, enters a sequence of months into it, and then saves it. You'll work with this macro again in Chapter 3, so don't delete it.

Create a Personal Macro Workbook If You Don't Have One Yet

If you don't already have a Personal Macro Workbook in Excel, you'll need to create one before you can create this procedure. (If you do have a Personal Macro Workbook, skip to the next section.) Follow these steps:

1. Click the Developer tab in the Ribbon, then click the Record Macro button on the Ribbon (or just click the Record Macro button on the status bar) to display the Record Macro dialog box.
2. Accept the default name for the macro because you'll be deleting it momentarily.
3. In the Store Macro In drop-down list, choose Personal Macro Workbook.
4. Click the OK button to close the Record Macro dialog box and start recording the macro.
5. Type a single character in whichever cell is active, and press the Enter key.
6. Click the Stop Recording button on the Ribbon or status bar to stop recording the macro.
7. Click the Unhide button on the View tab to display the Unhide dialog box. Select PERSONAL.XLSB and click the OK button.
8. Click the Developer tab in the Ribbon, then click the Macros button on the Ribbon to display the Macros dialog box.
9. Select the macro you recorded and click the Delete button to delete it. Click the Yes button in the confirmation message box.

You now have caused Excel to generate a Personal Macro Workbook that you can use from now on to hold your global macros.

Record the Macro

To create this macro, start Excel and follow these steps:

1. Click the Developer tab in the Ribbon, then click the Record Macro button on the Ribbon (or just click the Record Macro button on the status bar). This displays the Record Macro dialog box, shown in Figure 1.8, with information entered.
2. Enter the name for the macro in the Macro Name text box: New_Workbook_with_Months.
3. In the Shortcut Key text box, enter a shortcut key if you want to. (Remember that you can always change the shortcut key later, so you're not forced to enter one at this point.)
4. In the Store Macro In drop-down list, choose whether to store the macro in your Personal Macro Workbook, in a new workbook, or in this active workbook. As discussed a little earlier in this chapter, storing the macro in the Personal Macro Workbook gives you the most flexibility because it is Excel's global macro container. For this example, don't store the macro in the active workbook, because you're going to delete the active workbook almost immediately. Instead, store it in your Personal Macro Workbook. Remember, we'll use this macro in future examples.

   FIGURE 1.8 Display the Record Macro dialog box for Excel and make your choices in it.

   

5. Type a description for the macro in the Description text box.
6. Click the OK button to dismiss the Record Macro dialog box and start recording the macro.
7. Click the File tab on the Ribbon and click New to display the available templates for a new workbook.
8. Double-click the Blank workbook icon. Excel creates a new workbook and selects the first sheet on it.
9. Click cell A1 to select it. (It may already be selected; click it anyway because you need to record this click instruction.)
10. Enter January 2014 and press the right arrow key to select cell B1. Excel automatically changes the date to your default date format. That's fine.
11. Enter February 2014 and press the left arrow key to select cell A1 again.
12. Drag from cell A1 to cell B1 so that the two cells are selected.
13. Drag the fill handle from cell B1 to cell L1 so that Excel's AutoFill feature enters the months March 2014 through December 2014 in the cells. (The fill handle is the small black dot in the lower-right corner of the selection frame. You'll know you're on it when the cursor changes from a white to a black cross.)
14. Click the File tab on the Ribbon, then click the Save As option to display the Save As dialog box. Save the workbook in a convenient folder (for example, the My Documents folder) under a name such as Sample Workbook.xlsx.
15. Click the Stop Recording button on the Ribbon or status bar to stop recording the macro.

Close the sample workbook, and use Windows Explorer to navigate to the new .xlsx file you just saved, and delete the file. Then run the macro and watch what happens. (If you don't delete the existing workbook, Excel prompts you to decide whether to overwrite it when in step 14 it tries to save the new workbook using the same name as the existing workbook.)

Specifying How to Trigger an Existing Macro

If you didn't assign a way of running the macro when you recorded it, you can assign a way of running it as described here.

Assigning a Macro to a Quick Access Toolbar Button in Word

To assign a macro to the Quick Access Toolbar, follow these steps:

1. Right-click anywhere on the Quick Access Toolbar (it's the set of icons in the upper-left corner, above the Ribbon). A menu appears.
2. Click Customize Quick Access Toolbar on the menu. The Word Options dialog box appears.
3. In the Choose Commands From drop-down list, select Macros.
4. Click the name of the macro you want to assign a button to.
5. Click the Add button to copy the macro name into the list of buttons on the right.
6. Click the Modify button if you want to assign a different icon or modify the button's name.
7. Click OK to close the dialog.

Assigning a Macro to a Shortcut Key Combination

The section “Running a Macro via a Shortcut Key Combination,” earlier in this chapter, explained how to do this in Word. PowerPoint and Access do not let you assign a macro to a key combination. Excel uses a slightly different approach than Word, limiting you to Ctrl and Shift combinations, as described earlier in this chapter in the section “Assigning a Way to Run a Macro in Excel.”

Deleting a Macro

To delete a macro you no longer need, follow these steps:

1. Press Alt+F8 to display the Macros dialog box.
2. Choose the macro in the Macro Name list box.
3. Click the Delete button.
4. In the warning message box that appears, click the Yes button. Figure 1.9 shows Excel's variation of this warning message box.

   FIGURE 1.9 When you delete a macro, the application checks to make sure you mean to do so.

   

5. Click the Close button or the Cancel button to close the Macros dialog box.

The Bottom Line

Record a macro. The easiest way to create a macro is to simply record it. Whatever you type or click—all your behaviors—are translated into VBA automatically and saved as a macro.

Master It Turn on the macro recorder in Word and create a macro that moves the insertion cursor up three lines. Then turn off the macro recorder and view the code in the Visual Basic Editor.

Assign a macro to a button or keyboard shortcut. You can trigger a macro using three convenient methods: clicking an entry on the Ribbon, clicking a button in the Quick Access Toolbar, or using a keyboard shortcut. You are responsible for assigning a macro to any or all of these methods.

Master It Assign an existing macro to a new Quick Access Toolbar button.

Run a macro. Macros are most efficiently triggered via a Ribbon entry, by clicking a button on the Quick Access Toolbar, or by pressing a shortcut key combination such as Alt+N or Ctrl+Alt+F. When you begin recording a macro, the Record Macro dialog has buttons that allow you to assign the new macro to a shortcut key or toolbar button. However, if you are using the Visual Basic Editor, you can run a macro by simply pressing F5.

Master It Execute a macro from within the Visual Basic Editor.

Delete a macro. It's useful to keep your collection of macros current and manageable. If you no longer need a macro, remove it. Macros can be directly deleted from the Visual Basic Editor or by clicking the Delete button in the Macros dialog (opened by pressing Alt+F8).

Master It Temporarily remove a macro, then restore it, using the Visual Basic Editor.