Excel® Macros For Dummies®, 2nd Edition
Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, www.wiley.com
Copyright © 2017 by John Wiley & Sons, Inc., Hoboken, New Jersey
Published simultaneously in Canada
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without the prior written permission of the Publisher. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions.
Trademarks: Wiley, For Dummies, the Dummies Man logo, Dummies.com, Making Everything Easier, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and may not be used without written permission. Microsoft and Excel are registered trademarks of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ. FULFILLMENT OF EACH COUPON OFFER IS THE SOLE RESPONSIBILITY OF THE OFFEROR.
For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002. For technical support, please visit https://hub.wiley.com/community/support/dummies.
Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.
Library of Congress Control Number: 2017931733
ISBN: 978-1-119-36924-0
ISBN: 978-1-119-36926-4 (ePDF)
ISBN: 978-1-119-36927-1 (ePub)
In its broadest sense, a macro is a sequence of instructions that automates some aspect of Excel so that you can work more efficiently and with fewer errors. You might create a macro, for example, to format and print a month-end sales report. After you develop the macro, you can execute it to perform many time-consuming procedures automatically.
Macros are written in VBA, which stands for Visual Basic for Applications. VBA is a programming language developed by Microsoft and a tool used to develop programs that control Excel.
Excel programming terminology can be a bit confusing. For example, VBA is a programming language but also serves as a macro language. What do you call something written in VBA and executed in Excel? Is it a macro or is it a program? Excel’s Help system often refers to VBA procedures as macros, so this is the terminology used in this book.
You’ll also see the term automate throughout this book. This word means that a series of steps are completed automatically. For example, if you write a macro that adds color to some cells, prints the worksheet, and then removes the color, you have automated those three steps.
You’re probably aware that people use Excel for thousands of different tasks. Here are just a few examples:
The list could go on and on. The point is simply that Excel is used for a wide variety of tasks, and everyone reading this book has different needs and expectations regarding Excel. One thing most readers have in common, however, is the need to automate some aspect of Excel, which is what macros (and this book) are all about.
This book approaches the topic of Excel macros with the recognition that programming VBA takes time and practice — time that you may not have right now. In fact, many analysts don’t have the luxury of taking a few weeks to become expert at VBA. So instead of the same general overview of VBA topics, this book provides some of the most commonly used real-world Excel macros.
Each section in the book outlines a common problem and provides an Excel macro to solve the problem — along with a detailed explanation of how the macro works and where to use it.
Each section presents the following:
After reading each section, you'll be able to
The macros in this book are designed to get you up and running with VBA in the quickest way possible. Each macro tackles a common task that benefits from automation. The idea here is to learn through application. This book is designed so that you can implement the macro while getting a clear understanding of what the macro does and how it works.
I make three assumptions about you as the reader:
www.dummies.com/go/excelmacros.In addition to the material in the print or e-book you’re reading, this product comes with more online goodies:
Sample files: Each macro in this book has an associated sample file that enables you to see the macro working and to review the code. You can use the sample files also to copy and paste the code into your environment (as opposed to typing each macro from scratch). Download the sample files at:
www.dummies.com/go/excelmacros
Each macro in this book has detailed instructions on where to copy and paste the code. In general terms, you open the sample file associated with the macro, go to the Visual Basic Editor (by pressing Alt+F11), and copy the code. Then you go to your workbook, open the Visual Basic Editor, and paste the code in the appropriate location.
Note that in some macros, you need to change the macro to suit your situation. For example, in the macro that prints all workbooks in a directory (see Chapter 4), you point to the C:\Temp\ directory. Before using this macro, you must edit it to point to your target directory.
If a macro is not working for you, most likely a component of the macro needs to be changed. Pay special attention to range addresses, directory names, and any other hard-coded names.
www.dummies.com and searching for “Excel Macros Cheat Sheet”.If you’re completely new to Excel macros, start with Part 1 (Chapters 1 – 3) to get the fundamentals you’ll need to leverage the macros in this book. There, you will gain a concise understanding of how macros and VBA work, along with the basic foundation you need to implement the macros provided in this book.
If you’ve got some macro experience and want to dive right into the macro examples, feel free to peruse Chapters 4 – 9 and search for the task or macro that looks interesting to you. Don’t worry. Each macro example stands on its own within its own section that gives you all the guidance you need to understand and implement the code in your own workbook.
Visit Part 2 if you’re interested in macros that automate common workbook and worksheet tasks to save time and gain efficiencies.
Explore Part 3 to find macros that navigate ranges, format cells, and manipulate the data in your workbooks.
If you want to find macros that work with PivotTables, charts, and emails, thumb through the macros in Part 4 where you will discover macros that automate redundant PivotTable and chart tasks, as well as macros that send emails and connect to external data sources.
Don’t forget to hit Part 5 for some useful tips and advice on how to get the most out of your new macro skills.
Here are some final things to keep in mind while working with the macros in this book:
Part 1
IN THIS PART …
Build a foundation for your macro skills with fundamental macro recording concepts.
Get a solid understanding of the ground rules for using and distributing macros in Excel.
Explore Excel’s coding environment with a deep-dive of the Visual Basic Editor.
Explore how to leverage the Excel object model to start writing your own macros from scratch.
Understand the roles played by variables, events, and error handling in macro development.
Chapter 1
IN THIS CHAPTER
Why use macros
Recording macros
Understanding macro security
Examples of macros in action
A macro is essentially a set of instructions or code that you create to tell Excel to execute any number of actions. In Excel, macros can be written or recorded. The key word here is recorded.
Recording a macro is like programming a phone number into your cell phone. You first manually dial and save a number. Then when you want, you can redial those numbers with the touch of a button. Just as on a cell phone, you can record your actions in Excel while you perform them. While you record, Excel gets busy in the background, translating your keystrokes and mouse clicks to written code (also known as Visual Basic for Applications (VBA)). After a macro is recorded, you can play back those actions anytime you want.
In this chapter, you’ll explore macros and learn how you can use macros to automate your recurring processes to simplify your life.
The first step in using macros is admitting you have a problem. Actually, you may have several problems:
To start recording your first macro, you need to first find the Macro Recorder, which is on the Developer tab. Unfortunately, Excel comes out of the box with the Developer tab hidden — you may not see it on your version of Excel at first. If you plan to work with VBA macros, you'll want to make sure that the Developer tab is visible. To display this tab
Now that you have the Developer tab showing in the Excel Ribbon, you can start up the Macro Recorder by selecting Record Macro from the Developer tab. This activates the Record Macro dialog box, as shown in Figure 1-1.
FIGURE 1-1: The Record Macro dialog box.
Here are the four parts of the Record Macro dialog box:
With the Record Macro dialog box open, follow these steps to create a simple macro that enters your name into a worksheet cell:
Enter a new single-word name for the macro to replace the default Macro1 name.
A good name for this example is MyName.
Assign this macro to the shortcut key Ctrl+Shift+N.
You do this by entering uppercase N in the edit box labeled Shortcut Key.
Click OK.
This closes the Record Macro dialog box and begins recording your actions.
The macro was recorded in a new module named Module1. To view the code in this module, you must activate the Visual Basic Editor. You can activate the VB Editor in either of two ways:
In the VB Editor, the Project window displays a list of all open workbooks and add-ins. This list is displayed as a tree diagram, which you can expand or collapse. The code that you recorded previously is stored in Module1 in the current workbook. When you double-click Module1, the code in the module appears in the Code window.
The macro should look something like this:
Sub MyName()'' MyName Macro'' Keyboard Shortcut: Ctrl+Shift+N' ActiveCell.FormulaR1C1 = "Michael Alexander"End Sub
The macro recorded is a Sub procedure named MyName. The statements tell Excel what to do when the macro is executed.
Notice that Excel inserted some comments at the top of the procedure. These comments are some of the information that appeared in the Record Macro dialog box. These comment lines (which begin with an apostrophe) aren’t really necessary, and deleting them has no effect on how the macro runs. If you ignore the comments, you'll see that this procedure has only one VBA statement:
ActiveCell.FormulaR1C1 = "Michael Alexander"
This single statement causes the name you typed while recording to be inserted into the active cell.
Before you recorded this macro, you set an option that assigned the macro to the Ctrl+Shift+N shortcut key combination. To test the macro, return to Excel by using either of the following methods:
When Excel is active, activate a worksheet. (It can be in the workbook that contains the VBA module or in any other workbook.) Select a cell and press Ctrl+Shift+N. The macro immediately enters your name into the cell.
After you record a macro, you can make changes to it (although you must know what you’re doing). For example, assume that you want your name to be bold. You could re-record the macro, but this modification is simple, so editing the code is more efficient. Press Alt+F11 to activate the VB Editor window. Then activate Module1 and insert the following statement before the End Sub statement:
ActiveCell.Font.Bold = True
The edited macro appears as follows:
Sub MyName()'' MyName Macro'' Keyboard Shortcut: Ctrl+Shift+N' ActiveCell.Font.Bold = True ActiveCell.FormulaR1C1 = "Michael Alexander"End Sub
Test this new macro, and you see that it performs as it should.
Now that you’ve read about the basics of the Macro Recorder interface, it’s time to go deeper and begin recording macros. The first thing you need to understand before you begin is that Excel has two modes for recording — absolute reference and relative reference.
Excel’s default recording mode is in absolute reference. As you may know, the term absolute reference is often used in the context of cell references found in formulas. When a cell reference in a formula is an absolute reference, it does not automatically adjust when the formula is pasted to a new location.
The best way to understand how this concept applies to macros is to try it out. Open the Chapter 1 Sample File.xlsx file and record a macro that counts the rows in the Branchlist worksheet. (See Figure 1-2.)
FIGURE 1-2: Your pre-totaled worksheet containing two tables.
Follow these steps to record the macro:
Click OK to start recording.
At this point, Excel is recording your actions. While Excel is recording, perform the following steps:
Select the first empty cell in Column D (D16) and enter = COUNTA(D2:D15).
This gives a count of branch numbers at the bottom of column D. You need to use the COUNTA function because the branch numbers are stored as text.
The formatted worksheet should look something like the one in Figure 1-3.
FIGURE 1-3: Your post-totaled worksheet.
To see your macro in action, delete the total row you just added and play back your macro by following these steps:
If all goes well, the macro plays back your actions to a T and gives your table a total. Now here’s the thing: No matter how hard you try, you can’t make the AddTotal macro work on the second table (G1:I15 in Figure 1-3). Why? Because you recorded it as an absolute macro.
To understand what this means, examine the underlying code. To examine the code, select Macros from the Developer tab to get the Macro dialog box you see in Figure 1-4.
FIGURE 1-4: The Excel Macro dialog box.
Select the AddTotal macro and click the Edit button. This opens the Visual Basic Editor to show you the code that was written when you recorded your macro:
Sub AddTotal() Range("A16").Select ActiveCell.FormulaR1C1 = "Total" Range("D16").Select ActiveCell.FormulaR1C1 = "=COUNTA(R[-14]C:R[-1]C)"End Sub
Pay particular attention to lines 2 and 4 of the macro. When you asked Excel to select cell range A16 and then D16, those cells are exactly what it selected. Because the macro was recorded in absolute reference mode, Excel interpreted your range selection as absolute. In other words, if you select cell A16, that cell is what Excel gives you. In the next section, you take a look at what the same macro looks like when recorded in relative reference mode.
In the context of Excel macros, relative means relative to the currently active cell. So you should use caution with your active cell choice — both when you record the relative reference macro and when you run it.
First, make sure the Chapter 1 Sample File.xlsx file is open. Then, use the following steps to record a relative-reference macro:
FIGURE 1-5: Recording a macro with relative references.
At this point, you have recorded two macros. Take a moment to examine the code for your newly created macro.
Select Macros from the Developer tab to open the Macro dialog box. Here, choose the AddTotalRelative macro and click Edit.
Again, this opens the Visual Basic Editor to show you the code that was written when you recorded your macro. This time, your code looks something like the following:
Sub AddTotalRelative() ActiveCell.Offset(15, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "Total" ActiveCell.Offset(0, 3).Range("A1").Select ActiveCell.FormulaR1C1 = "=COUNTA(R[-14]C:R[-1]C)"End Sub
Notice that there are no references to any specific cell ranges at all (other than the starting point “A1”). Let’s take a quick look at what the relevant parts of this VBA code really mean.
Notice that in line 2, Excel uses the Offset property of the active cell. This property tells the cursor to move a certain number of cells up or down and a certain number of cells left or right.
The Offset property code tells Excel to move 15 rows down and 0 columns across from the active cell (in this case, A1). There’s no need for Excel to explicitly select a cell as it did when recording an absolute reference macro.
To see this macro in action, delete the total row for both tables and do the following:
Notice that this macro, unlike your previous macro, works on both sets of data. Because the macro applies the totals relative to the currently active cell, the totals are applied correctly.
For this macro to work, you simply need to ensure that
Hopefully, this simple example has given you a firm grasp of macro recording with both absolute and relative references.