20. Adding a Macro

What is a Macro?

Though printing our worksheet shows just what we had in our 401k Calculator and Data Table, we worked on creating a custom report by using a Macro.

Our Macro changes the orientation to Landscape, makes it scale up to 115%, centers the sheet both horizontally and vertically, and adds custom Headers. It then sends the job to the printer, and then undoes all the changes, and leaves us with our original Default Print job intact.

You can use a Macro to automate procedures so that you don't have to do them step by step every time. If you can think of a number of steps you'd like Excel to perform, you can create a Macro to do just that.

Remember that we changed the level of Macro Security, so that Excel requires us to OK a file with an included Macro for our own safety! We also saved our last file as an XLSM file, which means that it is an Excel 2007 file that is Macro Enabled. Saving, closing and re-opening the file gives you control over what Macro-Enabled files you open.

We looked at the code generated in VBA (Visual Basic for Applications) just to get an idea of what the code for a simple Macro looks like. The macro seems much more complex than the few steps we recorded.

We went on to Add a Button to the Quick Access Toolbar, and to assigned our Print Macro to the Button. That, along with our Keyboard Shortcut, allows us to quickly execute the Macro.

Materials Covered: Project 7 - Using Macros and Visual Basic for Applications (VBA) with Excel, pp. EX 548-566

Case 8: Developing an Excel Application. Follow the instructions to download your workfile.
Caution!! To correctly assign a Macro to a Button, use a Form Control Button from the Form Control Group, or your efforts will be useless!"
In your journal, write a short note telling me what you learned from this case. Thanks.