Excel Chapter 7, Lecture Notes Using Macros and Visual Basic for Applications (VBA) with Excel Phase 1 – Recording a Macro and Assigning it to a Toolbar Button (pages 530-551)

Opening a Workbook (p. 534)  Start Excel  Open the file Caliber from the Data Files for Students and then save the workbook using the file name, Caliber Steel  Make sure that both the Excel window and the worksheet window are maximized

Figure 1: Opening a Workbook

1

Entering Data in a Selected Range of Cells (p. 536)  Select the range D5:D10. In cell D5, type Ali Kunar as the employee name and then press the ENTER key  In cell D6, type 68400 as the annual salary and then press the ENTER key  In cell D7, type 9% as the employee investment percentage and then press the ENTER key  In cell D8, type 6% as the employer match percentage and then press the ENTER key  In cell D9, type 8% as the annual return and then press the ENTER key  In cell D10, type 25 as the years of service and then press the ENTER key to display the new future value of $813,127.57 in cell F10  Click cell H5 to remove the selection from the range D5:D10. You may have to scroll down the worksheet to refresh the pointer in the range B16:B24 Follow the steps in the text to enter data in a selected range of cells

Figure 2: Steps to enter data in a selected range of cells

2

Unprotect a Password-Protected Worksheet (p. 538)  Click the Review tab on the Ribbon  Click the Unprotect Sheet button to display the Unprotect Sheet dialog box  When the Unprotect Sheet dialog box appears, type caliber in the Password text box  Click OK to unprotect the 401(k) Investment Model worksheet  Click the Office Button to display the Office Button menu and then the Excel Options button to display the Excel Options dialog box  Click the Show Developer tab in the Ribbon check box  Click the OK button in the Excel Options dialog box to display the Developer tab on the Ribbon.  Click the Developer tab  Click the Macro Security button on the Ribbon to display the Trust Center dialog box  When the Trust Center dialog box appears, click the ‘Enable all macros’ option button to select it

Figure 3: Unprotect a Password-Protected Worksheet

3

Displaying the Developer Tab, Enabling Macros, and Saving a Workbook as a Macro-Enabled Workbook (p. 540)  Click the Office Button to display the Office Button menu and then the Excel Options button to display the Excel Options dialog box  Click the Show Developer tab in the Ribbon check box  Click the OK button in the Excel Options dialog box to display the Developer tab on the Ribbon.  Click the Developer tab  Click the Macro Security button on the Ribbon to display the Trust Center dialog box  When the Trust Center dialog box appears, click the ‘Enable all macros’ option button to select it  Click the OK button to close the Macro Security dialog box Click the Office Button and then click Save As to display the Save As dialog box  When the Save As dialog box appears, click the ‘Save as type’ box arrow and then click Excel Macro-Enabled Workbook in the ‘Save as type‘ list  Click the Save button in the Save As dialog box to save the workbook as an Excel Macro-Enabled Workbook file type

Figure 4: Saving a Workbook as a Macro-Enabled Workbook

4

Recording a Macro to Print the Worksheet in Portrait Orientation Using the Fit to Option (p. 542)  If necessary, click the Developer tab on the Ribbon  Click the Record Macro button on the Ribbon to display the Record Macro dialog box  When the Record Macro dialog box appears, type PrintPortrait in the Macro name text box  Type r in the Shortcut key text box to set the shortcut key for the macro to CTRL+R  Make sure the ‘Store macro in‘ box displays This Workbook and then type Macro prints worksheet in portrait orientation on one page in the Description text box  Click the OK button to begin recording the macro and cause the Record Macro button to become the Stop Recording button  Click the Page Layout tab on the Ribbon  Click the Page Setup Dialog Box Launcher to display the Page Setup dialog box  When the Page Setup dialog box displays, click the Page tab. If necessary, click Portrait in the Orientation area, and then click Fit to in the Scaling area  Click the Print button in the Page Setup dialog box  When the Print dialog box displays, click the OK button to print the worksheet  Click the Page Setup Dialog Box Launcher to display the Page Setup dialog box  If necessary, when the Page Setup dialog box displays, click the Page tab; click Landscape in the Orientation area; click Adjust to in the Scaling area; and then, if necessary, type 100 in the % normal size box  Click the OK button to close the Page Setup dialog box  Click the Developer tab on the Ribbon and then click the Stop Recording button to stop recording the worksheet activities

5

Figure 5: Recording a Macro to Print the Worksheet in Portrait Orientation Using the Fit to Option

Password-Protecting the Worksheet, Saving the Workbook and Closing the Workbook (p. 544)  Click the Review tab on the Ribbon, and then click the Protect Sheet button on the Ribbon. When the Protect Sheet dialog box appears, type caliber in the ‘Password to unprotect sheet’ text box and then click the OK button. When the Confirm Password dialog box is displayed, type caliber and then click the OK button  Click the Office Button and then click Save As. When the Save As dialog box is displayed, type Caliber Steel1 in the File name text box. Make sure your flash drive displays in the Save in box and then click the Save button in the Save As dialog box  Click the workbook’s Close button on the right side of its menu bar to close the workbook and leave Excel active

6

Setting the Macro Security Level to Medium (p. 545)  Click the Developer tab on the Ribbon  Click the Macro Security button on the Ribbon to display the Trust Center dialog box  Click the ‘Disable all macros with notification’ option button  Click the OK button in the Trust Center dialog box to close it

Figure 6: Setting the Macro Security Level to Medium

Opening a Workbook with a Macro and Executing the Macro (p. 546)  With Excel active, open the Caliber Steel1 workbook  Click the Options button in the Security Warning box to display the Microsoft Office Security Options dialog box 7

 Click the ‘Enable this content’ option button to select it  Click the OK button  When the Caliber Steel1 workbook opens, press CTRL+R to run the macro and print the worksheet

Figure 7: Opening a Workbook with a Macro and Executing the Macro

Viewing and Printing a Macro’s VBA Code (p.548)  Click the Developer tab on the Ribbon  Click the Macros button on the Ribbon to display the Macro dialog box  If necessary, when the Macro dialog box is displayed, click PrintPortrait in the Macro name list  Click the Edit button to start the Microsoft Visual Basic Editor  Use the scroll bar to scroll through the VBA code  When you are finished, click File on the menu bar  Click Print  When the Print – VBA Project dialog box is displayed, click the OK button to 8

print the macro code  Click the Microsoft Visual Basic Editor Close button on the right side of the title bar

Figure 8: Viewing and Printing a Macro’s VBA

Adding a Button to the Quick Access Toolbar, Assigning the Button a Macro, and Using the Button (p. 549)  Right-click anywhere on the Quick Access Toolbar to display the shortcut menu  Click the Customize Quick Access Toolbar command on the shortcut menu to display the Customize the Quick Access Toolbar page of the Excel Options dialog box  Click the ‘Choose commands from’ box arrow  Click Macros in the ‘Choose commands from’ list to display a list of macros 9

 If necessary, click the PrintPortait macro to select it  Click the Add button to add the PrintPortrait macro to the Customize Quick Access Toolbar list  Click the Modify button to display the Modify Button dialog box  Click the printer icon (column 5, row 2) in the Symbol list  Click the OK button to assign the printer icon to the new command and to close the Modify Button dialog box  Click the OK button in the Excel Options dialog box to close it  Point to the PrintPortrait button on the Quick Access Toolbar to display the ScreenTip for the button

Figure 9: Adding a Button to the Quick Access Toolbar, Assigning the Button a Macro, and Using the Button

10