13. Adding a Macro & VBA Button

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.

Adding a Visual Basic Command Button

We brought our 401K Calculator worksheet to a conclusion by using an ActiveX Command Button. We wrote code in our Visual Basic Editor to function whenever the Button is clicked to automatically execute the steps needed to Clear Data, and then request new information from the user to add data in our Retirement Calculator.

That should give you a small taste of what you can accomplish using VBA with Forms and ActiveX Controls.

If you find this sample interesting, then feel free to complete the last part of the chapter on VBA (Visual Basic for Applications) by constructing a User Interface to entirely automate the worksheet. You'll be surprised by how much can be controlled by getting an understanding of the capabilities of using VBA. Look over and optionally complete pages 567-603.

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.