Changing Multiple Properties at Once | Microsoft Excel 2002 Visual Basic for Applications Step by Step (Step by Step (Microsoft))

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

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.

Orientation

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

Activate the Budget window.
Select the range A6:A12.

The label Variable is at the top of the selected range.
Right-click in the selection, and click Format Cells.
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.
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.
Click OK to merge and tilt the label.

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

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

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.

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.

Right–click the selection, and then click Format Cells.
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.

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.

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.

Troubleshooting

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.

Press the Delete key.
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.

Macro Undo

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
```
Activate the Excel window, and select cells A25:A30.
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).
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.