Microsoft Office Excel 2007 Visual Basic for Application Step by Step - Chapter 1

This article is an excerpt from Microsoft Office Excel 2007 Visual Basic for Applications Step by Step by Reed Jacobson, from Microsoft Press (ISBN 9780735624023, copyright Microsoft Press 2008, all rights reserved). No part of these chapters may be reproduced, stored in a retrieval system, or transmitted in any form or by any means—electronic, electrostatic, mechanical, photocopying, recording, or otherwise—without the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.

Contents

• What’s the Difference Between VBA and a Macro?

• Creating a Simple Macro

• Changing Multiple Properties at Once

• Manipulating Recorded Properties

• Recording Methods in a Macro

• Trusting Macro-Enabled Workbooks

• Key Points

In this chapter, you will learn to:

• Record and run a macro.

• Understand and edit simple recorded macros.

• Run a macro by using a shortcut key.

• Manage macro security.

A couple of weeks ago, I misplaced the remote control to my digital video recorder. It was awful. I was trying to watch a perfectly legal, fair-use, time-shifted episode of Desperate Housewives, but I couldn’t because the navigation controls are built into the remote control. Fortunately, when I was getting a fresh bag of popcorn from a cupboard in the kitchen a few days later, I discovered where I had set the remote. I’m so happy that I can again watch pre-recorded television. Not to mention changing the channel without having to stand up.

Microsoft Visual Basic for Applications (VBA) is the remote control for Microsoft Office Excel 2007. Sure, you can use Excel without ever using VBA, but the VBA remote control makes Excel more convenient to use. It also allows you to take advantage of features that can’t be accessed through the standard user interface. Once you become acquainted with VBA, you’ll wonder how you ever got along without it.

Important
Before you complete this chapter, you need to install the practice files from the book’s companion CD to their default locations. See “Using the Book’s CD” on page xv for more information.

USE the Budget.xlsx workbook. This practice file is located in the Documents\MSP\ExcelVBA07SBS folder. The workbook contains a single worksheet, named Budget. This worksheet includes a projected month-to-month budget for the year 2007.

BE SURE TO save the Budget.xlsx workbook with the name Chapter01 in a new folder called Work. You put the workbook in a new folder so that you can be absolutely certain that nothing is in the folder except files that you put there. Later, this will allow you to trust any macro workbooks in that folder.

OPEN the Chapter01.xlsx workbook.

What’s the Difference Between VBA and a Macro?

If VBA is the remote control for Excel, then what is a macro? And what is the difference between VBA and a macro? It’s all very confusing. In essence, a macro is a computer program that gives automated instructions to the computer. The original macros were a way to use a few characters to represent a lot of instructions. They were called macros because the output was much bigger than the input.

In fact, the first spreadsheet macro programs really did just expand a short string of characters into a long set of actions. They were just shortcuts for the user interface commands. For example, if in the user interface you typed R (for “Range”), N (for “Name”), and C (for “Create”), you would enter RNC into the macro to automate the process. This approach was intuitive, but it also had inherent weaknesses. Not only were keystroke macros difficult to read, but they also didn’t adapt well to a graphical user interface. What keystrokes would you use to represent dragging a rectangle with the mouse? They also made it difficult to enhance the user interface, because any changes to the menu structure would cause any previously created macros to fail.

To solve these problems, the early versions of Excel contained a new type of programming language—one that was independent of the user interface command names. For example, in Microsoft Excel version 4 (Excel 2007 is version 12), you could copy a range in at least three different ways: press Ctrl+C, click the Copy button on the Standard toolbar, or click Copy on the Edit menu. All of these methods were represented by the same instruction =COPY(). This new programming language was not technically a macro language in the old sense of expanding a few characters into a sequence of instructions. It was technically a set of functions, much like the functions used to perform tasks in spreadsheet cells. But saying “You can now automate simple tasks by writing custom programs based on specialized spreadsheet functions” sounded scary, so the Excel team continued to refer to the custom programs as macros. The word macro came to mean a program that users can write by themselves.

Excel’s early function-based macros were a major improvement over keystroke macros, but they still had two big drawbacks. First, Excel macros were very specific to Excel—the functions looked too much like spreadsheet formulas to be able to adapt to other applications, such as Microsoft Office Word. Second, the number of functions increased with each new version of Microsoft Office, and there was no good way to organize or group the thousands of possibilities.

To solve the first limitation—that function-based macros are specific to Excel—Microsoft introduced Visual Basic for Applications, or VBA, starting with Microsoft Excel version 5. VBA acts as a general-purpose language that is independent of the application. Suddenly, anyone who knows how to work with any version of Microsoft Visual Basic has a big head start in automating Excel, and anyone who learns how to write Excel macros in VBA can transfer that knowledge to other types of Visual Basic programming. In addition, although Excel was the first major application to use VBA, VBA is not tied directly to Excel; it works just as well with other VBA-enabled applications, such as Word and Microsoft Office PowerPoint.

To solve the second limitation of function-based macros—that there are too many commands to manage effectively—VBA works with an object model. The term object model sounds pretty scary, but it’s really just a logical way to organize all the commands you can carry out in an application. In an object model, each different part of the application—for example, a workbook, a range, or a point on a chart—becomes an object, and each object has its own list of functions. You’ll learn more later about what an object is and how objects relate to functions, but the point is that the object model organizes all the millions of possible commands around how each command is used—for example, you copy and paste a range of cells, but you don’t copy and paste points on a chart.

Because of the object model, VBA doesn’t need any special access to the internals of Excel. Rather, Excel exposes its capabilities to the outside world by means of the object model, and VBA talks to the object model.

Figure 1

This means that an Excel VBA macro can control not only Excel, but also any application that provides an object model. All Microsoft Office applications, and several other Microsoft and non-Microsoft applications, provide appropriate object models.

Figure 2

The VBA that comes with Excel isn’t the only language that can communicate with the object model. Any language that supports Automation can control Excel. You can control Excel not only with the VBA hosted by Excel, but also with the VBA hosted by Word, with Microsoft Visual Basic version 6, or even with a language such as C++. With a simple translation layer, you can also talk to the Excel object model from Microsoft .NET applications written in C# or Microsoft Visual Basic .NET.

Figure 3

The object model exposes Excel’s capabilities to VBA, but more importantly, it also exposes Excel’s capabilities to you. When you know how to read and interpret an object model, you discover new features and quickly figure out how to put them to work. The best way to learn how VBA communicates with Excel objects is to create some simple macros by using the macro recorder. Eventually, however, you’ll want to move beyond the limitations of the macro recorder.

Creating a Simple Macro

Excel has a large collection of tools conveniently available on the Ribbon or on shortcut menus. Sometimes, however, a built-in tool doesn’t work quite the way you want. Creating a macro to enhance a built-in tool is a good way to get started with writing macros.

Format Currency by Using a Built-In Tool

On the Home tab of the Ribbon is a Number Format list from which you can choose any of several built-in formats. You can use the list to apply a Currency format to the selected cells.

1. In the Chapter01 workbook, select cells D3:F4 on the Budget worksheet.

2. On the Home tab of the Ribbon, click the Number Format arrow, and click Currency.

   Figure 4

   

   Excel reformats the selected cells as Currency with two decimal places.

   Figure 5

   

Record a Macro to Format Currency

The standard Currency format has two decimal places. Two decimal places may be appropriate in something like a checkbook, where you know the exact amount you spent. But a budget worksheet contains estimates, and displaying estimated values to the penny seems silly. You can create a macro that formats the currently selected cells as Currency, but with no decimal places.

On the status bar, immediately to the right of the cell mode (Ready) indicator, in the bottom-left corner of the Excel window, is a button that will start the macro recorder. When no macro is currently recording, you can think of this as a Record Macro button. If the Record Macro button is not visible in the status bar, right-click the status bar, select the check box for Macro Recording, and click OK.

Tip
The Record Macro button is very close, both in appearance and in location, to the Insert Worksheet button, so be careful to click the right one.

1. On the Budget worksheet, select cells D7:F8.

2. On the status bar, click the Record Macro button.

   Figure 6

   

3. Replace the default macro name with FormatCurrency, and then click OK.

   A macro name must begin with a letter, and it can contain uppercase and lowercase letters, numbers, and underscores, but no spaces or other special characters.

   Figure 7

   

   The only apparent change is that the icon on the Record Macro button changes to a square, so you can now think of it as a Stop Recording button, which happens to be what the tool tip says it is. The Stop Recording button is like an “On the Air” button at a TV studio: You’re recording.

4. Right-click in the selected range, and click Format Cells. If necessary, click the Number tab in the Format Cells dialog box.

5. Select Currency from the Category list.

6. Replace the value in the Decimal Places box with 0.

   Figure 8

   

7. Select Custom from the Category list, and look in the Type box.

   The characters $#,##0 represent a currency format with no decimal places. This is the format that gets applied to the selected cells.

   See Also To learn more about format strings, click the Office Excel Help Button and type “number format codes” in the search box.

8. Click OK to format the selected cells as currency without decimal places.

9. On the status bar, click the Stop Recording button.

   Figure 9

   

That’s it. You have recorded a macro to format a selection with the currency format you want.

Run the Macro

Now you probably want to try the macro to see how it works.

1. On the Budget worksheet, select cells D9:F10.

2. On the View tab of the Ribbon, click the Macros button at the right end of the tab.

   Figure 10

   

3. Select the FormatCurrency macro in the list, and click Run.

It worked! You ran your first macro, and the macro applied your customized currency format to the selected cells.

Tip

In the Excel Options dialog box, on the Popular page, is a check box labeled Show Developer Tab In The Ribbon. You can enable the Developer tab if you want, but it doesn’t really make it any easier to write macros. The Developer tab contains three sections: The Code section includes buttons that are already easily available—either as the Record Macro button on the status bar or as the Macros button on the View tab of the Ribbon. The Controls section contains items that are very useful when adding controls to a worksheet. The XML section has nothing to do with creating macros.

Assign a Shortcut Key to the Macro

Running a macro from the Macro dialog box isn’t much easier than directly assigning the number format. To make the macro easy to run, you can use a shortcut key.

1. On the View tab of the Ribbon, click the Macros arrow, and click View Macros.

2. Select the FormatCurrency macro in the list, and then click the Options button.

   The Macro Options dialog box allows you to change the macro’s shortcut key assignment and its description. Other than the fact that you can’t change the name, this dialog box looks very similar to the Record Macro dialog box, and in fact you can also assign a shortcut key at the time you first record a macro.

3. With the box below Shortcut Key selected, press Shift+C.

   As you can see by the label next to the box, this assigns Ctrl+Shift+C as the shortcut key.

   Figure 11

   

   Important
   Excel uses many Ctrl key combinations as built-in shortcuts. For example, Ctrl+C is Copy and Ctrl+Z is Undo. If you assign one of these shortcuts to your macro, pressing the shortcut key combination runs your macro rather than the built-in command. If you always use Ctrl+Shift key combinations when you assign shortcut keys to your macros, you’ll be less likely to override a built-in shortcut.

4. Click OK to return to the Macro dialog box, and then click Cancel to return to the worksheet.

5. Select cells D11:F12, and press Ctrl+Shift+C to run the macro.

Now you’ve successfully recorded, run, and enhanced a macro—all without seeing the macro itself. Maybe you’d like to actually see what you created.

Look at the Macro

The macro is hidden away in the workbook, and you need to open the Visual Basic editor to see it.

1. On the View tab of the Ribbon, click the Macros arrow, and click View Macros.

2. Click FormatCurrency, and then click Edit.

   The Visual Basic editor window opens. The Visual Basic editor appears to be a separate program, but it is “owned” by Excel—that is, if you quit Excel, the editor automatically shuts down. Inside the Visual Basic editor, a window captioned Module1 appears as well.

3. Maximize the Module1 window so that it fills the editor, and then resize the editor window so that you can see the Excel workbook in the background.

4. If any other windows are visible in the Visual Basic editor, close them now.

   Figure 12

   

   The window has the caption Module1. A module is the place where the recorder stores macros. Your macro is in the Module1 module. The macro looks like this:

   Sub FormatCurrency()
      '
      ' FormatCurrency Macro
      '
      '
      Selection.NumberFormat = "$#,##0"
   End Sub
   

The four lines that start with apostrophes are comments. An apostrophe tells Visual Basic to ignore all subsequent text on the line. (The blank line among the comments, without even an apostrophe, is where the recorder would have put the shortcut key combination if you had assigned it when you recorded the macro.) The recorder inserts the comments to remind you to add comments as you write a macro. You can add to them, change them, or delete them as you want without changing how the macro runs. Comments appear in green to help you distinguish them from statements. Everything in Visual Basic that is not a comment is a statement. A statement tells Visual Basic what to do.

The first statement in the macro begins with Sub, followed by the name of the macro. This statement tells Visual Basic to begin a new macro. Perhaps the word Sub is used because a macro is typically hidden, or out of sight, like a submarine. Or perhaps it is because macro-writers are sort of like hackers, and they are known to be subversive. Or maybe Sub is just used for boring historical reasons. The final statement of the macro is End Sub. This statement tells Visual Basic to come back to the surface.

All the statements between Sub and End Sub form the body of the macro. These are the statements that do the real work. The fi rst (and only) statement in the body of the FormatCurrency macro begins with Selection.NumberFormat.

The word Selection refers to some thing in Excel—in this case, the currently selected range of cells. The thing is called an object. Remember that objects are basically just a way of logically organizing the million of commands that are possible in Excel. Specifying Selection as the object says that the following instruction is going to have to do with a range of cells.

The word NumberFormat refers to an attribute—or property—of the range of cells. One way that Excel carries out an action is by assigning a new value to a property. In this case, it assigns the new format string to the NumberFormat property. In essence, the statement says, “Hey Excel, I’ve got a range here. Change the number format to look like this custom currency format, OK?”

This macro assumes that the current selection is a range of cells. If you happen to select a graphical shape and then run the macro, it fails. That’s because when the macro says, “Hey Excel, I’ve got a rectangle here. Change the number format, OK?” Excel says, “No way! It doesn’t make sense to change the number format of a rectangle, stupid!” Of course, it doesn't use those words. It says “Object doesn’t support this property or method,” but the meaning is about the same. Later, in Chapter 8, “Extend Excel and Visual Basic,” you’ll learn how to fix a macro so that you can give it to friends without the risk of having Excel tell them that they’re stupid.

Save the Macro Workbook

If you’ve been following the instructions very carefully, you have not yet saved your workbook. If you have tried to save your workbook, you’ve received an ominous message about how a Visual Basic project cannot be saved in a macro-free workbook. This is a little bit annoying, but it is a nice new feature of the 2007 Microsoft Office release. As I’m sure you know, evil people have figured out that they can use Excel workbooks and Word documents to spread viruses and other infestations. I distinctly remember the time when I spread a virus all over my company because I got a workbook from a “trusted” colleague and so I told Excel it was OK to enable the macro. The problem was not that my colleague was untrustworthy; it was that he didn’t even know that the workbook he sent me had a virus macro in it.

So now, in the 2007 Office release, a standard workbook is macro-free: it is guaranteed not to have any macros in it. There’s no way it can spread a virus. If you do put a macro into a workbook, you must save it as a macro-enabled workbook.

Important

Macro-free workbooks happen to have the extension .xlsx, and macro-enabled workbooks have the extension .xlsm, but the file extension is not what matters. Macros are stored in a special section inside the workbook. Excel can easily tell whether the workbook contains a macro by looking at the content list of the workbook file; it doesn’t even have to open the part of the workbook that contains the macro and could therefore be potentially dangerous. If you change the extension of a macro-enabled workbook to .xlsx, you just get an error when you try to open the workbook.

1. Click the Microsoft Office Button, point to the Save As arrow, click Excel Macro-Enabled Workbook, and click Save.

   This saves the workbook as Chapter01.xlsm.

2. Close the workbook, and use the Recent Files list on the Office menu to open it again.

   The file opens, but there’s an alert message under the Ribbon that warns you that the macros are disabled for your safety.

   Figure 13

   

3. On the security bar, click the Options button.

4. In the Microsoft Office Security Options dialog box, select Enable This Content.

   Figure 14

   

5. Click OK to enable the macros and make the security bar disappear.

6. Select cells D13:F13 and press Ctrl+Shift+C to format them as customize currency to make sure that the macro you created does work.

For your protection, Excel requires you to enable the macros each time you open the workbook. Even if you don’t enable the macros, but then re-save the workbook, the macros don’t go away. The next time you open the workbook you will still get the option to enable the macros. At the end of this chapter, you’ll learn how to trust specific macro-enabled workbooks so that you don’t have to enable the macros each time.

Changing Multiple Properties at Once

Now that you know how to save your work properly, let’s get back to creating macros. The FormatCurrency macro you created earlier contains a statement that changes a single property (the number format) of a single object (the currently selected range of cells). Assigning a value to the property changes the object. Assigning a value to a property is a common way to carry out an action in VBA, and the recorder will often create a similar-looking statement when you record an action. But sometimes when you record a single action, the recorder assigns values to multiple properties all at once.

Create Sidebar Headings with a Command

A sidebar heading is a heading that is on the side of a group of rows, rather than at the top of a group of columns. Most of the time, headings are at the top, and therefore Excel has a Ribbon button that can merge and center several cells in a horizontal row. It’s called the Merge And Center button and is in the Alignment group of the Home tab.

Figure 15

But there is not a single button for creating sidebar headings. The Merge Cells option on the Merge And Center button list does allow you to merge vertically, and the Rotate Text Up option on the Orientation list allows you to rotate the text up, but there is not a command anywhere on the Ribbon that lets you create a sidebar heading in a single step. You can create a one-step sidebar heading action by recording a macro.

To better understand what’s required, first walk through the steps to create the target format. Using the Alignment dialog box allows you to set both the text rotation and the cell merge at the same time.

1. Activate the Budget window.

2. Select the range A6:A12.

   The label Variable is at the top of the selected range.

   Figure 16

   

3. Right-click in the selection, and click Format Cells.

4. In the Format Cells dialog box, click the Alignment tab.

   The Alignment tab has several controls that control alignment, wrapping, orientation angle, text direction, shrinking, and merging.

5. Click the Merge Cells check box, and drag the red diamond in the orientation control to the top of the arc to set the orientation to 90 degrees.

   Figure 17

   

6. Click OK to merge and tilt the label.

   Figure 18

   

A sidebar heading gives you some interesting layout opportunities. You can now record a macro to make it easy to create one whenever you like.

Record a Macro to Merge Cells Vertically

Rearrange your windows as necessary so that you can see both the Module1 window and the Excel window.

Tip
To rearrange the windows, minimize all the applications you have open except Excel and the Visual Basic editor, and activate Excel. Right-click the taskbar, and on the shortcut menu, click Show Windows Side By Side. (In Microsoft Windows XP, the command is Tile Windows Horizontally).

1. Select the range A15:A20, and then click the Record Macro button.

2. In the Record Macro dialog box, replace the default macro name with SideBarHeading, replace the default description with Merge and Rotate cells Vertically, and press Shift+S to set Ctrl+Shift+S as the shortcut key.

   Important
   If you assign the same shortcut key to two macros, the one that appears first in the Run Macro list is the one that runs. Also, a shortcut key is valid only while the workbook containing the macro is open.

3. Click OK.

   In the module window, you can see that the recorder immediately inserts the comment lines, the keyboard shortcut, and the Sub and End Sub lines into the macro.

   Tip

   The first time you record a macro, Excel creates a new module. Each time you record an additional macro, Excel adds the new macro to the end of the same module. When you close and reopen the workbook, the macro recorder starts putting macros in a new module. There is no way for you to control where the recorder puts a new macro. Having macros in multiple modules shouldn’t be a problem. When you use the Macro dialog box to select and edit a macro, it automatically takes you to the appropriate module.

4. Right–click the selection, and then click Format Cells.

5. In the Format Cells dialog box on the Alignment tab, select the Merge Cells check box, set the orientation to 90 degrees, and click OK.

   The recorder inserts several lines into the macro all at once.

6. Click the Stop Recording button, and save the Chapter01 workbook.

The new macro in the Module1 window looks like this:

Sub SideBarHeading()
   '
   ' SideBarHeading Macro
   ' Merge and Rotate cells Vertically
   '
   ' Keyboard Shortcut: Ctrl+Shift+S
   '
   With Selection
      .HorizontalAlignment = xlGeneral
      .VerticalAlignment = xlBottom
      .WrapText = False
      .Orientation = 90
      .AddIndent = False
      .IndentLevel = 0
      .ShrinkToFit = False
      .ReadingOrder = xlContext
      .MergeCells = True
   End With
End Sub

The macro shows nine different properties that relate to cell alignment. Each property name is followed by an equal sign. These properties correspond to the controls you saw on the Alignment tab of the Format Cells dialog box.

All these properties pertain to the same object—the currently selected range of cells. This is the same object that the FormatCurrency macro uses, but in that macro, the property name comes right after the object, separated only by a period. This SideBarHeading macro is different because each property name just hangs there, preceded only by a dangling period.

The object for all these properties appears in a statement that begins with the word With. The group of statements from With to End With is called a With structure. Inside a With structure, you can put a dangling period in front of a property, and VBA just pretends that the object from the With statement is there. The macro recorder does that all the time, especially when you use a dialog box that has a lot of controls in it. A With structure makes the code easier to read because you can tell instantly that all the properties relate to the same object—in this case, the object is the currently selected range.

Eliminate Unnecessary Lines from the Macro

When you record a macro and make a change in a dialog box with a lot of controls, the recorder usually puts all the possible properties into the macro, even if you changed the values of only one or two of them. You can make your macro easier to understand if you eliminate unnecessary property assignments.

In the SideBarHeading macro, the only properties you need to change are Orientation and MergeCells, so you can delete all the other statements from the With structure.

1. Activate the Visual Basic editor window, and click as far to the left of the HorizontalAlignment statement as you can within the editor window. (Your mouse pointer should turn into a white arrow pointing northeast before you click.)

   This action selects the entire line, including the indent that precedes the text.

   Tip
   If you see a red circle in the margin after you click, you clicked too far into the gray area (and you need to learn the difference between North-East and North-West). Click in the red circle to remove it, and try again.

2. Press the Delete key.

3. Repeat Steps 1 and 2 for each property except Orientation and MergeCells. If you delete too much, click the Undo button to restore what you deleted.

   Figure 19

   

   The simplified macro (ignoring the comment lines, which you can delete if you want) should look like this:

   Sub SideBarHeading()
      With Selection
         .Orientation = 90
         .MergeCells = True
      End With
   End Sub
   

4. Activate the Excel window, and select cells A25:A30.

5. Press Ctrl+Shift+M.

   The macro adjusts the label. You can now create side-bar headings whenever you like (as long as this workbook is open).

6. Save the Chapter01 workbook.

Now you’ve not only recorded a macro, but you’ve also deleted parts of it—and it still works. Next you’ll record a macro and make additions to it.

Manipulating Recorded Properties

In a recorded macro, you can recognize a statement that assigns a value to a property because it always has an equal sign in the middle. Once you recognize a property in a recorded macro, you can easily change the way the macro works.

Record a Macro to Remove Window Elements

A typical Excel worksheet has several features that help you use the spreadsheet—the light gray gridlines that mark the boundaries of the cells, the row and column headings, and the formula bar. Most of the time, these are extremely helpful, but sometimes, you might want to remove them so that you can have a “clean” display. The Ribbon in Excel 2007 makes it very easy to turn each of these elements on or off, but you can create a macro that changes all three elements at the same time.

Arrange the workbook and macro windows so that you can see them both.

1. Click the Record Macro button.

2. Replace the default macro name with CleanDisplay, and click OK.

3. On the View tab of the Ribbon, clear the check boxes for Gridlines, Headings. and Formula Bar.

   All three elements will disappear from the display.

4. Click the Stop Recording button, and then save the Chapter01 workbook.

5. Click the Macros button, select CleanDisplay, and then click Edit to look at the resulting code.

   Ignoring the comment lines, here’s what it looks like:

   Sub CleanDisplay()
      ActiveWindow.DisplayGridlines = False
      ActiveWindow.DisplayHeadings = False
      Application.DisplayFormulaBar = False
   End Sub
   

The statements in this macro have a similar look to the one in the FormatCurrency macro. You can read the first one as “Let ‘False’ be the Display Gridlines state of the active window.” This time you’re not changing the selected cells but rather the active window. The second statement also changes something about the active window, but the third one changes something called the Application. In each case, you’re changing the property of an object. A workbook window has different properties than a range of cells, and they have different properties than the application. The object is really just a way to group the properties. There’s no such thing as a formula bar for a range of cells, and a single worksheet can have multiple windows, each with its own gridline and heading settings.

Run the Macro from the Visual Basic Editor

By looking at the macro, you can probably guess what you have to do to make it turn on these display features.

1. Replace each of the three occurrences of the word False with the word True.

   You can’t use a shortcut key while you’re in the Visual Basic editor, but the editor has its own shortcut for running whatever macro you’re currently editing.

2. Press F5 to run the macro

   The gridlines reappear in the current Excel worksheet. Pressing F5 from the Visual Basic editor is a fast way to run a macro while you’re testing it.

   Tip
   In addition to using F5 to run the current macro in the Visual Basic editor, you can click the Run Sub/UserForm button in the toolbar. Also, if you want to be able to use the Macro dialog box to select a macro to run, click outside any macro before you press F5.

3. Press Ctrl+Z three times to change all the True values back to False.

4. Press F8 to run the macro.

   The Sub statement turns yellow, but that’s all. F8 steps through the macro, running one statement at a time. This lets you watch what the macro is doing.

5. Press F8 again to highlight the first statement in the body of the macro, but then put the mouse pointer over the word DisplayGridlines in the yellow statement.

   You should see that the current state of the property is True.

6. Press F8 again to execute the statement (and highlight the next one). Again put the mouse pointer over the word DisplayGridlines in the statement that just executed.

   You should see that the current state of the property is now False, because the macro just changed it.

7. Press F5 to run the rest of the macro.

One of the really cool things about a property is that you can use the exact same words to find out the current value of the property. This allows you to change your macro into one that toggles the value of the properties.

Use a Macro to Toggle the Value of a Property

If a property uses True and False as its values, you can toggle the value by using the VBA keyword Not. It’s sort of like sarcastically saying, “That was a really funny joke—not!” You first ask Excel for the current value, and then you swap the value as you assign the value back to the property. Here’s how:

1. Select ActiveWindow.DisplayGridlines, and copy it.

2. Select the word after the equal sign—it’s probably the word False. Replace that word with Not, type a space, and then paste in the words you copied.

   The resulting statement is

   ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines

   The Visual Basic keyword Not turns the value True into False and False into True.

3. Repeat the process for the other two statements: copy everything to the left of the equal sign, and paste it after the equal sign, replacing the current constant and inserting the word Not.

4. Change the name of the macro from CleanDisplay to ToggleCleanDisplay to better reflect the macro’s new capabilities.

   This is what the macro should look like now:

   Sub ToggleCleanDisplay()
   ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines
   ActiveWindow.DisplayHeadings = Not ActiveWindow.DisplayHeadings
   Application.DisplayFormulaBar = Not Application.DisplayFormulaBar
   End Sub
   

5. Click the Save button in the Visual Basic editor.

   Figure 20

   

   This saves the workbook that contains the macros.

6. Click within the ToggleGrid macro, and then press F5 several times to test the macro.

The macro reads the old value of the property, changes it to the opposite with the keyword Not, and assigns the newly inverted value back to the property. By now, you should see a pattern to creating a simple convenience macro: First try out an action interactively. Once you know how to do the task, start the recorder. Do the task with the recorder on, and then stop the recorder and edit the recorded macro if necessary.

Eliminate Repeated Objects in a Recorded Macro

You may notice that your macro now contains the word ActiveWindow four different times. In each case, it is an object followed by a property. Based on what you’ve seen the macro recorder do, can you think of a way to only have to say ActiveWindow once? You move the object into a With structure, then let each property that needs the object begin with a dangling period, and then end the structure when you’re finished. Here are the steps:

1. Put the insertion point just before the first occurrence of the word ActiveWindow, and type With, followed by a space.

2. Put the insertion point just before the first period and press Enter, creating a new line.

3. Delete all three remaining occurrences of the word ActiveWindow, but always leave the dangling period.

4. Just before the word Application, insert a new line, and type End With.

5. Press Tab to increase indentation, and Backspace to decrease indentation so that it is clear which statements are part of the structure. The finished structure should look like this:

   With ActiveWindow
      .DisplayGridlines = Not .DisplayGridlines
      .DisplayHeadings = Not .DisplayHeadings
   End With
   

6. Press F5 to test your changes.

   It should toggle the same as before.

7. Create a With structure for the Application object, even though it occurs only twice—and both are in the same statement.

   The finished structure should look like this:

   With Application
      .DisplayFormulaBar = Not.DisplayFormulaBar
   End With
   

8. Press F5 to test your changes, and save the Chapter01 workbook.

Properties are a very powerful tool. When you detect a property assignment statementin a recorded macro, you can probably identify great opportunities for enhancing themacro.

Run a Macro from the Quick Access Toolbar

Keyboard shortcuts are convenient, but they are hard to remember. If you have a macro that you want to be able to run easily without having to remember a keyboard shortcut, you can add a custom button to Excel’s Quick Access Toolbar. A particularly nice feature of the Quick Access Toolbar is that you can add a button that appears only when the workbook containing the macro is active. This helps you avoid cluttering up the Quick Access Toolbar with unusable buttons.

1. In the main Excel window, right-click anywhere in the Quick Access Toolbar (the row of buttons next to the Microsoft Office Button), and then click Customize Quick Access Toolbar.

   This displays the Customize tab of the Excel Options dialog box.

2. In the Customize Quick Access Toolbar list, select For Chapter01.xlsm.

   This will make the button visible only when the Chapter01 workbook is active.

3. In the Choose Commands From list, select Macros, select ToggleCleanDisplay,and then click Add.

   The macro name moves to the list on the right, but it shows a generic icon thatmay not help you remember what the macro does.

   Figure 21

   

4. Select the ToggleCleanDisplay macro in the list on the right, and then click Modify.

5. In the Modify Button dialog box, select the white box icon (to symbolize a cleandisplay), and then change the Display Name to Toggle Clean Display (which makesthe tool tip easier to read without affecting the actual macro name).

   figure 22

   

6. Click OK twice to close both dialog boxes and return to Excel, which now has a newToggle Clean Macro button in the Quick Access Toolbar.

7. To try out the new Toggle Clean Display button, add a new workbook (whichwill make the button disappear), and then close it (which will make the buttonreappear).

   Figure 23

   

Recording Methods in a Macro

So far, in all the macros you’ve created, the macro recorder has used property assignments to carry out actions. Sometimes assigning a value to a property is not the best way to carry out an action. For example, sometimes it is critical to make multiple changes simultaneously, and assigning properties is not the best way to keep everything synchronized. The ToggleCleanDisplay macro is a good example of that: because each property is independent of the others, it’s very easy to get the toggle state inconsistent. Consequently, the Excel object model allows for a different way to carry out an action. This second method is called a—method. You can watch a method at work by using the macro recorder

Convert a Formula to a Value by Using Menu Commands

For example, suppose that you want to freeze the formulas of some cells in the Budget worksheet at their current values. First change the formulas to values using menu commands—watching carefully how Excel does or does not prompt for additional information—and then create a macro that can change formulas to values for any arbitrary selection.

1. Start by activating the Budget window, and then select cell D4.

   Figure 24

   

   Notice the formula in the formula bar: =D3-D54.

2. Right-click the cell, and click the Copy command.

3. Right-click the cell again, and click the Paste Special command.

   The Paste Special dialog box appears. This dialog box has four independent parts: the Paste group, the Operation group, the Skip Blanks check box, and the Transpose check box. You can choose only one option within each part, so you have four distinct choices you can make within this dialog box.

   Figure 25

   

4. Select the Values option from the Paste group, and click OK.

   Excel pastes the value from the cell over the top of the existing cell, eliminating the formula that was in it. The moving border is still visible around the cell, indicating that you could paste the value again somewhere else.

5. Press the Esc key to get out of copy mode and clear the moving border.

   In the formula bar, cell D4 now contains the value 28094.9.

Copying and pasting cell values are actions that don’t lend themselves to simple property assignments. When you execute the Copy command, what property would that be? Notice also that when you copy, you don’t see a dialog box. Excel simply puts a moving border around the cells; you don’t tell Excel how to do the copying.

When you execute the Paste Special command, on the other hand, you do see a dialog box. Excel needs additional information about exactly how you want the paste to behave. Think back to the Alignment tab of the Cell Format dialog box. That dialog box had multiple options, but the ones in Paste Special are different, because they interact with each other—if you select the Values option and the Add option and the Skip Blanks option allat the same time, they combine together to affect the one action of pasting. Given the interactive nature of all the dialog box controls, how would you do all that with a simple property assignment? When you record the same process in a macro, you’ll see how a method looks different from a property.

Convert a Formula to a Value by Using a Macro

You can learn about how a macro uses methods by recording a macro that converts formulas to values. As you look at the recorded macro, you can compare the statements for actions that display or don’t display a dialog box.

1. On the Budget worksheet, select cell E4.

   Notice the formula in the formula bar: =E3-E54.

2. Click Record Macro, replace the default name with ConvertToValues, press Shift+V to set the shortcut key to Ctrl+Shift+V, and then click OK.

3. Right-click cell E4, and click Copy.

4. Right-click cell E4 again, then click Paste Special, click the Values option, and then click OK.

5. Press the Esc key to remove the moving border.

6. Click the Stop Recording button, and save the Chapter01 workbook.

   In the formula bar, cell E4 now contains the value 28332.9.

7. Switch to the Visual Basic editor to look at the recorded macro.

Tip
If you closed the editor, go to the View tab of the Ribbon, click Macros, select ConvertToValue, and then click Edit.

Ignoring comments, the macro looks like this:

Sub ConvertToValues()
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

The basic structure of this macro is the same as that of the other macros you’ve seen in this chapter: It starts with a Sub and ends with an End Sub, and has a bunch of statements in the middle. Also, the fi nal statement in the body of the macro uses a familiar property assignment to set the value of the CutCopyMode property. This is how Excel interprets pressing Esc to remove the moving border around the cells.

The two statements that begin with Selection, however, are something new. Neither has a simple equal sign in it.

The statement Selection.Copy has two words, separated by a period. A word followed by a period is probably an object, and that’s exactly what this is: a range of cells object. The word Copy, however, isn’t a property; it’s a method. That’s why it doesn’t have an equal sign after it. You don’t assign anything to Copy; you just do it. Remember that the object is really just a way of grouping available commands. You can copy a range of cells, but you can’t copy, say, a workbook window, so there’s no such thing as ActiveWindow.Copy.

When you execute the Copy command in Excel, you don’t see a dialog box asking you for any extra information. In the same way, when you use the Copy method in a macro, you don’t give any extra information to the method.

The next statement begins with Selection.PasteSpecial. Once again, the word followed by a period—Selection—refers to an object. Once again, the word that follows the period—PasteSpecial—does not have a simple equal sign after it, so it’s not a property. It’s anothermethod.

When you execute the Paste Special command in Excel, you see a dialog box that lets you give extra information to the command. In the same way, when you use the PasteSpecial method in a macro, you give the same extra information to the method. The extra pieces of information you give to a method are called arguments.

Using a method is like giving instructions to your nine-year-old son. With some instructions—such as “Come eat”—you don’t have to give any extra information. With other instructions—such as “Go to the store for me”—you do have give more instructions: what to buy (milk), how to get there (on your bike), and when to come home (immediately). Giving an extra piece of information to your son is like giving an extra piece of information to an Excel method. In both cases, you end up with an argument.

The four arguments you use with the PasteSpecial method correspond exactly to the four distinct parts of the Paste Special dialog box. Each argument even has a name that matches the caption in the dialog box: Paste, Operation, SkipBlanks, and Transpose. When you use an argument, you don’t actually have to include the argument name. This statement would function the same as

Selection.PasteSpecial xlPasteValues, xlNone, False, False

The names just make it easier to read, so the macro recorder includes them. If you do use a name for an argument, you put a colon-equal sign (:=) between the argument name and its value. The colon-equal sign may include an equal sign, but it’s easy to tell them apart because the equal sign (used in a property assignment) always has a space on both sides.

Make a Long Statement More Readable

When a statement in a macro gets to be longer than about 70 characters, the macro recorder inserts a space and an underscore ( _) after a convenient word and continues the statement on the next line. The underscore tells the macro that it should treat the second line as part of the same statement. You can manually break long statements into several lines, as long as you break the line after a space. You can also indent related lines with tabs to make the macro easier to read.

1. In the ConvertToValues macro, put each argument of the PasteSpecial statement on a separate line, using a space and an underscore character at the end of each line except the last.

   Sub ConvertToValues()
   Selection.Copy
   Selection.PasteSpecial _
   Paste:=xlValues, _
   Operation:=xlNone, _
   SkipBlanks:=False, _
   Transpose:=False
   Application.CutCopyMode = False
   End Sub
   

   Splitting a statement into several lines doesn’t change the way the macro runs; it just makes it easier to read.

2. In Excel, select cell F4 and press Ctrl+Shift+V to run the macro. Look at the formula bar to make sure the formula changed to a value.

3. Save the Chapter01 workbook.

Most of the macros in this chapter change the property of an object, but this macro executes the method of an object. Both properties and methods are separated from objects by periods, and both allow you to carry out actions. However, you assign a value to a property to carry out the action, whereas you simply execute a method, sometimes giving it arguments along the way

Trusting Macro-Enabled Workbooks

If you frequently use workbooks that contain macros, having to enable the macros each time you open a workbook can be annoying—so annoying, in fact, that you might be tempted to disable the warning. Please don’t do that. Eliminating the warning dramatically increases your computer's vulnerability to macro viruses. Excel 2007 provides two simple alternatives that allow you to safely eliminate the warning for workbooks that you trust.One alternative involves trusting the location of the macro workbooks. The other alternative involves trusting the creator (or publisher) of the workbook.

Designate a Trusted Location for Macros

The concept of a trusted location is that you designate a folder as a trusted, and then don’t put macro-enabled workbooks into that folder if there is any chance that the workbook might be unsafe. Excel will then open any macro-enabled workbooks stored in that folder without a warning. Setting up the trusted location is a little bit complicated, but you only have to do it when you want to trust a new location.

1. Close the Chapter01 workbook, and then re-open it.

2. In the warning bar, click the Options button, and then click the Open the Trust Center link at the bottom of the pop-up.

   Figure 26

   

3. In the Trust Center dialog box, select the Trusted Locations group, and then click the Add new location button

   Figure 27

   

4. In the Microsoft Office Trusted Location dialog box, click the Browse button, navigate to the folder you created for the Chapter01 macro-enabled workbook, and click OK to put the folder name into the Path box.

   Figure 28

   

5. Click the OK button three times to add the folder and close the dialog boxes.

   The warning message should disappear.

6. Close the workbook, and then re-open it.

   It should open without a warning.

7. Select cells D16:F23 and press Ctrl+Shift+C to format the cells as customized currency.

   This confirms that the macro still works.

If you put a workbook in a trusted location, you don’t have to explicitly enable the macros. You have the responsibility to make sure that only trustworthy documents get into any of the trusted locations. Certain folders—such as the Temporary Internet Files folder—can never become trusted locations. You may want to look at the list folders Excel trusts by default and remove some of them as well, just to be safe.

Designate a Trusted Publisher for Macro

Specifying a trusted location is good for those situations where you can store all your macro-enabled workbooks in a very limited number of folders. It is also convenient when you receive macro-enabled workbooks from several different (trustworthy) colleagues. But if you create macro-enabled workbooks and need to store them in arbitrary locations, you can also designate yourself as a trusted publisher.

First, you need a macro-enable workbook that is not in a trusted location.

1. Click the Microsoft Office Button, point to Save As, and click Macro-Enabled Workbook.

2. Navigate to an un-trusted folder (such as the main folder that contains the practice files for this book), change the name of the workbook to Chapter01A.xlsm, and then click Save.

In order to trust yourself as a publisher, you must be able to prove who you are. To do that, you must first create a digital ID. The Microsoft Office Button contains a shortcut that allows you to create the digital ID you need.

1. In Excel, click the Microsoft Office Button, point to Prepare, and click Add a Digital Signature.

   Tip
   If you see a message that encourages you to go to the MicrosoftOffice Marketplace, click OK to go to the Get A Digital ID dialog box.

2. In the Get a Digital ID dialog box, select Create your own digital ID.

   Figure 29

   

   Creating your own digital ID is secure, but it is also valid only for you and on only the current computer. If a different user logs into your computer, the ID will not be valid for them. If you copy the workbook to a different computer, the ID will not be valid there. If you need a digital ID that can be used in multiple environments, you need to obtain one from a trusted source. You can either purchase a digital ID, or check with your company’s Information Technology department to see if they can provide a digital ID for use within the company.

3. Click OK to display the Create a Digital ID dialog box, and enter your name, plusany additional information you choose.

   Figure 30

   

4. Click Create, and then stop.

   Figure 31

   

5. Do not click the Sign button. Do not pass Go. Do not collect $200. Click Cancel.

   Important

   Adding a digital signature to a workbook is different from adding one to the VBA project stored inside the workbook. The Prepare command on the Microsoft Office Button is a convenient way to create a new digital ID, but if you actually sign the document, you prevent any changes to the workbook cells. Conversely, when you add a signature to a VBA project, only the macros are signed and anyone can still make changes to the workbook. Make sure you go to the Visual Basic editor to add the actual signature.

6. Switch to the Visual Basic editor, click the Tools menu, and click Digital Signature.

   The top portion of the Digital Signature dialog box shows whether the project is currently signed. The bottom portion shows digital signatures that are already trusted.

   Figure 32

   

7. Click the Choose button, and then in the Select Certificate list, select the certificate you just created—it should have your name on it.

   Figure 33

   

8. Click OK.

9. After confirming that the project is currently signed with your certificate, click OK again.

   You now have a digital ID, and you have used it to sign the project, but there is still one more step: You have not yet told Excel that you want to trust yourself as a publisher.

10. Switch back to Excel and close the Chapter01A workbook, saving the changes back to the untrusted location. (The change you made was to sign the VBA project.)

11. Re-open the workbook, and click Options on the Security Warning bar.

    An ominous-looking security alert appears, but all it really says is that “you have not yet chosen to trust” yourself.

    Figure 34

    

12. Give it a lot of thought, but if you do decide to trust yourself to publish only macros that don’t destroy your own computer, select Trust All Documents From This Publisher, and then click OK.

    Security Note
    Depending on how you close and open the workbook, a different form of the security alert may appear immediately. Just click the Trust All From This Publisher button in the security alert and continue.

13. Close the workbook and open it again. No notice appears.

14. Select a suitable cell that contains a formula, and press Ctrl+Shift+V to make sure the macros work.

15. To confirm the Trusted Publishers list, click the Microsoft Office Button, and then click Excel Options at the bottom.

16. Select the Trust Center tab and click the Trust Center Setting button on the right.

17. Select the Trusted Publishers tab and see your certificate listed.

    Figure 35

    

    You can revoke the trust for a publisher at any time, just as you can revoke the trust for a location at any time.

18. Click Cancel twice to close the dialog boxes.

Important

You can use the Prepare command to create a digital ID, but you cannot use the same approach to delete a digital ID. To delete a digital ID from your computer, you must go to Internet Options in the Windows Control Panel. On the Content tab, click Certificates. The Certificates dialog box allows you to delete or import digital certificates from your computer, but it does not allow you to create a new personal digital ID. Creating a personal digital ID is part of the 2007 Microsoft Office system. If you remove a digital certificate from your computer, that does not remove it from the trusted publishers. You can still use the macro-enabled workbook, but making any subsequent changes to the macros destroys the signature on the project.

Security is important when you create any programs, including macros. Security is especially important when you share applications with others. If you are mostly creating macro-enabled workbooks for your own use, a personal digital ID is a simple, but flexible solution. If you are sharing macro-enabled workbooks with a small group of people, the trusted locations approach is probably a good solution. If you need to create macro enabled documents for a wider audience, you’ll probably want to obtain a properly authenticated digital signature to allow others to take advantage of your work.

CLOSE the Chapter01.xlsx workbook.

Key Points

• The easiest way to start and stop recording macros is by using the small button in the status bar. To review or run macros, use the Macros button on the View tab.

• Dock, undock, hide and show windows freely in the Visual Basic editor. It’s your working environment—make it work for you.

• When you have macros that relate to a single workbook, assign them to the Quick Access Toolbar for just that workbook. You might want to be more judicious about which macros you assign to the global Quick Access Toolbar.

• Don’t be afraid to change what the macro recorder created—if you save a backup copy of the original, you can always restore it later. Delete unnecessary statement and property assignments. This will make your macro much easier to understand the next time you use it.

• Take advantage of the new security features to keep your computer—and your company’s network—safe. Be very careful which folders and publishers you trust. If you receive a macro-enabled workbook from someone else—whether you trust them or not—open it with macros disabled and inspect the macros before you put the workbook into a trusted location.