PAUSE & PRACTICE: EXCEL 8-3

In this project, you build a macros-only workbook and run the macros in a separate workbook.

File Needed: PerfectRentals-08NoMacros.xlsx

Completed Project File Names: [your initials] PP E8-3Macros.xlsm and [your initials] PP E8-3.xlsx

1. Create a new macro-enabled workbook.
   1. Create a new workbook and choose Save As from the File tab.
   2. Select This PC and type [your initials] PP E8-3Macros as the file name.
   3. Choose Excel Macro-Enabled Workbook from the Save as type list.
   4. Navigate to your folder for saving files and click Save.
2. Display the Developer tab if it is not shown.
3. Set macro security to Disable VBA macros with notification in the Trust Center.
4. Record a macro with absolute references.
   1. Verify that the Use Relative References button is not active [Developer tab, Code group].
   2. Click the Record Macro button [Developer tab, Code group].
   3. Type InsertDate in the Macro name box.
   4. Click the Shortcut key box, press Shift, and type d.
   5. Verify that This Workbook is selected in the Store macro in box.
   6. Click the Description box, type Insert date statement, and click OK.
   7. Select cell F2. This is the first step in your macro.
5. Use a nested CONCAT function in the macro.
   1. Click the Text button [Formulas tab, Function Library group] and choose CONCAT.
   2. Type Today is and press the Spacebar for the Text1 argument.
   3. Click the Text2 argument box. You will nest the TEXT function here.
   4. Click the Name Box arrow and choose More Functions.
   5. Navigate and find TEXT in the Text category.
   6. Verify that the Value argument box is ready in the TEXT Function Arguments dialog box.
   7. Type today() as the Value argument. You do not need an equals sign when the function is used as an argument.
   8. Click the Format_text argument box and type “mmmm dd, yyyy” including the quotation marks (Figure 8-34).
   9. Click CONCAT in the Formula bar and then click OK.
   10. Click the Stop Recording button [Developer tab, Code group].
6. Test the macro.
   1. Click the Undo button to delete the statement. You can undo actions that were carried out as you recorded a macro.
   2. Press Ctrl+Shift+D to test the macro.
   3. Select cell F2 and delete the contents. You cannot undo actions that were carried out by execution of a macro.
   4. If your macro did not execute properly, open the Macro dialog box, delete it, and repeat steps 3–5.
7. Determine macro steps to add fill to a row.
   1. Open PerfectRentals-08NoMacros.xlsx from your student data files.
   2. Select cell A5. You are not recording the macro at this point; you are going through the task to familiarize yourself with what you will record.
   3. Press Shift + press right arrow three times to select cells A5:D5.
   4. Click the Fill Color arrow [Home tab, Font group] and choose White, Background 1, Darker 15% (first column) to apply the fill color.
   5. Press Home and then press down arrow two times to move the active cell to cell A7. These are the steps to record in your macro.
   6. Click the Undo button and select cell A5.
   7. Leave the workbook open.
8. Record a macro with relative references.
   1. Return to [your initials] PP E8-3Macros, your macros-only workbook.
   2. Select cell A1. You can record the macro from any cell.
   3. Click the Use Relative References button [Developer tab, Code group].
   4. Click the Record Macro button [Developer tab, Code group].
   5. Type ApplyFill in the Macro name box.
   6. Click the Shortcut key box, press Shift, and type f.
   7. Verify that This Workbook is selected in the Store macro in box.
   8. Click the Description box, type Apply fill color to current row, and click OK. All keyboard movement commands will be recorded.
   9. Press Shift + press right arrow three times. This selects cells A1:D1.
   10. Click the Fill Color arrow [Home tab, Font group] and choose White, Background 1, Darker 15% (first column).
   11. Press Home and then press down arrow two times. The pointer ends in cell A3.
   12. Click the Stop Recording button [Developer tab, Code group].
   13. Click the Use Relative References button [Developer tab, Code group] to turn off the command.
9. Test the macro.
   1. Click the Undo button to remove the fill color.
   2. Select cell A5.
   3. Press Ctrl+Shift+F to test the macro. The macro runs in the current row.
   4. Apply No Fill to the cells in row 5.
   5. Open the Macro dialog box if your macro did not execute properly, delete it, and repeat step 8.
10. Prepare documentation for the macros-only workbook.
    1. Click the Select All button to the left of the column A heading. This selects every worksheet cell.
    2. Apply Gold, Accent 4, Lighter 60% fill (eighth column).
    3. Select cell A1.
    4. Type the documentation as shown in Figure 8-35.
    5. Set the label in cell A1 to 16 pt, center the labels in row 3 and apply bold, and add borders as shown.
    6. Click Save on the title bar.
11. Run macros in another workbook.
    1. Return to PerfectRentals-08NoMacros.
    2. Save this workbook as a regular Excel workbook named [your initials] PP E8-3 in your usual folder.
    3. Select cell A4.
    4. Press Ctrl+Shift+D to run the date macro. It displays the date in cell F2 because it was recorded with absolute references.
    5. Select cell A5.
    6. Press Ctrl+Shift+F to run the macro. The macro runs from the insertion point position.
    7. Press Ctrl+Shift+F to run the macro in row 7.
    8. Run the macro in the odd-numbered rows with data.
    9. Save and close the workbook (Figure 8-36).
12. Save and close the macros-only workbook.
13. Hide the Developer tab.