Microsoft Excel Macros
Email:
[email protected] Web Page: http://training.health.ufl.edu
Microsoft Excel: Macros 1.5 hours
Topics include working with series of text, numbers and dates; building equations; using basic mathematical functions; learning about absolute and relative references; naming cells; and working with named cell ranges. Macros ............................................................................................................................................. 1 Moving Between Cells ..................................................................................................................... 1 Navigating using the keyboard.................................................................................................... 1 Enter Key ................................................................................................................................. 1 Tab Key .................................................................................................................................... 1 Page Up and Page Down Keys ................................................................................................. 1 Arrow Keys .............................................................................................................................. 2 Home Key ................................................................................................................................ 2 End Key .................................................................................................................................... 2 Escape Key ............................................................................................................................... 2 Navigating Using the Mouse and Keyboard ................................................................................ 3 Shift‐Click ................................................................................................................................. 3 Ctrl‐Click .................................................................................................................................. 3 Keyboard Shortcuts ......................................................................................................................... 3 Excel 2010 Macro Help .................................................................................................................... 5 Before you run macros ................................................................................................................ 5 Run the macro ............................................................................................................................. 5 Run a macro by pressing a CTRL combination shortcut key ................................................... 6 Run a macro by clicking a button on the Quick Access Toolbar .............................................. 6 Run a macro by clicking a button in a custom group on the ribbon ....................................... 6 Run a macro by clicking an area on a graphic object .............................................................. 7 Configure a macro to run automatically upon opening a workbook ...................................... 7 Class Exercise ................................................................................................................................... 8
Pandora Rose Cowart
Education Coordinator Academic Health Center Training
C3‐013 Communicore PO Box 100152 Gainesville, FL 32610‐0152
Updated: 2/16/2016
(352) 273‐5051
[email protected] http://training.health.ufl.edu
Macros In Microsoft Office, a macro is a list of instructions that can be executed from a single command. This tool is used to help automate repetitive tasks. Macros are recorded in the Visual Basic for Applications (VBA) programming language. In some programs like Microsoft Access you have to provide the list of actions to perform, but programs like Word and Excel have a "learn as you go" approach. When you Record a Macro in Excel, the program will write the VBA code that contains every action you perform until you click Stop. Every action includes any mistakes you make along the way, so planning the macro is essential to success. It's rare to have a perfect plan the first time through, and building a macro often takes many trial and error attempts. Knowing the shortcuts of navigating in Excel can goes a long way to a smooth Macro creation. Moving Between Cells Navigating using the keyboard Enter Key The Enter (sometimes called the Return) key will move the focus down one cell. If you are in Edit or Enter mode, Excel will accept changes and move down. ‐
Using the Shift key and the Enter key will move the focus up one cell.
‐
Using the Ctrl key and the Enter key will accept changes and remain on the current cell. This is the same as clicking the in front of the formula bar. ‐
‐
Inside a selection Ctrl‐Enter will fill in the contents of the cell being edited into every cell in the selection.
Using the Alt key and the Enter key will put in a hard return; that is, it forces a cell to put text on a second line.
Tab Key The Tab key moves the focus one cell to the right. If you are in Edit or Enter mode, Excel will accept changes and move right. ‐
Using the Shift key and the Tab will move the focus one cell left.
‐
Using the Ctrl key and the Tab will move you to another Workbook (another Excel file).
‐
Using the Alt key and the Tab will move you to another open program on your computer.
Page Up and Page Down Keys The Page Up and Page Down keys will move the focus of the current cell to the last visible cell on the previous screen, that is, it moves down one "screenful" of cells. ‐
Using the Ctrl key and the Page Up or Page Down keys will move to the previous or next Worksheet, respectfully.
‐
Using the Alt key and the Page Up or Page Down keys moves a "screenful" left or right, respectively.
Page 1
Arrow Keys In Ready mode and in Enter mode the arrow keys will move in the direction they point: up, down, left and right by one cell, respectively. ‐ Using the Shift key and the arrows will select a range of cells as you move. ‐
Using the Ctrl key and the arrows will move the current cell to the edge of the current data region (where the data stops/starts). ‐
Using both the Shift‐Ctrl and the arrows keys will select all the cells from the current cell to the edge of the current data region.
In Edit mode the arrow keys will move you up, down, left and right between the letters and lines within the cell (if you only have one line of text the up and down arrows have no effect). ‐ Using the Shift key and the arrows will select text in that direction ‐
Using the Ctrl key and the left and right arrows will move word by word ‐
Shift‐Ctrl and the left and right arrows will select text, word by word
Home Key In Ready and Enter mode the Home key moves to the first column within that row. ‐ Using the Shift key and the Home key will select from the current cell to the first column within that row. ‐
Using the Ctrl key and the Home key will move to the very top of your worksheet. ‐
Shift‐Ctrl‐Home will select from the current cell to the very top of the worksheet
In Edit mode the Home key moves to the beginning of the cell. ‐ Using the Shift key and the Home key will select from where the cursor is blinking to the beginning of the cell.
End Key
In Ready mode the End key turns on an End feature. You will see the "END" appear on the status bar. Once it's on, you can then press an arrow key and it will search in that direction for a change in the cell pattern (with/without data). A similar method can be used by simply using the Ctrl key and the directional arrow. ‐ Ctrl‐End moves to the last working cell on the worksheet, which is the cell at the intersection of the right‐most used column and the bottom‐most used row (in the lower‐right corner) ‐
Shift‐Ctrl‐End will select from the current cell to the last (working) cell on the spreadsheet
In Edit mode the End key moves to the end of the cell. ‐ Using the Shift key and the End key will select from where the cursor is blinking to the end of the cell.
Escape Key The Esc (escape) key is the cancel button; if you are editing/entering data in a cell, escape will ignore any changes you have made and return you to the original data or blank cell. This is the same as clicking the x in front of the formula bar. Esc will close dialog boxes such as open, save as.
Page 2
Navigating Using the Mouse and Keyboard Shift‐Click If you use Shift key and the mouse you can select a range (a set of conjoining cells). Click where you want to start, hold down the Shift key and click where you want to stop. Every cell in between will be selected.
Ctrl‐Click If you use the Ctrl key and the mouse you can select various cells (skipping ones in between). You cannot use these for equations but you can apply formatting (i.e. change several cells to red text).
Keyboard Shortcuts Macros can be assigned to a keyboard shortcut. It has to be a single letter, but upper case and lowercase are looked at differently. This gives us 52 shortcut possibilities. The macro shortcuts replace the standard shortcuts. Remember, while you may not use a shortcut, other people may use them all the time. Ctrl‐A – Select All (Once current range, twice Ctrl‐Shift‐A – inserts the argument names right entire worksheet) of a function name in a formula Ctrl‐B – Toggle Bold Ctrl‐Shift‐B – Unassigned Ctrl‐C – Copy Selected Cells Ctrl‐Shift‐C – Unassigned Ctrl‐D – Fill down Ctrl‐Shift‐D – Unassigned Ctrl‐E – Unassigned Ctrl‐Shift‐E – Unassigned Ctrl‐F – Find Ctrl‐Shift‐F – Format Cells dialog box ‐ FONT Ctrl‐G – Goto Ctrl‐Shift‐G – Unassigned Ctrl‐H – Replace Ctrl‐Shift‐H – Unassigned Ctrl‐I – Toggle Italics Ctrl‐Shift‐I – Unassigned Ctrl‐J – Unassigned Ctrl‐Shift‐J – Unassigned Ctrl‐K – Hyperlink Ctrl‐Shift‐K – Unassigned Ctrl‐L – Create a Table Ctrl‐Shift‐L – Unassigned Ctrl‐Shift‐M – Unassigned Ctrl‐M – Unassigned Ctrl‐Shift‐N – Unassigned Ctrl‐N – New Workbook Ctrl‐Shift‐O – Selects all cells with Comments Ctrl‐O – Open Workbook Ctrl‐Shift‐P – Format Cells dialog box ‐ FONT Ctrl‐P – Print Ctrl‐Shift‐R – Unassigned Ctrl‐R – Fill to the right Ctrl‐Shift‐S – Unassigned Ctrl‐S – Save Ctrl‐Shift‐T – Unassigned Ctrl‐T – Create Table Dialog box Ctrl‐U – Toggle Underline Ctrl‐Shift‐U – Expands/Collapses formula bar Ctrl‐V – Paste Ctrl‐Shift‐V – Unassigned Ctrl‐W – Close Workbook Ctrl‐Shift‐W – Unassigned Ctrl‐X – Cut Ctrl‐Shift‐X – Unassigned Ctrl‐Y – Redo/Repeat the last action Ctrl‐Shift‐Y – Unassigned Ctrl‐Z – Undo Ctrl‐Shift‐Z – Unassigned Page 3
Record a new macro From the View tab, choose Macros, Record Macro…
The first step is to give your macro a descriptive name. You cannot use spaces, dashes, periods, or other special characters in your macro name. The name cannot begin with a number. Common practice is to use Underscores (_) to represent spaces, or to use alternating capitalization (CamelCaps). Print_This_Sheet ~ PrintThisSheet
Next decide where to store the macro. You can save it in the current workbook (This Workbook). This choice will attach this macro to the current workbook, and it will only be accessible when this workbook is open. If you want to be able to use the macro from any workbook on that computer, choose the "Personal Macro Workbook". The Shortcut key is optional, but most people find them very useful. The Ctrl is understood here, so if you want to assign it to the letter R, type in only the R, be mindful of your capitalization. The Description is optional, but it's good practice to explain why this macro was created and what it is supposed to do. This is also a great place to put who created the macro.
Page 4
Excel 2010 Macro Help There are several ways to run a macro in Microsoft Excel. A macro is an action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language. You can always run a macro by clicking the Macros command on the ribbon (Developer tab, Code group). Depending on how a macro is assigned to run, you might also be able to run it by pressing a CTRL combination shortcut key, by clicking a button on the Quick Access Toolbar or in a custom group on the ribbon. or by clicking an area on an object, graphic, or control. In addition, you can run a macro automatically when you open a workbook. Note When you set the macro security level in Excel to Disable all macros without notification, Excel will run only those macros that are digitally signed or stored in a trusted location, such as the Excel startup folder on your computer. If the macro that you want to run is not digitally signed or located in a trusted location, you can temporarily change the security level that enables all macros. Before you run macros You may first need to change a few settings in Excel before you can run macros: 1. If the Developer tab is not available, do the following to display it: a. Click the File tab, click Options, and then click the Customize Ribbon category. b. In the Main Tabs list, select the Developer check box, and then click OK. 2. To set the security level temporarily to enable all macros, do the following: a. On the Developer tab, in the Code group, click Macro Security.
b. In the Macro Settings category, under Macro Settings, click Enable all macros (not recommended; potentially dangerous code can run), and then click OK. Note To help prevent potentially dangerous code from running, we recommend that you return to any one of the settings that disable all macros after you finish working with macros. Run the macro 1. Open the workbook that contains the macro. 2. On the Developer tab, in the Code group, click Macros. 3. In the Macro name box, click the macro that you want to run.
4. Do one of the following: a. To run a macro in an Excel workbook, click Run. Tip You can also press CTRL+F8 to run the macro. You can interrupt the execution of the macro by pressing ESC. b. To run a macro from a Microsoft Visual Basic for Applications (VBA) module, click Edit, and then on the Run menu, click Run Sub/UserForm , or press F5. Page 5
Run a macro by pressing a CTRL combination shortcut key 1. If the Developer tab is not available, do the following to display it: a. Click the File tab, click Options, and then click the Customize Ribbon category. b. In the Main Tabs list, select the Developer check box, and then click OK. 2. On the Developer tab, in the Code group, click Macros. 3. In the Macro name box, click the macro that you want to assign to a CTRL combination shortcut key. 4. Click Options. The Macro Options dialog box appears. 5. In the Shortcut key box, type any lowercase letter or uppercase letter that you want to use with the CTRL key. Note The shortcut key will override any equivalent default Excel shortcut key while the workbook that contains the macro is open. For a list of CTRL combination shortcut keys that are already assigned in Excel, see the article Excel shortcut and function keys. 6. In the Description box, type a description of the macro. 7. Click OK to save your changes, and then click Cancel to close the Macro dialog box.
Run a macro by clicking a button on the Quick Access Toolbar To add a button to the Quick Access Toolbar that will run a macro, do the following: 1. Click the File tab, Options, and then click Quick Access Toolbar. 2. In the Choose commands from list, select Macros. 3. In the list, click the macro that you created, and then click Add. 4. To change the button image of the macro, select the macro in the box to which it was added, and then click Modify. 5. Under Symbol, click the button image that you want to use. 6. To change the name of the macro that is displayed when you rest the pointer on the button, in the Display name box, type the name that you want to use. 7. Click OK to add the macro button to the Quick Access Toolbar. 8. On the Quick Access Toolbar, click the macro button that you just added.
Run a macro by clicking a button in a custom group on the ribbon By taking advantage of the customizability of the ribbon in Excel 2010, you can create a custom group that appears on a tab in the ribbon, and then assign a macro to a button in that group. For example, you can add a custom group named "My Macros" to the Developer tab, and then add a macro (that appears as a button) to the new group.
Page 6
Run a macro by clicking an area on a graphic object You can create a hot spot on a graphic that users can click to run a macro. 1. In the worksheet, insert a graphic object, such as a picture, clip art, shape, or SmartArt. To learn about inserting a graphic object, see Add, change, or delete shapes. 2. To create a hot spot on the existing object, on the Insert tab, in the Illustrations group, click Shapes, select the shape that you want to use, and then draw that shape on the existing object.
3. Right‐click the hot spot that you created, and then click Assign Macro. 4. Do one of the following: o To assign an existing macro to the graphic object, double‐click the macro or enter its name in the Macro name box. o To record a new macro to assign to the selected graphic object, click Record, type a name for the macro in the Record Macro dialog box, and then click OK to begin recording your macro. When you finish recording the macro, click Stop Recording the Developer tab in the Code group. o
on
Tip You can also click Stop Recording on the left side of the status bar. To edit an existing macro, click the name of the macro in the Macro name box, and then click Edit.
5. Click OK. 6. In the worksheet, select the hot spot. This displays the Drawing Tools, adding a Format tab. 7. On the Format tab, in the Shape Styles group, click the arrow next to Shape Fill, and then click No Fill.
8. Click the arrow next to Shape Outline, and then click No Outline.
Configure a macro to run automatically upon opening a workbook If you record a macro and save it with the name "Auto_Open," the macro will run whenever you open the workbook that contains the macro. Another way to automatically run a macro when you open a workbook is to write a VBA procedure in the Open event of the workbook by using the Visual Basic Editor. The Open event is a built‐in workbook event that runs its macro code every time you open the workbook.
Page 7
Class Exercise Open Excel Discuss Personal.xls vs. This Workbook Open KRats01.txt, click Finish Record New Macro - View Tab ‐> Macros ‐> Record Macro o Name: CleanRat o Save in personal.xls - Select Column B, Format Date - Select Column D, Format number one decimal - Select Row 1, Bold - Select All, Autofit Columns - Control‐Home - STOP - Close without saving Test Macro - Open KRats01.txt, click Finish - Run the Macro Make a Button - Customize Quick Access Toolbar - More Commands - Choose Commands From: Macros - Find your macro - Add >> - Select the Macro - Modify… Button at the bottom of the pane Test button - Open KRats02.txt, click Finish - Run the Macro - Open KRats03.txt, click Finish - Run the Macro - Close All without saving Remove Button - Right‐click on button to remove from quick access toolbar Delete Macro - View Tab ‐>Macro‐>View Macros - Try to delete CleanRat - Click ok to error, and cancel - View Tab ‐> Windows‐>Unhide‐>Personal.xls - Tools‐>Macro‐>Macros - Delete CleanRat Page 8
- Windows‐>Hide Create a new Worksheet Talk about moving around excel using the keyboard. - Practice with Ctrl arrows - Practice with Shift‐Ctrl arrows Turn on Developer Tab - Right‐click on the ribbon - Choose Customize Ribbon - Turn on checkbox for Developer Create new macro - Developer Tab ‐> Record Macro o Macro Name: Test o Shortcut Key: Ctrl‐q o Store Macro in: This Workbook - B2: abc – blue - C3: def – red - D4: 123 – green - STOP Run Macro - Move to Sheet 2 - Run Macro twice (ctrl‐q) - Move to Sheet 3 - Run Macro (ctrl‐q) Close and don't save the file. Create new macro - Developer Tab ‐> Record Macro o Macro Name: Test o Shortcut Key: Ctrl‐q o Store Macro in: This Workbook - Developer Tab: USE RELATIVE REFERENCES - B2: abc – blue - C3: def – red - D4: 123 – green - STOP Run Macro - Move to Sheet 2 - Run Macro three times (ctrl‐q) Close and don't save the file.
Page 9
Open Customers.xls Create Macro - Click on Worksheet K - Tools‐>Macro‐>Record New Macros... o Name: Consolidate o Save in this Workbook o assign Ctrl‐q - relative off - Ctrl Home - down one cell, shift‐ctrl‐down, shift‐ctrl‐right - copy - Click on Sheet J - Ctrl‐Home - Ctrl‐down - down - paste - Ctrl‐Home - STOP Test macro - Click on Worksheet L - Ctrl‐q - Click on Worksheet M - Ctrl‐q Close without saving Open Customers.xls Create Macro - Click on Worksheet K - New Macro o Name: Consolidate o Save in this Workbook o assign Ctrl‐w - Use Relative References - Ctrl Home - down one cell, shift‐ctrl‐down, shift‐ctrl‐right - copy - Click on Sheet J - Ctrl‐Home - Ctrl‐down - down - paste - Ctrl‐Home - STOP Test macro - Click on Worksheet L, Ctrl‐q - Click on Worksheet M, Ctrl‐q Page 10