Chapter 1

Macro Fundamentals

In This Chapter

arrow Understanding why you should use macros

arrow Recording macros

arrow Understanding macro security

arrow Seeing macros in action

A macro is essentially a set of instructions or code that you create to tell Excel to execute any number of actions. In Excel, macros can be written or recorded. The key word here is recorded.

Recording a macro is like programming a phone number into your cell phone. On your phone, you first manually dial and save the number. Later, you can redial those numbers with the touch of a button. In Excel, you start recording a macro, and then you perform your intended actions. While you record, Excel gets busy in the background, translating your keystrokes and mouse clicks to a macro. This written code is known as Visual Basic for Applications (VBA). After the macro is recorded, you can play back those actions anytime you want.

In this chapter, you explore macros and learn how you can use them to automate recurring processes to simplify your life.

Why Use a Macro?

The first step in using macros is admitting you have a problem. Actually, you may have several problems:

  • Repetitive tasks: As each new month rolls around, you have to crank out those reports. You have to import that data. You have to update those pivot tables. You have to delete those columns, and so on. Wouldn’t it be nice if you could fire up a macro and have the more redundant parts of your reporting processes performed automatically?
  • Mistakes: When you’re repeatedly applying formulas, sorting, and moving things around manually, you're bound to make mistakes. Add looming deadlines and constant change requests, and your error rate goes up. Why not calmly record a macro, ensure that everything is running correctly, and then forget it? The macro will perform every action the same way every time you run it; reducing the chance of errors.
  • Awkward navigation: Make your reports more user friendly, and those who have a limited knowledge of Excel, will appreciate your efforts. Macros can be used to dynamically format and print worksheets, navigate to specific sheets in your workbook, or even save the open document in a specified location. Your audience will appreciate these touches that help make perusal of your workbooks a bit more pleasant.

Macro Recording Basics

To start recording your first macro, you need to find Macro Recorder, which is on the Developer tab. Unfortunately, Excel comes out of the box with the Developer tab hidden — you may not see it on your version of Excel at first. If you plan to work with VBA macros, you'll want to make sure that the Developer tab is visible. To display this tab:

  1. Choose File ⇒ Excel Options.
  2. In the Excel Options dialog box, select Customize Ribbon.
  3. In the list box on the right, click to place a check mark next to Developer.
  4. Click OK to return to Excel.

Now that the Developer tab appears in the Excel ribbon, you can start Macro Recorder. Select Record Macro from the Developer tab. The Record Macro dialog box appears, as shown in Figure 1-1.

image

Figure 1-1: The Record Macro dialog box.

Here are the four parts of the Record Macro dialog box:

  • Macro name: Excel gives a default name to your macro, such as Macro1, but you should give your macro a name more descriptive of what it does. For example, you might name a macro that formats a generic table FormatTable.
  • Shortcut key: Every macro needs an event, or something to happen, for it to run. This event can be a button press, a workbook opening, or in this example, a keystroke combination. When you assign a shortcut key to your macro, entering that of keys triggers your macro to run. Note that you don’t need a shortcut key to trigger a macro, so this field is optional.
  • Store macro in: The This Workbook option is the default. Storing your macro in This Workbook simply means that the macro is stored along with the active Excel file. The next time you open that particular workbook, the macro is available to run. Similarly, if you send the workbook to another user, that user can run the macro as well (provided the macro security is properly set by your user — more on this later in this chapter).
  • Description: This optional field can come in handy if you have numerous macros in a spreadsheet or you need to give a user a more detailed description about what the macro does.

With the Record Macro dialog box open, follow these steps to create a simple macro that enters your name in a worksheet cell:

  1. In the User Name field, enter a new single-word name for the macro to replace the default Macro1 name.

    A good name for this example is MyName.

  2. In the Shortcut Key field, enter an uppercase N.

    You've just assigned this macro to the shortcut key Ctrl+Shift+N.

  3. Click OK to close the Record Macro dialog box and begin recording your actions.
  4. Select a cell in your Excel spreadsheet, type your name in the selected cell, and then press Enter.
  5. Choose Developer ⇒ Code ⇒ Stop Recording (or click the Stop Recording button in the status bar).

Examining the macro

The macro was recorded in a new module named Module1. To view the code in this module, you must activate Visual Basic (VB) Editor. You can activate VB Editor in either of two ways:

  • Press Alt+F11.
  • Choose Developer ⇒ Code ⇒ Visual Basic.

In VB Editor, the project window displays a list of all open workbooks and add-ins. This list is displayed as a tree diagram, which you can expand or collapse. The code that you recorded previously is stored in Module1 in the current workbook. When you double-click Module1, the code in the module appears in the Code window.

The macro should look something like this:

Sub MyName()
'
' MyName Macro
'
' Keyboard Shortcut: Ctrl+Shift+N
'
    ActiveCell.FormulaR1C1 = "Michael Alexander"

End Sub

The recorded macro is a Sub procedure named MyName. The statements tell Excel what to do when the macro is executed.

At the top of the procedure, note that Excel inserted some comments, which consist of information from the Record Macro dialog box. These comment lines (which begin with an apostrophe) aren’t necessary, and deleting them has no effect on how the macro runs. If you ignore the comments, you'll see that this procedure has only one VBA statement:

ActiveCell.FormulaR1C1 = "Michael Alexander"

This single statement inserts in the active cell the name you typed while recording.

tip Placing a single apostrophe in front of any text of creates a comment and is called commenting a line. Commented lines will turn green and Excel will skip these lines when running the macro. Comments allow you to add your own notes in the code, giving you a chance to document what the code is doing, any business rules you’ve applied, or any other information you feel would help when reading the code.

Testing the macro

Before you recorded this macro, you set an option that assigned the macro to the Ctrl+Shift+N shortcut key combination. To test the macro, return to Excel by using either of the following methods:

  • Press Alt+F11.
  • Click the View Microsoft Excel button on the VB Editor toolbar.

When Excel is active, activate a worksheet. (It can be in the workbook that contains the VBA module or in any other workbook.) Select a cell and press Ctrl+Shift+N. The macro immediately enters your name into the cell.

remember In the preceding example, note that you selected the cell to be formatted before you started recording your macro. This step is important. If you select a cell while the macro recorder is turned on, that cell will be recorded into the macro. In such a case, the macro would always format that particular cell, and it would not be a general-purpose macro.

Editing the macro

After you record a macro, you can make changes to it (although you must know what you’re doing). For example, assume that you want your name to be bold. You could re-record the macro, but editing the code is more efficient because this modification is simple. Press Alt+F11 to activate the VB Editor window. Then double-click Module1 and insert the following statement before the End Sub statement:

ActiveCell.Font.Bold = True

The edited macro appears as follows:

Sub MyName()
'
' MyName Macro
'
' Keyboard Shortcut: Ctrl+Shift+N
'
    ActiveCell.Font.Bold = True

    ActiveCell.FormulaR1C1 = "Michael Alexander"

End Sub

Test this new macro, and you see that it performs as it should.

Comparing Absolute and Relative Macro Recording

Now that you’ve read about the basics of the Macro Recorder interface, it’s time to go deeper and begin recording macros. The first thing you need to understand before you begin is that Excel has two modes for recording: absolute reference and relative reference.

Recording macros with absolute references

Excel’s default recording mode is absolute reference. When a cell reference in a formula is an absolute reference, it does not automatically adjust when the formula is pasted to a new location.

The best way to understand how this concept applies to macros is to try it out. Open the Chapter 1 Sample File.xlsx file and record a macro that counts the rows in the Branchlist worksheet. (See Figure 1-2.)

image

Figure 1-2: Your pretotaled worksheet containing two tables.

remember You can find the sample data set used in this chapter on this book’s companion website at www.dummies.com/extras/excelmacros. See this book's Introduction for more on the companion website.

Follow these steps to record the macro:

  1. Make sure cell A1 is selected.
  2. On the Developer tab, select Record Macro.
  3. Name the macro AddTotal.
  4. Choose This Workbook for the save location.
  5. Click OK to start recording.

    At this point, Excel is recording your actions.

  6. While Excel is recording, select cell A16 and type Total in the cell.
  7. Select the first empty cell in Column D (D16) and type = COUNTA(D2:D15).

    This formula gives a count of branch numbers at the bottom of column D. You use the COUNTA function because the branch numbers are stored as text.

  8. Click Stop Recording on the Developer tab to stop recording the macro.

    The formatted worksheet should look like something like the one in Figure 1-3.

image

Figure 1-3: Your post-totaled worksheet.

To see your macro in action, delete the Total row you just added and play back your macro by following these steps:

  1. On the Developer tab, select Macros.
  2. Find and select the AddTotal macro you just recorded.
  3. Click the Run button.

If all goes well, the macro plays back your actions to a T and gives your table a total. Now here’s the thing. No matter how hard you try, you can’t make the AddTotal macro work on the second table. Why? Because you recorded it as an absolute macro.

To understand what this means, examine the underlying code by selecting Macros on the Developer tab. The Macro dialog box appears, as shown in Figure 1-4.

image

Figure 1-4: The Excel Macro dialog box.

Select the AddTotal macro and click the Edit button. Visual Basic Editor opens and displays the code that was written when you recorded your macro:

Sub AddTotal()

  Range("A16").Select

  ActiveCell.FormulaR1C1 = "Total"

  Range("D16").Select

  ActiveCell.FormulaR1C1 = "=COUNTA(R[-14]C:R[-1]C)"

End Sub

Pay particular attention to the two lines of code that select range A16 and range D16. Because the macro was recorded in absolute reference mode, Excel interpreted your range selection as absolute cell references. In other words, no matter where your cursor is in your workbook, when you run the recorded macro, Excel will select cell A16 and then cell D16. In the next section, you take a look at what the same macro looks like when recorded in relative reference mode.

Recording macros with relative references

A relative reference means relative to the currently active cell. So use caution with your active cell choice — both when you record the relative reference macro and when you run it.

First, make sure the Chapter 1 Sample File.xlsx file is open. (This file is available on this book’s companion website at www.dummies.com/extras/excelmacros.) Then, use the following steps to record a relative reference macro:

  1. On the Developer tab, select the Use Relative References option, as shown in Figure 1-5.
  2. Make sure cell A1 is selected.
  3. On the Developer tab, select Record Macro.
  4. Name the macro AddTotalRelative.
  5. Choose This Workbook for the save location.
  6. Click OK to start recording.
  7. Select cell A16 and type Total in the cell.
  8. Select the first empty cell in Column D (D16) and type = COUNTA(D2:D15).
  9. On the Developer tab, click Stop Recording to stop recording the macro.
image

Figure 1-5: Recording a macro with relative references.

At this point, you've recorded two macros. Take a moment to examine the code for your newly created macro by selecting Macros on the Developer tab to open the Macro dialog box. Choose the AddTotalRelative macro and click Edit.

Again, Visual Basic Editor opens and shows you the code that was written when you recorded your macro. This time, your code looks something like the following:

Sub AddTotalRelative()

  ActiveCell.Offset(15, 0).Range("A1").Select

  ActiveCell.FormulaR1C1 = "Total"

  ActiveCell.Offset(0, 3).Range("A1").Select

  ActiveCell.FormulaR1C1 = "=COUNTA(R[-14]C:R[-1]C)"

End Sub

First note that the code does not contain references to specific cell ranges (other than the starting point, A1). Note that in this macro, Excel uses the Offset property of the active cell. This property tells the cursor to move a certain number of cells up or down and a certain number of cells left or right.

In this case, the Offset property code tells Excel to move 15 rows down and 0 columns across from the active cell (A1). Because the macro was recorded using relative reference, Excel will not explicitly select a particular cell as it did when recording an absolute reference macro.

To see this macro in action, delete the Total row for both tables and do the following:

  1. Select cell A1.
  2. On the Developer tab, select Macros.
  3. Find and select the AddTotalRelative macro.
  4. Click the Run button.
  5. Select cell F1.
  6. On the Developer tab, select Macros.
  7. Find and select the AddTotalRelative macro.
  8. Click the Run button.

Note that this macro, unlike your previous macro, works on both sets of data. Because the macro applies the totals relative to the currently active cell, the totals are applied correctly.

For this macro to work, you simply need to ensure that

  • You’ve selected the correct starting cell before running the macro.
  • The block of data has the same number of rows and columns as the data on which you recorded the macro.

I hope this simple example has given you a firm grasp of macro recording with both absolute and relative references.

Other Macro Recording Concepts

At this point, you should feel comfortable recording your own Excel macros. In this section, I describe some other important concepts you’ll need to keep in mind when working with macros.

Macro-enabled file extensions

Beginning with Excel 2007, Excel workbooks were given the standard file extension .xlsx. Files with the .xlsx extension cannot contain macros. If your workbook contains macros and you then save that workbook as an .xlsx file, your macros are removed automatically. Excel warns you that macro content will be disabled when saving a workbook with macros as an .xlsx file.

If you want to retain the macros, you must save your file as an Excel macro-enabled workbook. This gives your file an .xlsm extension. The idea is that all workbooks with an .xlsx file extension are automatically known to be safe, whereas you can recognize .xlsm files as a potential threat.

Macro security in Excel 2010

With the release of Office 2010, Microsoft introduced significant changes to its Office security model. One of the most significant changes is the concept of trusted documents. Without getting into the technical minutia, a trusted document is essentially a workbook you have deemed to be safe.

If you open a workbook that contains macros in Excel 2010 or later, you see a yellow bar message under the ribbon stating that macros (active content) have been disabled.

If you click Enable, the workbook automatically becomes a trusted document. This means you no longer are prompted to enable the content as long as you open that file on your computer. The basic idea is that if you tell Excel that you trust a particular workbook by enabling macros, it is highly likely that you will enable macros each time you open the workbook. Thus, Excel remembers that you’ve enabled macros before and inhibits any further messages about macros for that workbook.

This feature is great news for you and your clients. After enabling your macros one time, they won’t be annoyed at the constant messages about macros, and you won't have to worry that your macro-enabled dashboard will fall flat because macros have been disabled.

remember Any workbook you create from scratch will automatically be considered to be trusted. That is, Excel will not require you to enable macros in the workbooks you create.

Trusted locations

If the thought of any macro message coming up (even one time) unnerves you, set up a trusted location for your files. A trusted location is a directory that is deemed a safe zone where only trusted workbooks are placed. A trusted location allows you and your clients to run a macro-enabled workbook with no security restrictions as long as the workbook is in that location.

To set up a trusted location, follow these steps:

  1. On the Developer tab, select the Macro Security button.

    This activates the Trust Center dialog box.

  2. On the left, click Trusted Locations.

    The Trusted Locations menu appears (see Figure 1-6), displaying all the directories that are considered trusted.

  3. Click the Add New Location button.
  4. Click Browse, and find and select the directory that will be considered a trusted location.
image

Figure 1-6: Add directories that are considered trusted.

After you specify a trusted location, any Excel file that is opened from this location will have macros automatically enabled.

Storing macros in your personal macro workbook

Most user-created macros are designed for use in a specific workbook, but you may want to use some macros in all your work. You can store these general-purpose macros in the personal macro workbook so that they’re always available to you. The personal macro workbook is loaded whenever you start Excel. This file, named personal.xlsb, doesn't exist until you record a macro using the personal macro workbook as the destination.

To record the macro in your personal macro workbook, select the Personal Macro Workbook option in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-down list (refer to Figure 1-1).

If you store macros in the personal macro workbook, you don’t have to remember to open the personal macro workbook when you load a workbook that uses macros. When you want to exit, Excel asks whether you want to save changes to the personal macro workbook.

remember The personal macro workbook normally is in a hidden window to keep it out of the way.

Assigning a macro to a button and other form controls

When you create macros, you may want to have a clear and easy way to run each one. A basic button can provide a simple but effective user interface.

As luck would have it, Excel offers a set of form controls for creating user interfaces directly on spreadsheets. Several types of form controls are available, from buttons (the most commonly used control) to scrollbars.

The idea behind using a form control is simple. You place a form control on a spreadsheet and then assign a macro to it — that is, a macro you’ve already recorded. When the control is clicked, the macro is executed, or played.

Take a moment to create a button for the AddTotalRelative macro you created earlier. Here’s how:

  1. On the Developer tab, click the Insert command, shown in Figure 1-7.
  2. In the drop-down list that appears, select the button form control.
  3. Click the location where you want to place your button.

    When you drop the button control on your spreadsheet, the Assign Macro dialog box appears, as shown in Figure 1-8.

  4. Select the macro you want to assign to the button and then click OK.
image

Figure 1-7: Form controls are on the Developer tab.

image

Figure 1-8: Assign a macro to the button.

At this point, you have a button that runs your macro when you click it! Keep in mind that all controls in the Form Controls group (refer to Figure 1-7) work in the same way as the button form control used in this example. That is to say, you assign a macro to run when the control is selected.

Placing a macro on the Quick Access toolbar

You can assign a macro not only to a form control on a spreadsheet but also to a button in Excel’s Quick Access toolbar. The Quick Access toolbar sits either above or below the ribbon. You can add a custom button that will run your macro by following these steps:

  1. Right-click your Quick Access toolbar and select Customize Quick Access Toolbar.

    The dialog box illustrated in Figure 1-9 appears.

  2. On the left of the dialog box, click Quick Access Toolbar.
  3. In the Choose Commands From drop-down list, select Macros.
  4. Select the macro you want to add and then click the Add button.
  5. Change the icon by clicking the Modify button.
image

Figure 1-9: Adding a macro to the Quick Access toolbar.

Examples of Macros in Action

Covering the fundamentals of building and using macros is one thing. Coming up with good ways to incorporate them in your reporting processes is another. In this section, you take a moment to review a few examples of how macros automate simple reporting tasks.

remember To follow along in this section, go to www.dummies.com/extras/excelmacros and open the Chapter 1 Sample.xlsm file.

Building navigation buttons

The most common use of macros is in navigation. Workbooks that have many worksheets or tabs can be frustrating to navigate. To help your audience, you can create a switchboard, like the one shown in Figure 1-10. When users click the Example 1 button, for example, they're taken to the Example 1 sheet.

image

Figure 1-10: Use macros to build buttons that help users navigate your reports.

Creating a macro to navigate to a sheet is quite simple:

  1. Start at the sheet that will become your starting point.
  2. Start recording a macro.
  3. While recording, click the destination sheet (the sheet this macro will navigate to).
  4. Stop recording.
  5. Add a button form control on your starting point and Assign the macro to a button by selecting your newly recorded macro in the Assign Macro dialog box.

tip Excel has a built-in hyperlink feature, which enables you to convert the contents of a cell to a hyperlink that links to another location. That location can be a separate Excel workbook, a website, or another tab in the current workbook. Although creating a hyperlink may be easier than setting up a macro, you can’t apply a hyperlink to a form control (such as a button). Instead of a button, you use text to let users know where they’ll go when they click the link.

Dynamically rearranging pivot table data

In the example illustrated in Figure 1-11, macros allow a user to change the perspective of the chart simply by selecting one of the buttons shown.

image

Figure 1-11: Users can change the perspective.

Figure 1-12 reveals that the chart is actually a pivot chart tied to a pivot table. The recorded macros assigned to the buttons do nothing more than rearrange the pivot table to slice the data using various pivot fields.

image

Figure 1-12: The macros behind the buttons rearrange the data fields in a pivot table.

Here are the high-level steps needed to create this type of setup:

  1. Create your pivot table and a pivot chart.
  2. Start recording a macro.
  3. Move a pivot field from one area of the pivot table to the other, and then stop recording the macro.
  4. Record another macro to move the data field back to its original position.
  5. Assign each macro to a separate button.

You can fire your new macros in turn to see your pivot field dynamically move back and forth.

Offering one-touch reporting options

The last two examples demonstrate that you can record any action that you find of value. That is, if you think users would appreciate a certain feature being automated for them, why not record a macro to do so?

In Figure 1-13, note that users can filter the pivot table for the top or bottom 20 customers. Because the steps for this filter have been recorded, users save time and effort and can benefit from this functionality without having to know the steps involved. Also, recording a specific action enables you to manage risk because your users will interact with your reports in a method that you've developed and tested.

image

Figure 1-13: Prerecorded views let users benefit from advanced features.

Figure 1-14 demonstrates how you can give your audience a quick and easy way to see the same data on different charts. Don’t laugh too quickly at the apparent uselessness of this example. It’s not uncommon to be asked to see the same data different ways.

image

Figure 1-14: Give your users a choice in how they view data.

Instead of taking up real estate with multiple charts, just record a macro that changes the chart type. Your clients will be able to switch views to their heart’s content.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.222.161.187