Manipulating Recorded Properties | Microsoft Excel 2002 Visual Basic for Applications Step by Step (Step by Step (Microsoft))

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.

Click the Record Macro button.
Replace the default macro name with CleanDisplay, and click OK.
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.
Click the Stop Recording button, and then save the Chapter01 workbook.

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 justa 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.

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.

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.

Press Ctrl+Z three times to change all the True values back to False.
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.
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.
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.
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:

Select ActiveWindow.DisplayGridlines, and copy it.
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.
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.

Change the name of the macro from CleanDisplaytoToggleCleanDisplay 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

Click the Save button in the Visual Basic editor.

Macro Save

This saves the workbook that contains the macros.
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:

Put the insertion point just before the first occurrence of the word ActiveWindow, and type With, followed by a space.
Put the insertion point just before the first period and press Enter, creating a new line.
Delete all three remaining occurrences of the word ActiveWindow, but always leave the dangling period.
Just before the word Application, insert a new line, and type End With.
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
```
Press F5 to test your changes.

It should toggle the same as before.
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
```
Press F5 to test your changes, and save the Chapter01 workbook.

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

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.

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.
In the Customize Quick Access Toolbar list, select For Chapter01.xlsm.

This will make the button visible only when the Chapter01 workbook is active.
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 that may not help you remember what the macro does.
Select the ToggleCleanDisplay macro in the list on the right, and then click Modify.
In the Modify Button dialog box, select the white box icon (to symbolize a clean display), and then change the Display Name to Toggle Clean Display (which makes the tool tip easier to read without affecting the actual macro name).
Click OK twice to close both dialog boxes and return to Excel, which now has a new Toggle Clean Macro button in the Quick Access Toolbar.
To try out the new Toggle Clean Display button, add a new workbook (which will make the button disappear), and then close it (which will make the button reappear).

Toggle Clean Display