| Changing Multiple Properties at Once The FormatCurrency macro changes a single property (the number format) of a single object (the currently selected cells). Assigning a value to the property changes the object. Many macro statements assign a value to a property of an object. Sometimes when you record an action, the macro changes multiple properties of one object at the same time. Merge Text Vertically with a Command Excel has a toolbar button that can merge and center several cells in a horizontal row: the Merge And Center button. But sometimes you might want to merge cells vertically along the edge of a report. Excel doesn't have a toolbar button that merges cells in a vertical column, but you can record a macro that does. To better understand what's required, first walk through the steps to create this format using menu commands. -
Activate the Budget window. -
Select the range A6:A12. The label Variable is at the top of the selected range. -
On the Format menu, click 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. Putting a label to the side of a block of cells is extremely powerful. You can make it easy to do by recording a macro. 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. Then right-click the taskbar, and on the shortcut menu, click Tile Windows Horizontally. | Record Macro -
Select the range A15:A20, and then click the Record Macro button. -
In the Record Macro dialog box, replace the default macro name with MergeVertical, replace the default description with Merge cells vertically, and set Ctrl+Shift+M 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. 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 puts 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 recording macros into 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. | -
On the Format menu in the Excel window, click 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 puts several lines into the macro all at once. Stop Recording -
Click the Stop Recording button, and save the Chapter01 workbook. The new macro in the Module1 window looks like this: Sub MergeVertical() ‘ ‘ MergeVertical Macro ‘ Merge cells vertically ‘ ‘ Keyboard Shortcut: Ctrl+Shift+M ‘ 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 cells, as does the NumberFormat property in the FormatCurrency macro. In the FormatCurrency macro, however, the object directly precedes the property, separated only by a period. In this MergeVertical macro, however, 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. Whenever a property inside a With structure is preceded by a dangling period with no object, you simply pretend that the object from the With statement is there. With structures make the code easier to read because you can tell instantly that all the properties relate to the same object-the current selection in this case. You'll often see With structures in macros that you record. Eliminate Unnecessary Lines from the Macro In many dialog boxes, the macro recorder records all the possible properties, even though you might change 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 MergeVertical macro, you need to change the values of only the Orientation and MergeCells properties. You can therefore delete 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 right-pointing arrow 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. 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. The simplified macro (ignoring the comment lines, which you can delete if you want) should look like this: Sub MergeVertical() 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. -
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. |