California State University, Northridge Information Technology Training Guide Prepared by Tina Actis-Purtee, User Support Services June 13, 2006 ITR’s Technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please do not use this material without citing the source. Excel 2003 Basic Macros in Excel Table Of Contents 1. Introduction 2. What are Macros? 3. Recording Macros • Absolute Vs. Relative • Creating an Absolute Macro • Creating a Relative Reference 4. Running Macros 5. Assigning Macros • to your Toolbar • to an Icon Button • to the Menu Bar • To an Object 6. Deleting Macros 7. Viewing your Macro Option 8. Class Exercise Introduction Find out what a macro can do for you. Save yourself time, energy, keystrokes and headaches. Learn how Macros can help you perform frequently repeated tasks quicker and easier. This guide will take you through the process of: • Understanding what macros are and what they can do for you. • Recording macros • Running macros • Deleting macros • Assigning frequently used macros to an icon buttonCalifornia State University, Northridge Information Technology Training Guide Prepared by Tina Actis-Purtee, User Support Services June 13, 2006 ITR’s Technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please do not use this material without citing the source. What Are Macros? What is a Macro? A macro is a collection of excel activities that are performed in a predefined sequence and recorded. The recorded sequences run completely within Excel. What can Macro's do for me? Marcos can be used to automate tedious or frequently repeated tasks . How many times in a day do you have to type in a standard page heading for your department or put a standard footer on your document? A macro will carry out this sequence of actions much more quickly than you could yourself. For example, you can create a macro that enters your department name, address, phone number, etc. In one row or cell in your worksheet, centers the date in the cell, and then applies a color and border format to the row or cell. Any task that you find yourself repeatedly performing in Excel, a macro can be recorded for it. Wow, what a time saver. Now instead of carrying out that task manually, all you have to do is hit a sequence of two or three keys. Where is my Macro stored? When you record a macro, you have the option of storing it in a couple places: 1) The This Workbook, or 2) the Personal Macro Workbook. Those Macros that are stored in the "This Workbook" can only be accessed when that particular workbook containing the macro is open. As long as that workbook is open, the macro you recorded and stored in that workbook is available to you to use in other workbook documents. Macros stored in the "Personal Macro Workbook" can be accessed from any Excel workbook whenever your Excel application is open. Once you have recorded your initial macro, you can run the macro to perform the task again. The overall process for recording a macro consists of three steps. First, you start the macro recorder and supply a name for the macro. Next, you perform the actions you want to record, such as choosing menu commands, selecting cells, and entering data. Finally, you stop the macro recorder.California State University, Northridge Information Technology Training Guide Prepared by Tina Actis-Purtee, User Support Services June 13, 2006 ITR’s Technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please do not use this material without citing the source. Recording Macros Absolute vs. Relative References Before you begin recording a macro, you will need to determine whether the cell references will be absolute or relative. Absolute Reference An absolute reference refers to a specific cell address such as A1. For example, when you record your macro using this setting, you input data in cell D5, Excel will place the data in the D5 position each time the macro is executed. Relative Reference A relative reference, on the other hand, refers to a cell’s position relative to other cells, rather than a specific cell address. For example, if you want the macro to begin in a position relative to the cell in which you click, click on the Relative Reference button on the Stop Recording toolbar (see figure 1) If the relative reference button is selected, it will display with a light grey box around it like it is depressed. Figure 1 * * Note * * It is important to plan the macro and test the sequence before you begin recording. Every keystroke or step is recorded, even errors or typo corrections. Creating an Absolute Reference Follow these steps 1. From the "TOOLS" menu select "MACRO" 2. From the Macro submenu select "RECORD NEW MACRO" 3. Excel will bring up the "Record Macro" dialog box shown in Figure 2. Relative Reference ButtonCalifornia State University, Northridge Information Technology Training Guide Prepared by Tina Actis-Purtee, User Support Services June 13, 2006 ITR’s Technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please do not use this material without citing the source. Excel displays the Record Macros dialog box shown. Figure 2 – Record Macro Dialog Box 4. In the "Macro Name" field type the name of the macro. * * Note * * The name you choose must be one word. Excel does not allow spaces. For example, formatmacro is acceptable, while format macros is not. You can use the underscore (_) to separate words. For example format_macro is acceptable. 5. Next, in the "Shortcut key" field assign a key stroke combination to the macro by entering a letter in the shortcut Key edit box. (The letter should be an uppercase letter so as not to interfere with already existing Excel macros) 6. In the "Store Macro in" field, use the arrow to the right of this field to choose where you want to store this macro. • If you store it in the "Personal Macro Workbook" it will be available to you to use in any Excel worksheet or workbook. • If you choose