86.

Editing a Recorded Macro

A typical Excel worksheet has light gray gridlines that mark the boundaries of the cells. Sometimes, you might want to remove the gridlines. First walk through the process to remove the gridlines with menu commands, and then record a macro to make the change.

Remove Gridlines with a Command

1. On the Tools menu, click Options, and then, if necessary, click the View tab.

2. Clear the Gridlines check box at the bottom of the Window Options group.

3. Click OK. The gridlines disappear.

4. Repeat step 1, select the Gridlines check box to turn the gridlines back on, and then click OK.

Gridlines are a property of the window. You can select the Gridlines check box so that the value of the property is True and the window displays the gridlines, or you can clear the check box so that the value of the property is False and the window doesn't display the gridlines. Now see how the recorder turns off the gridlines.

Record a Macro to Remove Gridlines

Record Macro

1. Click the Record Macro button.

2. Replace the default macro name with RemoveGrid, and click OK. The recorder puts the shell of the macro (the comments and the Sub and End Sub lines) into the module.

3. On the Tools menu, click Options, clear the Gridlines check box on the View tab, and then click OK. The gridlines disappear.

   Stop Recording

   

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

   Run Macro

   

5. Click the Run Macro button, select RemoveGrid, and then click Edit to look at the resulting code. Ignoring the comment lines, here's what it looks like:

   Sub RemoveGrid()     ActiveWindow.DisplayGridlines = False End Sub

This macro is similar to the FormatCurrency macro. You can read it 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. In both cases, you're changing an object, an Excel element that you can control with macros. However, this time the object isn't a range of cells, but a window.

Run the Macro from the Visual Basic Editor

You can easily change the macro to make it restore the gridlines.

1. In the RemoveGrid macro, replace False with True.

   You can't use a shortcut key while you're in the Visual Basic Editor, but the Visual Basic 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 

   If you're in VBA and want to display the Macro dialog box so that you can select a macro, click outside any macro before you press F5.

Toggle the Value of a Property with a Macro

You could create one macro to turn the gridlines off and a second macro to turn them on; but somehow, letting a single macro toggle the value of the property seems more natural. To toggle the value of a property, you first ask Excel for the current value, which you can store in a special container called a variable. You then change the value as you assign the variable back to the property. Here's how:

1. Insert a new blank line after the comments.

2. Select ActiveWindow.DisplayGridlines, and press and hold the Ctrl key as you drag it up to the blank line. This makes a copy of the expression.

3. Type myGrid = at the beginning of the new line; the resulting statement is myGrid = ActiveWindow.DisplayGridlines. This statement stores the current value of DisplayGridlines, whether True or False, in the variable myGrid.

   Important 

   Variable names follow the same rules as macro names: begin with a letter, and use only letters, numbers, and underscores. You should also avoid names already used by Excel or Visual Basic. If you add a prefix such as my to the variable name, you'll most likely avoid any conflict.

4. Double-click True in the original statement, and replace it with Not myGrid. The VBA keyword Not turns the value True into False and False into True.

5. Change the name RemoveGrid to ToggleGrid to better reflect the macro's new capabilities. This is what the macro should look like now:

   Sub ToggleGrid()     myGrid = ActiveWindow.DisplayGridlines     ActiveWindow.DisplayGridlines = Not myGrid End Sub

   Important 

   If Option Explicit appears at the top of the module, delete it before running this macro.

6. Click the Save button in the Visual Basic Editor. This saves the workbook that contains the macros.

7. 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. Then stop the recorder and edit the recorded macro if necessary.