Microsoft Mastering

®

Excel Macros ®

© 2015 The Continuing Education Center, Inc. d/b/a National Seminars Training. All rights reserved, including the right to reproduce this material or any part thereof in any manner.

MASTERING MICROSOFT ® EXCEL® MACROS

In this webinar, you will learn: 1. Why you’ll need (and want) to use macros 2. How you can record a macro in a few simple steps

Why You Need Macros

• As you work with Excel, you may find yourself repeating the same sequence of commands and actions, for example: — — — — —

Updating sales figures on multiple worksheets Plotting the latest monthly data on a chart Applying special formats Locating cells with formulas or cells with values Reconfiguring a mailing label address layout into a database layout

3. How you can store macros in your own personal workbook

• With a macro, you can store these sets of procedures and not need to repeat the entire sequence of actions each time you need them performed.

4. How to quickly run a macro with a keystroke shortcut or icon

• You will save a good deal of time and effort and improve accuracy by creating macros.

5. How to add buttons to the Quick Access toolbar for rapid macro access

Sample Short Macros

1. Apply a special numeric format that has no button equivalent in the Ribbon.

6. How to create an interactive macro

2. Select all cells that contain formulas or functions.

7. How to debug a macro with the step method

4. Use a special format for displaying values in millions.

3. Apply light-gray border lines to cells with color backgrounds. 5. Print a selected area. 6. Create subtotals for a group of cells. 7. Turn Double Accounting Underlining on or off. 8. Select all blank cells within a selected range. 9. Round cell values on the actual cells instead of writing/copying formulas and using additional columns. 10. Hide a row if values exceed a certain amount. Multistep Macros

1. Remove records of employees whose termination dates have passed. 2. Append these records to an archival database. 3. Sort the database by department and name. 4. Add subtotals to create new summary totals by department. 5. Print the list showing the detail and summary together. 6. Collapse the list and print just the summary. 7. Remove the subtotals and sort the full database by employee name. 8. Print the results. 9. Sort the database by date of hire. 10. Print those results.

WTEXL051515

2

PARTICIPANT NOTEBOOK

How Do You Create a Macro? There are three primary ways to create a macro: 1. Record the various commands and keystrokes that comprise the task(s) you want to automate. These actions get translated into the coding language VBA — Visual Basic for Applications. 2. Type macro code using VBA. 3. Copy macro code from existing macros or from Help screens. The recording method is the dominant technique for creating macros, but it does not cover all possible actions you want a macro to perform. You’ll need to type certain macro statements that have no equivalent recording method; structures such as Do Loops, For Statements, and If…Then…Else, must be typed or copied into your macro code. With some macros, you’ll use a combination of all three techniques.

Steps to Record a Macro Method One

1. In the Ribbon, click the drop arrow beneath the Macros button in the Macros group on the View tab. 2. Click Record Macro….

Method Two

• Click the button just to the right of the word Ready on the left side of the Status bar at the bottom of the screen.

3

WTEXL051515

MASTERING MICROSOFT ® EXCEL® MACROS

The Record Macro Dialog Box 1. Provide a macro name. a. No spaces b. Cannot begin with a number c. Use the underscore character or uppercase letters to simulate word breaks. d. Limited to 255 characters e. Avoid special characters, except for underscore, as a word separator. 2. If desired, provide a shortcut key combination. All combinations automatically use the Ctrl key. Hold down the Shift key to create a combination, such as Ctrl+Shift+G as a shortcut. Avoid lowercase letters so as to not replace commonly used shortcuts such as Ctrl+C for Copy and Ctrl+V for Paste. 3. Select a location to store the macro. Store in This Workbook if you intend to use it only in the current workbook. Store in Personal Macro Workbook if you want it to be available in any workbook. 4. Type a description of what the macro does. It’s not critical, but it’s helpful to you and others. 5. Click OK to begin recording. Record Your Steps

• Perform all the actions that you want stored in the macro. • To stop recording: 1. In the Ribbon, click the drop arrow beneath the Macros button in the Macros group on the View tab.

Steps to record when creating a macro that adds color to all worksheet cells with numbers (not formulas):

2. Click Stop Recording.

1. On the Home tab of the Ribbon, click the Find & Select button in the Editing group.

or Click the button just to the right of the word Ready on the left side of the Status bar at the bottom of the screen.

2. Click Go To Special…. 3. Click the button next to Constants. 4. Clear the check boxes for Text, Logicals, and Errors. 5. Click OK. 6. Click the Fill Color drop arrow in the Font group on the Home tab of the Ribbon. 7. Click a color.

WTEXL051515

4

PARTICIPANT NOTEBOOK

Some Macros Require Typing VBA Code

Five Different Ways to Run a Macro

Record a macro to toggle Double Accounting Underlining on or off. Only two steps can be recorded:

1. From the Macros button on the View tab

1. Turn Double Accounting Underlining off.

3. From a new button you placed on the Quick Access Toolbar

2. With a keystroke combination that you previously defined

2. Turn Double Accounting Underlining on.

4. From a graphic object, ClipArt, or picture placed on your worksheet

Some Macros Rapidly Perform Lengthy, Complicated Tasks

5. After activating the Developer tab in the Ribbon, select the Macros button in the Code group of the Developer tab.

A macro to transpose data in mailing label layout into Excel database (list) layout

• Methods 2 and 3 are ideal for Personal Macro Workbook macros.

• Although mostly created by recording, this macro, too, requires adding additional code to automate a basic procedure so that it gets performed many times.

• Method 4 is best for macros designed for a specific workbook.

• This macro is representative of those kinds of macros that handle huge amounts of data; it performs a repetitive task with amazing speed.

Assign a Macro to the Quick Access Toolbar (QAT) 1. Right-click the QAT and select Customize Quick Access Toolbar.

The Personal Macro Workbook

2. Click the arrow to the right of Popular Commands and click Macros.

• The Personal Macro Workbook does not exist until the first time you store a macro there. Excel then creates this file and stores it in the XLSTART folder. Upon a normal exit from Excel, a prompt asks you to save this workbook.

3. From the list of macros, click the macro to be associated with the new button. 4. Click the Add button and then the Modify button. 5. Click one of the 181 buttons in the Modify Button dialog box.

• The Personal Macro Workbook, once created, is a hidden workbook that is always open (but usually hidden) when Excel is running.

6. Click OK twice.

• There is usually no reason to unhide this file. • Any macro stored here is always available, because this workbook is automatically opened every time you start Excel.

5

WTEXL051515

MASTERING MICROSOFT ® EXCEL® MACROS

Run a Macro From a Clip Art Image 1. Click the Insert tab in the Ribbon. 2. Click Picture or ClipArt. 3. Browse folders to locate a picture or use the ClipArt dialog box to locate an image. 4. Double-click the desired picture or image. 5. Right-click the image and select Assign Macro. 6. Double-click the name of the macro to be represented by the button.

WTEXL051515

6

PARTICIPANT NOTEBOOK

The Visual Basic Editor

Running a Macro in Step Mode

1. Press Alt+F11 to get to the Visual Basic Editor.

To run a macro step by step:

2. VBA Project Explorer occupies most of the left side.

1. Click in the macro code and press the F8 key. 2. Press F8 to step to the next line of code — the code line is highlighted in yellow.

3. The Code window occupies most of the right side. 4. You might also see these windows:

3. Press the Reset button if you want to stop the macro at any point.

a. Properties — lower left corner

4. Press F5 and the macro will complete the remaining steps without stopping.

b. Watch — lower right corner 5. Mostly, your focus is on the Code window and the Project Explorer window. 6. Press Alt+F11 to go back to the Excel worksheet environment.

Make a Macro Interactive To make a macro more flexible, such as allowing it to prompt for user input, you can type the VBA statement InputBox into a macro, as in the following example that prompts the user to type a sheet name. The macro then activates that sheet and processes the data there.

Visual Basic Code • Macro code has syntax and rules, just like spoken languages.

SheetName = InputBox("SheetSelector", "Type sheet Name")

• Most macros contain code generated by recording, so you should have some idea how to read the code if you recorded the macro.

Worksheets(SheetName).Activate

• A macro begins and ends with the word Sub. • The single-quote character and everything to the right of it on a line is a comment. These lines are automatically green. • Other lines comprise the executable code of the macro.

7

WTEXL051515

MASTERING MICROSOFT ® EXCEL® MACROS

Recommended Resources Books Microsoft® Access® 2010 Bible Microsoft® Excel® 2010 Formulas Microsoft® PowerPoint® 2010 Bible Microsoft® Word® 2010 Bible

CD-ROMs Mastering Microsoft® Excel® Macros Microsoft® Office® 2010 (19-CD set) Microsoft® Office® 2013

Online Training Excel 2013 Office 2013 Productivity Collection

WTEXL051515

8

BONUS MATERIALS Dear Customer, You are a valued customer and to say “thank you,” we have included the following as a bonus for you. We believe you’ll find it helpful as a job aid or to further your knowledge beyond today’s broadcast. Thank you,

Susan Enyeart Director, Curriculum and Product Development

9

WTEXL051515

MASTERING MICROSOFT ® EXCEL® MACROS

BONUS MATERIALS:

Microsoft® Excel® Keystroke Shortcuts, Function Keys, and Other Productivity Tips

WTEXL051515

10

PARTICIPANT NOTEBOOK

Entering data Start a new line in the same cell.

Alt+Enter

Create or edit a cell comment.

Shift+F2

Fill the selected cell range with the current entry — keep current cell active.

Ctrl+Enter

Menus Undo the last action.

Ctrl+Z or Alt+Bksp

Repeat the last command action.

Ctrl+Y or F4

Show a shortcut menu.

Shift+F10

Make the menu bar active.

F10 or Alt

Enter a hyperlink.

Ctrl+K

Activate the File Open dialog box.

Ctrl+O (the letter O)

Activate the File Save As dialog box.

Ctrl+S

Open a new workbook.

Ctrl+N

Insert a new worksheet to the left of the current worksheet.

Shift+F11

Insert, delete, and copy a selection Copy the selection.

Ctrl+C

Paste the selection.

Ctrl+V

Cut the selection.

Ctrl+X

Insert new row/column above/left of selected row/column.

Ctrl+Plus Sign

Delete currently selected row(s) or column(s).

Ctrl+Minus Sign

11

WTEXL051515

MASTERING MICROSOFT ® EXCEL® MACROS

Formatting data Apply general number format.

Ctrl+Shift+~ (tilde)

Apply number format, 2 decimals, commas, minus before negatives.

Ctrl+Shift+!

Apply currency format, 2 decimal places (negatives in parentheses).

Ctrl+Shift+$

Apply percentage format with no decimal places.

Ctrl+Shift+%

Apply exponential number format with two decimal places.

Ctrl+Shift+^

Apply time format with AM/PM as in 5:43 PM.

Ctrl+Shift+@

Apply date format (d-mm-yy), i.e., 9-Aug-06.

Ctrl+Shift+#

Increase indent.

Ctrl+Alt+Tab

Decrease indent.

Ctrl+Alt+Shift+Tab

Apply the outline border.

Ctrl+Shift+&

Remove all borders.

Ctrl+Shift+_

Apply or remove bold formatting.

Ctrl+B or Ctrl+2

Apply or remove italic formatting.

Ctrl+L or Ctrl+3

Apply or remove an underline.

Ctrl+U or Ctrl+4

Apply or remove strikethrough formatting.

Ctrl+5

Display the Format Style command dialog box.

Alt+' (apostrophe)

Display the Format Cells command dialog box.

Ctrl+1

Hide rows of the selected range or active cell.

Ctrl+9

Unhide rows.

Ctrl+Shift+9

Hide columns of the selected range or active cell.

Ctrl+0 (zero)

Unhide columns.

Ctrl+Shift+0 (zero)

Toggle display of outline numbers and symbols.

Ctrl+8

WTEXL051515

12

PARTICIPANT NOTEBOOK

Move and scroll on a worksheet or workbook Move to the upper-left corner of the current worksheet.

Ctrl+Home

Move the active cell to the lower-right corner of worksheet.

Ctrl+End

Move to the next sheet in the workbook.

Ctrl+Page Down

Move to the previous sheet in the workbook.

Ctrl+Page Up

Move down one screen.

Page Down

Move up one screen.

Page Up

Move one screen to the right.

Alt+Page Down

Move one screen to the left.

Alt+Page Up

Move to the edge of data in the current column or row.

Ctrl+ Arrow Key

Move between unlocked cells on a protected worksheet.

Tab

Move to Column A in the current row.

Home

Move to the next workbook or window.

Ctrl+F6 or Ctrl+Tab

Display the Print command (File menu).

Ctrl+P

Scroll to display the active cell.

Ctrl+Backspace

Move to the next pane or previous pane.

F6 or Shift+F6

Move to the previous workbook or window.

Ctrl+Shift+F6 or Ctrl+Shift+Tab

Charting and graphical features Create a chart on a new sheet based on the selected range.

F11

Select the next or previous group of items in a chart.

É or Ê

Select the next or previous item within a chart series.

È or Ç

Duplicate a selected object (picture, ClipArt, WordArt, etc.).

Ctrl+D

13

WTEXL051515

MASTERING MICROSOFT ® EXCEL® MACROS

Move within a selection Move downward.

Enter

Move upward.

Shift+Enter

Complete current cell entry, but keep active cell in place.

Ctrl+Enter

Move clockwise to the next corner of the selection.

Ctrl+Period

Move to the right between nonadjacent selections.

Ctrl+Alt+È

Move to the left between nonadjacent selections.

Ctrl+Alt+Ç

Select cells, columns, rows, objects, or display features in worksheets Extend the selection by one cell.

Shift+ Arrow Key

Extend the selection to the beginning of the worksheet.

Ctrl+Shift+Home

Select the entire column.

Ctrl+Space Bar

Select the entire row.

Shift+Space Bar

Select the entire worksheet.

Ctrl+A

If multiple cells are selected, select only the active cell.

Shift+Backspace

Extend the selection down one screen.

Shift+Page Down

Extend the selection up one screen.

Shift+Page Up

With an object selected, select all objects on a sheet.

Ctrl+Shift+Space Bar

Alternate hiding/displaying objects and displaying object placeholders.

Ctrl+6

Show or hide the standard toolbar.

Ctrl+7

Show or hide outlining symbols and outlining panel.

Ctrl+8

WTEXL051515

14

PARTICIPANT NOTEBOOK

Working in cells or in the formula bar Insert the AutoSum formula.

Alt+= (equal sign)

Enter the current date.

Ctrl+; (semicolon)

Enter the current time.

Ctrl+Shift+; (semicolon)

Alternate displaying cell values and displaying cell formulas.

Ctrl+~ (tilde)

Copy the value from cell above active cell into active cell.

Ctrl+Shift+" (quote)

Copy a formula from cell above active cell into active cell.

Ctrl+' (apostrophe)

Define a name.

Ctrl+F3

Calculate all sheets in all open workbooks.

F9

Calculate the active worksheet.

Shift+F9

Display AutoComplete list; also used in Data Validation lists.

Alt+Down Arrow

Toggle between absolute, relative, and mixed address designation.

F4

Select cells with special characteristics Select only visible cells in the current selection.

Alt+; (semicolon)

Select all cells that are directly dependent on the active cell.

Ctrl+[

Select all cells in any way dependent on the active cell.

Ctrl+Shift+[

Select cells that directly contribute to the active cell’s result.

Ctrl+]

Select all cells that contribute to the active cell’s result.

Ctrl+Shift+]

Select current region (range enclosed by blank rows and columns) around active cell.

Ctrl+Shift+Asterisk

Select all cells with comments.

Ctrl+Shift+O (letter O)

15

WTEXL051515

MASTERING MICROSOFT ® EXCEL® MACROS

Editing data Edit the active cell.

F2

Delete text to the end of the edit line.

Ctrl+Delete

Move edit cursor one word to left or right.

Ctrl+È or Ç

After typing a function name, show function arguments.

Ctrl+Shift+A

Move edit cursor to the beginning (left side) of the edit line.

Home

Move edit cursor to the end (right side) of the edit line.

End

Paste a name into a formula.

F3

Enter a formula as an array formula.

Ctrl+Shift+Enter

Convert the highlighted portion of a formula to its current value.

F9

Windows and dialog boxes Switch to the next program.

Alt+Tab

Show the Windows Start menu.

Ctrl+Esc

Close the active workbook window.

Ctrl+W or Ctrl+F4

Restore the active workbook window.

Ctrl+F5

Switch to the next workbook window.

Ctrl+F6

Switch to the previous workbook window.

Ctrl+Shift+F6

Minimize the workbook window to an icon.

Ctrl+F9

Maximize or restore the workbook window.

Ctrl+F10

Exit from Excel.

Alt+F4

WTEXL051515

16

PARTICIPANT NOTEBOOK

Excel Function Keys Function Keys in Excel Shift

Ctrl

Alt

F1

Display Help or the Office Assistant.

Activates the What’s This pointer

Create a chart on a new sheet (same as F11).

F2

Edit the active cell.

Enter or edit a cell comment.

File Save As Command (same as F12)

F3

Paste a name into a formula.

Paste a function into a formula.

Define a name.

F4

Repeat last action; apply abs/rel status.

Repeat the last Find Next command.

File Close command

F5

Edit Go To command

Display the Find dialog box.

Restore the window size.

F6

Move to the next pane.

Move to the previous pane.

Move to the next workbook window.

F7

Spell check.

F8

Extend a selection.

Add to the selection.

Resize the window.

F9

Calculate all sheets in all open workbooks.

Calculate the active worksheet.

Minimize the workbook.

F10

Make the menu bar active.

Display a shortcut menu.

Maximize or restore the workbook window.

F11

Create a chart on a new sheet.

Insert a new worksheet to the left of the current one.

Insert an Excel 4.0 macro sheet.

F12

File Save As command

File Save command

File Open command

Ctrl+Shift

Create names from row/column labels. File Exit command

Edit Find search backward from last.

Jump to VB editor, if open.

Move to the previous workbook window.

Move the window.

17

Display the Macro dialog box.

Display the Visual Basic Editor.

File Print command

WTEXL051515

MASTERING MICROSOFT ® EXCEL® MACROS

Excel Productivity Tips Other Timesaving Techniques

1. Hold down the Ctrl key as you roll the mouse wheel — zoom in 15% increments. 2. To enter the same data in a selected range — type an entry and press Ctrl+Enter. 3. To move/copy a range, right-drag the selection border — the menu appears when you release the mouse. 4. Align the edges of an object (picture, ClipArt, etc.) with cell boundaries — press Alt as you drag the edge. 5. To paste values from a selected range, right-drag its border and select Copy here as Values Only. 6. Press F5, type an address, and press Enter to jump to any cell. 7. Right-click the sheet navigation arrows to see up to 16 sheet names. 8. Double-click the fill handle to copy data or formula down a column. 9. Double-click a cell edge to move the active cell to the end of data in any direction.

WTEXL051515

18

PARTICIPANT NOTEBOOK

Macro Questions and Answers

4. My macros get stored in modules named Module1, Module2, etc. How can I change the name of a module so that it’s more meaningful?

1. What if I forgot to assign a keystroke shortcut to a macro, want to change an existing keystroke shortcut, or want to eliminate a keystroke shortcut?

Take these steps:

In the Ribbon, click the Macros button in the Macros group on the View tab. In the Macro dialog box, click the name of the macro whose keystroke shortcut you want to adjust (create, change, or delete), then click the Options button. In the Macro Options dialog box, make the desired adjustment.

a. In the Visual Basic Editor, if the Properties window is not visible, activate it by clicking the View menu and selecting the Properties window. b. In the Project window of the Visual Basic window, click a module whose name you want to change; this causes the name to appear in the Properties window to the right of the word Name.

2. How do I stop a macro while it’s running — particularly if it appears to be going haywire and doing things I don’t want it to be doing?

c. In the Properties window, type a new name for the module to replace the current name.

Press the Esc key.

5. I’ve got a lot of short lines in a macro. Is there a way to have two or more lines of code put on the same line?

3. How can I cut down on the number of modules and organize my macros in the Personal Macro Workbook?

At the end of a line, type a colon (:) followed by a space. Then press the Delete key as many times as necessary to bring the code up from the line below.

Here are two things you can do: a. Cut and paste macro code so that macros of the same type are in the same module. b. Right-click on a module that’s empty or no longer needed and select Remove ModuleN. In response to a dialog box asking if you want to export it, click No.

19

WTEXL051515

MASTERING MICROSOFT ® EXCEL® MACROS

6. Can I split a line of macro code onto two lines if it’s unusually long?

10. What if I create a macro and store it in the current workbook, but later I decide that I could use it in other workbooks. Can I move it to the Personal Macro Workbook?

To continue a statement from one line to the next, break the line by typing a space followed by the underscore character ( _ ). You cannot break a statement in the middle of a keyword value.

Yes, you can. In fact, it’s quite easy to copy or move macros from one workbook to another, even when the Personal Macro Workbook is one of them. Select the code in question and click the Copy or Cut button in the macro toolbar; then activate the destination module by clicking the destination file name in the Project window of the Visual Basic Editor. Click the module where you want to paste the data and then click the Paste button on the macro toolbar.

7. What if I want to test some new macro code that might be faster than what I have been using; how can I make macro code lines inoperative? If you think a line of code in a macro is unneeded, insert a single quote at the beginning of the line (this converts it into a comment) and run the macro. If the macro runs properly, remove the unneeded line. If the macro fails, remove the single quote and thereby return it to its operative state.

11. Where is the Personal Macro Workbook stored? The Personal Macro Workbook usually does not exist until the first time you store a macro there. Excel then creates this file — PERSONAL.XLS — and stores it in a folder named XLSTART.

8. I recorded a macro that does a lot of copying and pasting of data to many different locations. Do I need to see all that screen action every time I run the macro? Doesn’t it slow down the macro?

The Personal Macro Workbook, once created, is a hidden workbook that is always open (but usually hidden) when Excel is running. There is usually no reason to unhide this file.

You can hide all that screen action, and furthermore, it actually makes the macro run faster. Enter this line of code at the beginning of the macro:

Any macro stored here is always available, because the workbook is automatically opened every time you start Excel.

Application.ScreenUpdating = False

When you run the macro, you won’t see anything happening on the screen until the macro finishes. 9. How do I know if a workbook I’m using has any macros stored in it? Jump to the Visual Basic Editor (Alt+F11) and look in the Project Explorer window on the left side to see if there are any modules associated with the workbook in question. Double-click a module to view the code in the module.

WTEXL051515

20