Excel® VBA 24-Hour Trainer, Second Edition
Published by
John Wiley & Sons, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256www.wiley.com
Copyright © 2015 by John Wiley & Sons, Inc., Indianapolis, Indiana
Published simultaneously in Canada
ISBN: 978-1-118-99137-4
ISBN: 978-1-118-99140-4 (ebk)
ISBN: 978-1-118-99141-1 (ebk)
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 either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. 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.
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 Web site 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 Web site may provide or recommendations it may make. Further, readers should be aware that Internet Web sites listed in this work may have changed or disappeared between when this work was written and when it is read.
For general information on our other products and services please contact our Customer Care Department within the United States at (877) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002.
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: 2015930536
Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, Programmer to Programmer, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. Excel is a registered trademark 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.
To my father, Bill Urtis.
TOM URTIS is a Microsoft Excel MVP who has been using Excel since 1994. Tom owns Atlas Programming Management (www.atlaspm.com), a Microsoft Office solutions company specializing in Excel programming, development, testing, and training for an international clientele. As an Excel trainer, Tom created the Excel Aptitude Test (XAT®, http://xat.atlaspm.com), an innovative test that measures knowledge and aptitude of Excel for individuals and businesses.
Tom has co-authored Don't Fear The Spreadsheet (Holy Macro! Books, 2012) and Holy Macro, It's 2500 Excel VBA Examples (Holy Macro! Books, 2005), and he has served as technical editor and consultant for other Excel books and training material. Tom actively contributes to the Excel community through his blog, in forums, and with his daily Excel tips and examples on social media.
Tom is a graduate of Michigan State University. He has lived in the San Francisco Bay Area since 1983, where he enjoys the outdoor life that California offers. Tom is an avid fan of college and professional sports, and a collector of rare sports memorabilia. Tom can be reached by e-mail at tom@atlaspm.com.
Mike Alexander is a Microsoft Certified Application Developer (MCAD) and author of more than a dozen books on advanced business analysis with Microsoft Access and Excel. He has more than 16 years experience consulting and developing Office solutions. Michael has been named a Microsoft MVP for his ongoing contributions to the Excel community.
EXECUTIVE EDITOR
Carol Long
PROJECT EDITOR
Charlotte Kughen
TECHNICAL EDITOR
Michael Alexander
PRODUCTION EDITOR
Christine O'Connor
COPY EDITOR
Kim Cofer
MANAGER OF CONTENT DEVELOPMENT AND ASSEMBLY
Mary Beth Wakefield
MARKETING DIRECTOR
David Mayhew
MARKETING MANAGER
Carrie Sherrill
PROFESSIONAL TECHNOLOGY & STRATEGY DIRECTOR
Barry Pruett
BUSINESS MANAGER
Amy Knies
ASSOCIATE PUBLISHER
Jim Minatel
PROJECT COORDINATOR, COVER
Brent Savage
PROOFREADER
Josh Chase, Word One New York
INDEXER
Ted Laux
COVER DESIGNER
Wiley
COVER IMAGE
Wiley
THE PRODUCTION OF THIS BOOK WAS made possible by the combined efforts of highly talented people, starting with the entire Wiley Publishing team, all of whom are a pleasure to work with. Thanks to Carol Long, the executive editor who got the project approved, and kept the process moving from start to finish. Thanks to Technical Editor Mike Alexander, who introduced me to Wiley Publishing in 2010 when I wrote the first edition to this book. Thanks to Charlotte Kughen, the project editor; to Kim Cofer, the copy editor; and to Christine O'Connor, the production editor.
Thank you to my family and friends for your understanding and support of my book-writing schedule, and of my everyday drive for working with Excel and teaching it to others. Many thanks to the Excel development team at Microsoft Corporation for improving Excel with each new release of Office, while considering suggestions from Excel users. A special thanks to the global Excel community. You've shown me creative ways to use Excel over the years, and taught me how to explain technical concepts to beginning Excel users.
Finally, I want to thank you for buying this book. Please tell us what you think about it, including what you liked so we keep doing it, or what you think can be improved. After all, this is your book.
CONGRATULATIONS ON MAKING TWO EXCELLENT CHOICES! You want to learn programming for Microsoft Excel with Visual Basic for Applications (VBA), and you've purchased this book to teach you. Excel is the most powerful and widely used spreadsheet application in the world. VBA enables you to become much more productive and efficient, while getting your everyday Excel tasks done more quickly and with fewer errors. You'll gain a programming skill that is in high demand, which will improve your value in the workplace and your marketability when searching for employment.
This book covers VBA from the ground up, and assumes you have never programmed Excel before. If you've never recorded or written an Excel macro, this book shows you how. If you've worked with VBA before, this book has examples of programming techniques you might not have seen. The instruction and examples in this book teach VBA concepts that range in levels from fundamental to advanced. The techniques in this book apply just as well to the Excel business power user as to the keeper of the family budget.
VBA is the programming language for Microsoft's popular Office suite of applications, including Excel, Word, Access, PowerPoint, and Outlook. A full section of this book explains how to control each of those applications from Excel with VBA. By the time you complete this book, you will have learned how to record, write, and run your own macros. You'll learn how to make VBA run itself by programming Excel to monitor and respond to users' actions, and how to create friendly, customized interfaces that the users of your workbooks will enjoy.
The future of VBA is solid. Microsoft has confirmed time and again that VBA will be supported in versions of Excel into the foreseeable future, and the programming skills you learn in this book will serve you throughout your career. You'll be able to apply the principles you learn in this book to other tasks that can be automated in Excel and Microsoft's other Office applications. VBA is an enormous programming language, and when combined with Excel, using it is an ongoing, rewarding process of learning something new every day. With this book as your entry into the world of VBA programming, you are well on your way.
This book is for Excel users who have never programmed Excel before. You are an Excel user who has been doing a frequent task manually, and you are ready to automate the task with VBA. You might also be a job seeker, and you want to improve your chances of being hired in this difficult job market by learning a valuable skill. Whether your Excel tasks are large or small, this book is for you. You find out how to use VBA to automate your work by doing anything from recording a simple one-line macro to writing a complex program with a customized, user-friendly interface that will look nothing like Excel. This book has something for everyone, but especially for the person who wants to dive right into VBA from square one and learn to use its powerful programming tools.
This book contains 33 lessons, which are broken into five parts:
My main principle in this book is to teach you what you need to know in VBA. I tried to write this book as if you and I were sitting down in front of your computer, and I was explaining Excel and VBA's technical concepts in an informal tutorial session. The book is structured such that each lesson teaches you the theory of a topic, followed by one or more coded examples, with plenty of screenshots and notes to help you follow along. To avoid redundancy of instruction, the lessons build on each other, so the later chapters assume you've read, or are already familiar with, the material discussed in earlier lessons. I strongly recommend that you watch the videos, which you can find at www.wrox.com/go/excelvba24hour. You will get more out of them than you might imagine because they include bonus information about Excel, such as tips and tricks that will help you manage your workbooks with greater ease and efficiency.
What you need is this book and a fully installed version of Microsoft Office. If you only have Excel installed, that will suffice for lessons up to and including Lesson 28. Lessons 29 to 33 deal with controlling other Office applications from Excel. VBA ships with Excel, so you already have all the programming tools you need when you installed VBA with Office. The version of your Windows operating system is not important.
In many examples, different versions of Excel are represented, with Excel's latest version at this writing—version 2013—shown most frequently. If you are using Excel version 2003 or before, you can complete almost all the examples in this book, but it will be easier for you to follow along by using a version starting with 2007—ideally with 2010 or 2013. Almost everything discussed in this book has VBA example code to go along with it, with comments in the code (lines of text in VBA code that start with an apostrophe) that explain what the code is doing, and why. Plenty of screenshots help you see beforehand what to expect, and help you after you've tested your code to confirm you followed the steps correctly.
You need one other thing, which only you can control, and that is a quiet period of time for yourself so you can read this book and view its video Try It lessons uninterrupted. Everyone studies and retains new material differently, and we all live in a busy world. But do what you can to carve out some “you time” as you make your way through the book. You'll find a lot of useful material that will lead you to think of other situations you typically encounter in Excel that can be solved with the concepts you'll be learning.
To help you get the most from the text and keep track of what's happening, we've used a number of conventions throughout the book.
As for styles in the text:
persistence.properties.We use a monofont type with no highlighting for most code examples.
We use bold to emphasize code that's particularly important in the present context.Name it cmdExit and caption it as Exit.
As you work through the examples in this book, you may choose either to type in all the code manually or to use the source code files that accompany the book. All of the source code used in this book is available for download at www.wrox.com/go/excelvba24hour. The code snippets from the source code are accompanied by a download icon and note indicating the name of the program so you know it's available for download and can easily locate it in the download file. Once at the site, simply locate the book's title (either by using the Search box or by using one of the title lists) and click the Download Code link on the book's detail page to obtain all the source code for the book.
After you download the code, just unzip the file using WinZip or a similar tool. Alternatively, you can go to the main Wrox code download page at http://www.wrox.com/dynamic/books/download.aspx
We make every effort to ensure that there are no errors in the text or in the code. However, no one is perfect, and mistakes do occur. If you find an error in one of our books, like a spelling mistake or faulty piece of code, we would be very grateful for your feedback. By sending in errata you may save another reader hours of frustration and at the same time you will be helping us provide even higher quality information.
To find the errata page for this book, go to http://www.wrox.comwww.wrox.com/misc-pages/booklist.shtml
If you don't spot “your” error on the Book Errata page, go to www.wrox.com/contact/techsupport.shtml
p2p.wrox.comFor author and peer discussion, join the P2P forums at p2p.wrox.com
At http://p2p.wrox.com
p2p.wrox.comOnce you join, you can post new messages and respond to messages other users post. You can read messages at any time on the web. If you would like to have new messages from a particular forum e-mailed to you, click the Subscribe to this Forum icon by the forum name in the forum listing.
For more information about how to use the Wrox P2P, be sure to read the P2P FAQs for answers to questions about how the forum software works as well as many common questions specific to P2P and Wrox books. To read the FAQs, click the FAQ link on any P2P page.
Lesson 1: Introducing VBA
Lesson 2: Getting Started with Macros
Lesson 3: Introducing the Visual Basic Editor
Lesson 4: Working in the VBE
Welcome to your first lesson in Excel VBA 24-Hour Trainer! A good place to start is at the beginning, where you'll find it useful to get an understanding of where Visual Basic for Applications (VBA) comes from and what VBA is today. After you get a feel for how VBA fits into the overall Excel universe, you find out how to use VBA to manipulate Excel in ways you might never have thought possible.
VBA is a programming language created by Microsoft to automate operations in applications that support it, such as Excel. VBA is an enormously powerful tool that enables you to control Excel in countless ways that you cannot do—or would not want to do—manually.
In fact, VBA is also the language that manipulates Microsoft Office applications in Access, Word, PowerPoint, and Outlook. For the purposes here, VBA is the tool you use to develop macros and manipulate objects to control Excel and to control other Office applications from Excel.
You do not need to purchase anything more than the Office suite (or the individual application) to also own VBA. If you have Excel on your computer, you have VBA on your computer.
VBA is a present-day dialect of the BASIC (Beginner's All-purpose Symbolic Instruction Code) programming language that was developed in the 1960s. BASIC became widely used in many software applications throughout the next two decades because it was easy to learn and understand.
Over the years, BASIC has evolved and improved in response to advancing technology and increased demands by its users for greater programming flexibility. In 1985, Microsoft released a much richer version of BASIC, named QuickBASIC, which boasted the most up-to-date features found in programming languages of the day. In 1992, Microsoft released Visual Basic for Windows, designed to work within the burgeoning Windows environment.
Meanwhile, various software publishers were making their own enhancements to BASIC for their products' programming languages, resulting in a wide and confusing range of functionality and commands among software applications that were using BASIC. Microsoft recognized the need for developing a standardized programming language for its software products, and created Visual Basic for Applications.
VBA was first released by Microsoft with Excel 5 in the Office 1995 suite. Since then, VBA has become the programming language for Microsoft's other popular Office applications, as well as for external software customers of Microsoft to whom VBA has been licensed for use.
Everyone reading this book uses Excel for their own needs, such as financial budgeting, forecasting, analyzing scientific data, creating invoices, or charting the progress of their favorite football team. One thing all readers have in common is the need to automate some kind of frequently encountered task that is either too time-consuming or too cumbersome to continue doing manually. That's where VBA comes in.
The good news is that utilizing VBA does not mandate that you first become a world-class professional programmer. Many VBA commands are at your disposal, and are relatively easy to implement and customize for your everyday purposes.
Anything you can do manually you can do with VBA, but VBA enables you to do it faster and with a minimized risk of human error. Many things that Excel does not allow you to do manually, you can do with VBA. The following sections describe a handful of examples of what VBA can do for you.
If you find yourself needing to produce weekly or monthly sales and expense reports, a macro can create them in no time flat, in a style and format you (and more importantly, your boss) will be thrilled with. And if the source data changes later that day and you need to produce the updated report again, no problem—just run the macro again!
When faced with needing to perform the same task on every worksheet in your workbook, or in every workbook in a particular file folder, you can create a macro to “loop” through each object and do the deed. You find out how to repeat actions with various looping methods in Lesson 10. Figure 1.1 shows an example of worksheets that were sorted in alphabetical order by a macro that looped through each tab name, repositioning each sheet in the process.
Figure 1.1
In some situations, you want a macro to run automatically so you don't have to worry about remembering to run it yourself. For example, to automatically refresh a pivot table the moment its source data changes, you can monitor those changes with VBA, ensuring that your pivot table always displays real-time results. This is called “event” programming, which is cool stuff, and is discussed in Lessons 13 and 14.
An event can also be triggered and programmed anytime a cell or range of cells is selected. A common request I've received from Excel users is to highlight the active cell, or the row and column belonging to the active cell, automatically when a cell is selected. Figure 1.2 shows three options to easily locate your active cell as you traverse your worksheet.
Figure 1.2
You can create your own worksheet functions, known as user-defined functions, to handle custom calculations that Excel's built-in functions do not provide, or would be too complicated to use even if such native functions were available. For example, later in the book you see how to add up numbers in cells that are formatted a certain color. UDFs, as these custom functions are called, are covered in Lesson 19, “User-Defined Functions.”
When you create a workbook for others to use, there will inevitably be users who know little to nothing about Excel, but who will still need to work in that file. You can build a customized interface with user-friendly menus and informational pop-up boxes to guide your novice users throughout their activities in the workbook. You might be surprised at how un-Excel-looking an Excel workbook can be, with VBA providing a visually comfortable and interactive experience for users unfamiliar with Excel, enabling them to get their work done. Figure 1.3 shows an example of accomplishing this with UserForms, which are discussed in Lessons 21, 22, and 23.
Figure 1.3
If you create narrative reports in Word that require an embedded list of data from Excel, or if you need to import a table from Access into an Excel worksheet, VBA can automate the process. VBA is the programming language for Microsoft's other Office applications, enabling you to write macros in Excel to perform tasks in those other applications, with the users being none the wiser that they ever left Excel while the macro was running.
As you might imagine, the list of advantages to using VBA could fill the capacity of your average flash drive. The point is, you are sure to have tasks in your everyday dealings with Excel that can be accomplished more quickly and efficiently with VBA, and this book shows you how.
Although VBA is a tremendously useful and versatile tool, it is not a 100 percent perfect programming language—but then, no programming language anywhere can truthfully claim infallibility. The pros of VBA far outweigh its cons, but learning and using VBA does come with a few objective caveats that you should be aware of:
With the introductory nature of this first lesson, there's nothing specific to try with VBA. What you can do is to get a jump on the rest of the lessons in this book by making a list of some of your most frequent everyday manual Excel tasks, especially the dreaded, time-consuming ones you wish would go away. Tasks such as those will become good candidates for you to apply the VBA macros and automated solutions skills that the following lessons will teach you.