Excel 2016 Macros

Lesson 3 – Editing Macros

Excel 2016

E XERCISE

E DITING M ACROS



T ASK Create and edit macros using the Visual Basic Editor.

1. Open EXER83.XLSM and enable the macros. 2. View the workbook in Print Preview layout. Notice the portrait page orientation and then close Print Preview . 3. Open the Visual Basic Editor and open the Module1 Code window, if necessary. Maximize the Code window. 4. Press [Enter] twice to move the Sub Sort_RegionSales() down two lines. 5. Press [Ctrl+Home] to navigate to the top of the Code window. Then type Sub Sort_RegionLastN() , and press [Enter] . 6. Enter the following comment: Sorts by region and then by last name . Then, press [Enter] . ( Hint: Remember to begin the comment with an apostrophe.) 7. Copy all the macro steps from the Sort_RegionSales macro to the Sort_RegionLastN macro. 8. In the Sort_RegionLastN macro, change the cell reference in Key2:=Range(“J6”) to A6 and change the Order2:=xlDescending argument to Order2:=xlAscending . 9. Insert a blank line above the End Sub command and type the following macro command in the line: ActiveSheet.PageSetup.Orientation=xlLandscape . Then press [Enter] . 10. Run the Sort_RegionLastN macro from the Visual Basic Editor. ( Hint: Remember to place the insertion point within the desired macro.) 11. Close the Visual Basic Editor. 12. Notice that the list in the Last Year worksheet is sorted first by region and then by last name. View the list in Print Preview and notice the landscape page orientation. 13. Select the Projections worksheet and run the Sort_RegionLastN macro.

14. Delete the Sort_RegionLastN macro. 15. Close the workbook without saving it.

Page 56

OFFICEPRO, Inc.