Advanced Training for Microsoft Excel 2002

Automating Repetitive Tasks with Macros

Introducing Macros

1 To locate an existing macro, click the Tools menu, point to Macro, and then click Macros....
2 To open the Visual Basic Editor and view the code behind a macro, in the Macro dialog box, in the Macro name box, note that the Highlight macro is already selected, and click Edit.
3 Notice the code for the Highlight macro displayed in the right pane of the Visual Basic Editor. To return to the Excel worksheet, in the upper-right corner of the Visual Basic Editor, click the red Close button.
4 To see the steps of the Highlight macro performed one-by-one, click the Tools menu, point to Macro, and then click Macros....
5 In the Macro dialog box, click Step Into.
6 Notice that the the first line of instruction is highlighted in the Visual Basic Editor. To move through the macro one step at a time, click the Debug menu, and then click Step Into.
7 To view the first step of the macro performed on the Excel workbook, click the Debug menu, and click Step Into again.
8 Note that cell E6 is selected in the worksheet and the next step (Selection.Font.Bold = True) is highlighted in the Visual Basic Editor. To allow the macro to bold the contents of cell E6, click the Debug menu, and click Step Into again.
9 To close it, in the upper-right corner of the Visual Basic Editor, click the red Close button.
10 To undo the effect of the macro on cell E6, click cell E6, and then, on the Formatting toolbar, click the Bold button.
11 To run the Highlight macro without stopping, click the Tools menu, point to Macro, click Macros...
12 In the Macro dialog box, click Run.
13 Notice that the contents of cells E6, E11, E18, F8, and F17 are now bold. To save your work, on the Standard toolbar, click the Save button.

Creating and Modifying Macros

1 To create a new macro that removes the bold format from a series of cells, click the Tools menu, point to Macro and then click Record New Macro....
2 To give an appropriate name to the macro you are about to record, in the Record Macro dialog box, in the Macro name box, type RemoveHighlight, and then click OK.
3 Notice that the Stop Recording toolbar appears, indicating that your actions are now being recorded for the new macro. To begin recording steps for the macro, click cell E6, and then, on the Formatting toolbar, click the Bold button.
4 To end recording the macro, on the Stop Recording toolbar, click the Stop Recording button (the square to the left).
5 To modify the macro you have just recorded, click the Tools menu, point to Macro and then click Macros....
6 To select the macro to be modified, in the Macro dialog box, in the Macro name box, click RemoveHighlight, and then click the Edit button.
7 Notice the code for the RemoveHighlight macro displayed in the right pane of the Visual Basic Editor. To modify the macro so that it selects cell F17, click to the left of End Sub, type Range(?F17?).Select, and then press ENTER.
8 To add a macro statement that removes the bold formatting from the selected cell (F17), type Selection.Font.Bold = False, and then press ENTER.
9 To scroll the worksheet so that columns E and F are clearly visible, on the horizontal scroll bar, click the right scroll arrow twice.
10 To run the macro that you have just modified, on the Task Bar at the bottom of the screen, click the Microsoft Visual Basic button.,
11 In the Visual Basic Editor, click the Run menu, and then click Run Sub/UserForm.
12 Notice that the macro has removed bold formatting from cell F17. To save your changes to the RemoveHighlight macro and to the DailySalesbyHour workbook, on Excel's Standard toolbar, click the Save button.

Creating a Toolbar to Hold Macros

1 To begin creating a toolbar for a new macro, click the Tools menu, and then click Customize.
2 In the Customize dialog box, on the Toolbars tab, click New....
3 In the New Toolbar dialog box, in the Toolbar name box, type Custom Macros, and then click OK.
4 Notice that the Custom Macros toolbar appears in the Toolbars list in the Customize dialog box, and a new, empty toolbar floats in the workbook window at left. To add a new button to the Custom Macros toolbar, in the Customize dialog box, click the Commands tab.
5 In the Categories list, click once below the scroll box in the scroll bar, and then click Macros.
6 In the Commands list, click Custom Button, press and hold down the left mouse button, drag the mouse pointer to the new Custom Macros toolbar at left, and release the mouse button.
7 To assign an appropriate name to the new button, in the Customize dialog box, click Modify Selection, and then click Name.
8 Notice that the text to the right of the Name command is already selected.To change the button's name, type Highlight, and then press ENTER.
9 To change the appearance of the new Highlight button, in the Customize dialog box, click Modify Selection, point to Change Button Image, and then click the Up Arrow button image (fifth row, 3rd button).
10 To assign a macro to the toolbar, in the Customize dialog box, click Modify Selection, and then click Assign Macro....
11 To select the appropriate macro, in the Assign Macro dialog box, click Highlight, and then click OK.
12 To end the process of creating the toolbar, click Close.
13 To run the macro on the worksheet, on the Custom Macros toolbar, click the Highlight button.
14 Notice that the macro has changed the contents of several cells to bold type. To hide the Custom Macros toolbar, on the Custom Macros toolbar, click the Close button.
15 To delete the Custom Macros toolbar entirely, click the Tools menu, and then click Customize.
16 In the Customize dialog box, click the Toolbars tab.
17 In the Toolbars list, click once below the scroll box in the vertical scroll bar, click Custom Macros, and then click Delete.
18 In the warning dialog box that appears click OK.
19 In the Customize dialog box, click Close.

Creating a Menu to Hold Macros

1 To begin creating a custom menu to hold macros, click the Tools menu, and click Customize.
2 In the Customize dialog box, in the Categories list, click once below the scroll box in the scroll bar, and then click New Menu.
3 To position the new menu, in the Commands pane, click New Menu, press and hold down the left mouse button, drag the mouse pointer to the left of the Help menu on the menu bar at the top of the screen, and release the mouse button.
4 To assign an appropriate name to the new menu, in the Customize dialog box, click Modify Selection, and then click Name:.
5 Notice that the text next to the Name: command is already selected. To name the menu, type Custom Macros, and then press ENTER.
6 Notice that Custom Macros is now displayed on the menu bar. To begin adding commands to the Custom Macros menu, in the Customize dialog box, in theCategories pane, click Macros.
7 In the Commands list, click Custom Menu Item, press and hold down the left mouse button, drag the menu command to the Custom Macros menu head, then drag it to the box that appears under the Custom Macros menu head, and release the mouse button.
8 To assign a name to the selected custom menu item, in the Customize dialog box, click Modify Selection, and then click Name:.
9 Notice that the text next to the Name: command is already selected. To change the custom menu item's name, type Highlight, and then press ENTER.
10 Notice that the name of the menu item changes to Highlight. To assign a macro to the Highlight menu item, in the the Customize dialog box, click Modify Selection, and then from the menu that appears, click Assign Macro....
11 In the Assign Macro dialog box, in the Macro Name box, click Highlight, and then click OK.
12 In the Customize dialog box, click Close.
13 To run the macro on the worksheet, click the Custom Macros menu, and then click Highlight.
14 Notice that the macro has changed the contents of several cells to bold type. To delete the Custom Macros menu that you have just created, click the Tools menu, and then click Customize.
15 On the Excel menu bar, click the Custom Macros menu head.
16 In the Customize dialog box, click Modify Selection, and then click Delete.
17 In the Customize dialog box, click Close.

Running a Macro When a Workbook Is Opened

1 To begin recording a macro that will run automatically whenever this workbook is opened, click the Tools menu, point to Macro and then click Record New Macro....
2 To signal to Excel that it should run this macro every time this workbook is opened, in the Record Macro dialog box, in the Macro name box, type Auto_.
3 To complete the macro's name with a word that reflects the macro's function, type Regular, and then click OK.
4 To select the range of cells D6:O36, click cell D6, click once to the right of the scroll box in the horizontal scroll bar, click once below the scroll box in the vertical scroll bar, press and hold down the SHIFT key, click cell O36, and release the SHIFT key.
5 To format the selected cells, click the Format menu, and then click Cells...
6 To apply Regular font style to the selected cells, in the Format Cells dialog box, on the Font tab page, in the Font style list, click Regular, and then click OK.
7 Notice that all cells that contained bold type now appear in regular type. To stop recording the macro, on the Stop Recording toolbar, click the Stop Recording button (square button on the left).
8 To undo changes you've just made so that you can test the new macro, on the Standard toolbar, click the Undo button.
9 To save the recorded macro, on the Standard toolbar, click the Save button.
10 To test the Auto_Regular macro, you first need to close the DailySalesbyHour file. To do this, click the Close button at the top right of the Excel window.
11 To run the Auto_Regular macro, on the Standard toolbar, click the Open button.
12 In the Open dialog box, double-click DailySalesbyHour.
13 In the dialog box warning about macros contained in the workbook, click Enable Macros.
14 Notice that when the DailySalesbyHour file opens, the contents of all cells appear in regular type, and the range D6:O36 is selected, indicating that the Auto_Regular macro has been run. To save your work, on the Standard toolbar, click the Save button.