Working with Macros and Forms in Microsoft Excel 2010

8/12/2011

Contents

4.1 Create and Manipulate Macros
4.2 Insert and Manipulate Form Controls

This chapter from MOS 2010 Study Guide for Microsoft Word Expert, Excel Expert, Access, and SharePoint Exams shows you how to work with macros and forms in Microsoft Excel.

4.1 Create and Manipulate Macros

A macro in Excel can perform an operation as routine as adding your company address to a worksheet or printing a worksheet with specific settings. You could record a macro that enters a series of dates, months, or other periods of time in a worksheet row, aligns the cell data, and then applies formatting to the cells. Macros can, of course, be used to do many other tasks, especially once you learn how to enhance the operations of a macro you record or write macros from scratch by using Microsoft Visual Basic for Applications (VBA).

When working with macros in Excel, keep in mind that the default file format—Excel Workbook (.xlsx)—does not support macros. If you record or write a macro in a workbook and try to save the workbook as an .xlsx file, Excel displays a message indicating that you can’t include the macro. To include macros in a workbook, you must save the workbook in the Excel Macro-Enabled Workbook (.xlsm) format or as a macro-enabled template (.xltm). You can also use the Excel 97–2003 Workbook (.xls) format, but that format does not support many of the features in Excel 2010.

You can run the macros in a workbook in a variety of ways, which lets you choose the option that fits the situation at hand. For a macro you want to apply to every workbook, you can add a button that runs the macro to the Quick Access Toolbar or to a custom group on the ribbon. You can also define a macro that runs whenever a workbook is opened or run a macro by using a keystroke.

In the following sections, you’ll learn details about recording a macro and about different ways you can run a macro.

Recording and Storing a Macro

In the Record Macro dialog box, you should first provide a name for the macro you’re recording. (You can accept the default name that Excel displays, but providing your own descriptive name is more useful.) You must begin a macro name with a letter; you can include numbers later in a macro’s name. Macro names cannot include spaces. Use an underscore character to separate words or use interior capitalization (for example, My_new_macro or MyNewMacro).

By default, Excel first displays This Workbook in the Store Macro In list. Keep this option if you want the macro to be available only in this workbook. Choose Personal Macro Workbook to make a macro available throughout Excel. The Personal Macro Workbook (Personal.xlsb) is a file Excel creates when you first choose to save a macro in it. (Excel prompts you to save Personal.xlsb when you save a macro in the Personal Macro Workbook and when you add macros to it after that.) Excel opens Personal.xlsb when you run Excel but hides the workbook by default. You can view it by choosing Unhide in the Window group on the View tab.

Using the XLStart Folder

Excel by default saves Personal.xlsb in a folder named XLStart, which it creates under your user profile. In Windows 7 or Windows Vista, the XLStart folder is located at Users\username\AppData\Roaming\Microsoft\Excel. In Windows XP, the folder is located at \Documents and Settings\username\Application Data\Microsoft\Excel. If you want Excel to open a specific workbook each time you run Excel, add that workbook to the XLStart folder. If you want each workbook you create to contain specific information—a custom header, for example—create a workbook named Book.xlsx and save it in the XLStart folder. Excel opens this workbook when it starts. Add the details you need to the workbook, and then save it with its own name.

Before you record a macro, you should also determine whether to use absolute or relative cell references. (You can use a combination of the two as necessary). If you keep absolute references, the macro operates only in the specific cells you use when you record the macro. For example, with absolute references, if you insert a series of dates or names in the cell range A1:G1 as a step in recording a macro, that macro works only in that cell range when you run it. If you use relative references (by clicking Use Relative References in the Code group), the macro will insert the data in any comparable range of cells.

You can alternate between absolute and relative references while recording a macro by turning on or off the Use Relative References command. The command appears highlighted when it is enabled.

MACRO SECURITY SETTINGS

The default security setting for macros in Excel is Disable All Macros With Notification. With this option selected, when you open a workbook that contains macros, Excel displays a security warning indicating that macros are disabled. Click Enable Macros if you trust the workbook and need access to the macros. You can change security settings by clicking the Macro Security command in the Code group on the Developer tab. Use the Macros Settings page of the Trust Center dialog box to select a different option. For example, if you are experimenting with macros during a work session, select Enable All Macros (Not Recommended) as a temporary measure and then reset the default option (or choose one of the other options that disable macros) when you are done. The other options available are Disable All Macros Without Notification and Disable All Macros Except Digitally Signed Macros.

If you select the most restrictive setting (Disable All Macros Without Notification), you can still gain access to macros in a workbook by saving that workbook in a trusted location. (The Trust Center does not check files stored in a trusted location.) You can view default trusted locations and add a new location by using the Trusted Locations page in the Trust Center dialog box (the same dialog box that Excel displays when you click Macro Security in the Code group).

Digital signatures are used to verify the source of a macro and to confirm that a macro (or a Visual Basic project that contains a macro) has not been modified since it was signed. If you select the option Disable All Macros Except Digitally Signed Macros, Excel enables macros automatically if the macros are signed and originate from a source (a publisher) you have trusted. Excel displays a security warning and the Enable Macros button when you open a workbook from a source that is not a trusted publisher. Unsigned macros are disabled if you select this option.

To record a macro
1. On the Developer tab, click Record Macro.
2. In the Record Macro dialog box, type a name for the macro.
3. Enter a shortcut key if you want to run the macro from the keyboard.
4. In the Store macro in list, specify whether to store this macro in the current workbook, the Personal Macro Workbook, or a new workbook.
5. Type a description of the macro, and then click OK.
6. To turn on relative references, click Use Relative References in the Code group.
7. Follow the steps you want to record in the macro.
  
  Click the Use Relative References command to control the use of relative references as you record the macro.
8. When you complete the steps, click Stop Recording.

Modifying a Macro

When you record a macro in Excel, Excel stores the macro in a module that you can work with in the Visual Basic editor. To open the Visual Basic editor, click Visual Basic in the Code group. To work with a specific macro, first click Macros on the Developer tab (or click Macros, View Macros on the View tab). In the Macro dialog box, select the macro you want to modify and then click Edit. You’ll see the modules for the current workbook listed in the pane at the left (called the Project Explorer). The code for the macros in that module appears in the Code window.

If you are familiar with VBA, you can modify code directly. If you are working with macros you recorded, you can expand and change the operations of a macro by adding code for one macro to another macro or by calling one macro from another.

For example, as part of recording a macro that inserts your company’s address or another block of text, you might apply formatting to the cells by using a specific font and font size, bold text, a light-colored fill, and a centered alignment. Excel would generate code something like the following to apply that formatting (you would see this code within the code that performs the macro’s other operations):

With Selection.Font
    .Name = "Arial"
    .FontStyle = "Bold"
    .Size = 11
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
End With
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorLight2
    .TintAndShade = 0.799981688894314
    .PatternTintAndShade = 0
End With

If you want to use this formatting code in another macro, you can copy the code and paste it into the code from another macro. You can also save this code as a separate subprocedure in a module and run it on its own when you want to apply the same formatting to a selection of cells.

A subprocedure is a set of code statements enclosed by Sub and End Sub statements. You can see these statements in the preceding screen shot. When you record a macro in Excel, the macro is defined in a subprocedure. You can call one macro from another by referring to the name of the macro you want to run in the subprocedure for the second macro. In the following code, for example, the RefreshData macro is run from the Auto_Open macro:

Sub Auto_Open()
'
' Auto_Open Macro
'
    MsgBox "Do you want to update financial data?", vbYesNo
      If Response = vbYes Then
      RefreshData
      End If

End Sub

To save code as its own subprocedure, click Visual Basic on the Developer tab. In the Visual Basic editor, in the Project Explorer, open the module that contains the macro with the code you want to copy. Select that code, and copy it to the Clipboard.

On the Insert menu in the Visual Basic editor, click Procedure. Type a name and keep the default settings in the Type and Scope sections. The Sub option creates a subprocedure, and the Public option makes the code available to macros in other modules.

When you click OK in the Add Procedure dialog box, the Visual Basic editor inserts the lines Public Sub (which includes the name you provided) and End Sub. Paste the code you copied between these lines.

Even though you didn’t specifically record a macro with this name, you’ll find an entry for it in the Macro dialog box in Excel. You can now run the macro as you would any other, including by calling the macro from another macro. To do this, you add the macro’s name to the macro you want to call it from. Here’s an example in which the FormatStandard macro is called at the end of the AddressBlock macro.

To open the Visual Basic editor to work with a macro
- Click Visual Basic in the Code group on the Developer tab.
To modify a macro
1. In the Code group, click Macros.
2. In the Macro dialog box, select the macro you want to modify and then click Edit.
3. Revise the macro by using the Visual Basic editor, and then click Save on the Visual Basic editor toolbar.
To insert a subprocedure in the Visual Basic editor
1. In Excel, click Visual Basic in the Code group on the Developer tab.
2. In the Visual Basic editor, on the Insert menu, click Procedure.
3. In the Add Procedure dialog box, type a name for the procedure.
4. In the Type group, keep Sub selected. In the Scope group, keep Public selected, and then click OK.
5. In the Code window, write or copy code for the new subprocedure.
6. On the Visual Basic editor toolbar, click Save.

Run a Macro from the Macro Dialog Box

You can run a macro directly from the Macro dialog box, which Excel displays when you click Macros in the Code group on the Developer tab. Use the Macros In list to select from the macros available only in the current workbook, in all open workbooks, in Personal.xlsb (which contains global macros), or other choices.

Before you open the Macro dialog box, be sure you place the cursor in the cell where the macro should begin its operations (if necessary). When you’ve done this, click Macros, select the macro you want to run, and then click Run.

To run a macro from the Macro dialog box
1. Position your cursor where you want to start running the macro.
2. In the Code group, click Macros.
3. In the Macro dialog box, choose an option from the Macros in list to display the available macros.
4. Select the macro you want to run, and then click Run.

Assigning a Keystroke to a Macro

In the Record Macro dialog box, you can assign a shortcut key to a macro before you start recording. The shortcut key must be used in combination with the Ctrl key. You can also press the Shift key to use uppercase characters or a special character you access with the Shift key, but you cannot use an Alt key combination. To specify this shortcut when you record a macro, just type the key in the Short Key area of the Record Macro dialog box.

If you don’t assign a shortcut key when you record a macro, you can assign one later through the Macro dialog box. Click Macros on the Developer tab, select the macro in the list of macros, and then click Options. Use the Shortcut Key area of the Macro Options dialog box to assign the keystroke combination you want to use.

To assign a shortcut key to a macro
- When you record the macro, enter the shortcut key you want to use in the Shortcut key area of the Record Macro dialog box.
  
  or
1. On the Developer tab, click Macros.
2. In the Macro dialog box, select the macro you want to work with and then click Options.
3. In the Macro Options dialog box, enter the shortcut key in the Shortcut key area and then click OK.

Running a Macro When You Open a Workbook

By naming a macro Auto_Open, you define the macro so that it runs whenever you open a workbook. You will probably save an Auto_Open macro in a specific workbook (you might use an Auto_Open macro to set properties for your view of a workbook, for example), but you can save an Auto_Open macro in your Personal Macro Workbook (Personal.xlsb) so that it is applied to more than one workbook. However, keep in mind that an Auto_Open macro runs before other workbooks are opened, so storing it in Personal.xlsb is often problematic. If you record actions that you want Excel to perform on the default Book.xlsx workbook or on another workbook that is opened from the XLStart folder, the Auto_Open macro fails when you start Excel because the macro runs before the workbooks stored in XLStart are opened.

Another approach to automatically running a macro when you open a workbook is to use the workbook’s Open event, which you can add code to in the Visual Basic editor. (Events are part of Visual Basic for Applications. The default event for a command button is the Click event, for example. In addition to the Open event, you can control workbooks by using the BeforeClose or AfterClose events, for example.)

To use a workbook’s Open event, click Visual Basic in the Code group to open the Visual Basic editor. In the Project Explorer (the pane at the left), right-click This Workbook and choose View Code. In the Code window, open the list on the left (the Object list) and choose Workbook (if Workbook is not displayed). Open the list at the right (the Procedure list), and choose Open. In the Code window, you’ll see the following lines of code:

Private Sub Workbook_Open()

End Sub

Insert the code for the macro you want to run when Excel opens the workbook. You can write this code yourself, insert the name of a macro you recorded or wrote already, or copy code from another macro and paste it between Private Sub Workbook_Open() and End Sub. The code shown in the following screen shot updates an external connection to a text file when the workbook opens.

To create an Auto_Open macro
1. On the Developer tab, click Record Macro.
2. In the Macro dialog box, name the macro Auto_Open.
3. Click OK, and then record the steps you want the macro to perform.
4. Click Stop Recording, and then save the workbook.
  
  The macro runs the next time you open the workbook.
To run a macro from a workbook’s Open event
1. On the Developer tab, click Visual Basic in the Code group.
2. In the Visual Basic editor, right-click This Workbook in the Project Explorer and choose View Code.
3. In the Code window, choose Workbook from the Object list and Open from the Procedure list.
4. Insert the code for the macro between the lines of code Private Sub Workbook_Open() and End Sub.
5. On the Visual Basic editor’s toolbar, click Save.

Adding a Macro Button to the Ribbon

To run a macro from the ribbon, you need to add the macro to a custom group. You can create a custom group for one of the built-in tabs or create a tab of your own and add the macro to a group on your custom tab.

To start, open the Excel Options dialog box from the File tab and then click Customize Ribbon in the list on the left. If you are adding a custom group to one of the main tabs, consider the purpose of the macro in deciding where to place it. If you use the macro to format a workbook, you might add the group to the Page Layout tab. If the macro is designed for working with data, it might be useful to place it on the Data tab. If you have several macros and want to collect them on a tab of their own, create a custom tab that holds all your macros.

If you are adding a macro to a built-in tab, use the New Group button to create a custom group on that tab. To create a custom tab, click New Tab. Excel creates an entry for the tab and creates a new group for that tab as well. To name the tab (to something more meaningful than New Tab), select the entry for the custom tab, click Rename, and then type a display name for the tab. Do the same for the new group Excel creates by default for a custom tab. If you are adding several macros to a custom tab, use more than one group and name them so that they reflect the type of macros you are adding (Formatting, Analysis, and so on).

In the Choose Commands From list, select Macros. In the Main Tabs list, select the group where you want to place the macro, select the macro in the list at the left, and then click Add. If you want to provide a different display name for the macro or change the icon Excel uses to display it, click Rename and then make the modifications you want in the Rename dialog box.

To add a macro to a custom tab
1. On the File tab, click Options.
2. In the Excel Options dialog box, click Customize Ribbon.
3. Under the Main Tabs list, click New Tab.
4. In the Main Tabs list, click New Tab (Custom), and then click the Rename button.
5. In the Rename dialog box, type a display name for the custom tab and click OK.
6. In the Main Tabs list, click New Group (Custom) under the tab you created, and then click Rename.
7. Type a display name for the group, and select a symbol if you don’t want to use the default icon Excel provides.
8. In the Choose commands from list, select Macros.
9. Select the macro you want to add, and then click Add.
To add a macro to a built-in tab
1. On the File tab, click Options.
2. In the Excel Options dialog box, click Customize Ribbon.
3. In the Main Tabs list, select the tab where you want to add the macro and then click New Group.
4. In the Main Tabs list, click New Group (Custom), and then click Rename.
5. Type a display name for the group, and select a symbol if you don’t want to use the default icon Excel provides.
6. In the Choose commands from list, select Macros.
7. Select the macro you want to add, and then click Add.

Adding a Macro Button to the Quick Access Toolbar

One of the most convenient ways to run a macro is to add a button for that macro to the Quick Access Toolbar. You assign the macro to a button by using the Excel Options dialog box.

On the Customize The Quick Access Toolbar page, open the Choose Commands From list and select Macros. In the list of macros, select the one you want to add to the Quick Access Toolbar and then click Add. In the Customize Quick Access Toolbar list, select For All Documents if you want the button to appear on the Quick Access Toolbar for all workbooks. You can also select the current workbook if you want to use the macro within that scope.

To rename the button or to change the icon associated with it, select it in the list of commands on the Quick Access Toolbar and then click Modify. Use the Modify Button dialog box to change the display name and to choose an icon. The display name appears in a ScreenTip when you point to the button with the mouse.

To add a custom macro button to the Quick Access Toolbar
1. Click the arrow at the right end of the Quick Access Toolbar, and then click More Commands.
2. In the Choose commands from list, choose Macros.
3. In the list of macros, select the macro you want to place on the Quick Access Toolbar and then click Add.
4. Click Modify, and then select the icon you want to use and change the display name for the button.

Creating an Action Macro

In a worksheet that includes graphical objects—an image, for example, or clip art or a shape—you can assign a macro to all or a portion of the object and run that macro when you click the object (or a particular spot on the object). For example, in a worksheet to which you’ve added several shapes (using the Shapes gallery on the Insert tab) to illustrate a process or a decision path, you can assign a macro to one or more of the shapes. You can assign a macro to a diagram you insert using SmartArt (but not to the individual shapes in the diagram). You can also place an object on a picture, assign a macro to that object, and then apply formatting that hides the object. Using these steps, you effectively create a hidden hot spot on the picture that runs a macro when you click it.

To run a macro from an object, right-click the object and choose Assign Macro. In the Assign Macro dialog box, Excel provides a default name for the macro by appending Click to the type of object (for example, Rectangle2_Click). Use the default name, or type one of your own. You can select a macro from the list of available macros (by using the Macros In list to display macros from your Personal Macro Workbook or from other open workbooks), click Record to record a new macro, or click New to open the Visual Basic editor and write a macro using VBA. You can also edit an existing macro by selecting it and clicking Edit.

You’ll know that a macro is assigned to an object because the cursor appears as a hand with a pointing finger (the same icon you see when you point to a hyperlink). Click the object to run the macro.

To hide a shape or other object to which you’ve assigned a macro, right-click the shape and choose Format Shape. In the Format Shape dialog box, choose No Fill on the File page and No Line on the Line Color page.

To assign a macro to a shape or other object
1. Right-click the shape or object and choose Assign Macro.
2. In the Assign Macro dialog box, use the Macros in list to select the macros you want to see (This Workbook, All Open Workbooks, or another option).
3. Select the macro you want to assign to the shape and click OK, or click Record to record the macro, or click New to write the macro in the Visual Basic editor.
To hide a shape or other object to which a macro is assigned
1. Right-click the shape and choose Format Shape.
2. In the Format Shape dialog box, on the Fill page, click No Fill.
3. On the Line Color page, click No Line.

PRACTICE TASKS

The practice files for these tasks are located in the folder for Microsoft Excel 2010. You can save the results of these exercises in the same folder. Change the file name so that you don’t overwrite the sample files. When you are done, try performing the following tasks:

Open a blank worksheet. Record a macro named GetResults that performs the following operations:
- Uses the From Text command in the Get External Data group (on the Data tab) to import the text file Results.txt (which is included in the sample files folder). Click Next in the Text Import Wizard’s first step. In the second step, select comma as the delimiting character and then click Finish.
- Select the top row of the data, and apply bold formatting.
- Select the cells containing the data, and format them as currency.
- Stop recording the macro at this point. Delete the data you imported, and then run the macro from the Macro dialog box to test it.
Open the workbook SampleMacros.xlsm. Using the macros in this workbook, practice running the macros from the ribbon, from the Quick Access Toolbar, and from an object you add to the workbook.