Excel® 2016 Power Programming with VBA
Published by
John Wiley & Sons, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256
www.wiley.com
Copyright © 2016 by John Wiley & Sons, Inc., Indianapolis, Indiana
Published simultaneously in Canada
ISBN: 978-1-119-06772-6
ISBN: 978-1-119-06756-6 (ebk)
ISBN: 978-1-119-06762-7 (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 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.
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: 2016930004
Trademarks: Wiley and the Wiley logo 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.
Michael Alexander is a Microsoft Certified Application Developer (MCAD) and author of several books on advanced business analysis with Microsoft Access and Microsoft Excel. He has more than 15 years of experience consulting and developing Microsoft Office solutions. Mike has been named a Microsoft MVP for his ongoing contributions to the Excel community. In his spare time, he runs a free tutorial site, www.datapigtechnologies.com, where he shares Excel and Access tips.
Dick Kusleika has been awarded as a Microsoft MVP for 12 consecutive years and has been working with Microsoft Office for more than 20. Dick develops Access- and Excel-based solutions for his clients and has conducted training seminars on Office products in the United States and Australia. Dick also writes a popular Excel-related blog at www.dailydoseofexcel.com.
Niek Otten lives in the Netherlands. He started in data processing in 1967 in an insurance company. He ran into Visicalc in 1980 and has been addicted to spreadsheets ever since. His first encounter with Excel (version 1!) was in 1985 on a Macintosh. Since 2005, Niek has been self-employed — reviewing books, writing articles, and developing Excel-related software, such as a high-performance actuarial add-in.
For most of us, the path to Excel VBA programming starts with the need to perform some task that can’t be done with the standard tools in Excel. That task is different for each of us. Maybe that task is to automatically create separate workbooks for all the rows in a dataset. Maybe that task is to automate the emailing of dozens of reports. Whatever that task is for you, you can bet that someone has started their own journey into Excel VBA with the same need.
The beautiful thing about Excel VBA is that you don’t have to be an expert to start solving problems with it. You can learn just enough to solve a particular problem, or you can go further and discover ways to handle all kinds of automation scenarios.
Whatever your goals may be, Excel 2016 Power Programming with VBA will help you harness the power of the VBA language to automate tasks, work smarter, and be more productive.
This book focuses on Visual Basic for Applications (VBA), the programming language built into Excel (and other applications that make up Microsoft Office). More specifically, it shows you how to write programs that automate various tasks in Excel. This book covers everything from recording simple macros through creating sophisticated user-oriented applications and utilities.
You can approach this book in any way that you please. You can read it from cover to cover, or you can skip around, picking up useful tidbits here and there. VBA programming is often a task-oriented endeavor. So if you’re faced with a challenging task, you might try the index first to see where the book might specifically address your problem.
This book does not cover Microsoft Visual Studio Tools for Office (VSTO), a technology that uses Visual Basic .NET and Microsoft Visual C#. VSTO can also be used to control Excel and other Microsoft Office applications.
As you may know, Excel 2016 is available for other platforms. For example, you can use Microsoft’s Excel Web App in your browser and even iPads and tablets. These versions do not support VBA. In other words, this book is for the desktop version of Excel 2016 for Windows.
This is not a book for beginning Excel users. If you have no experience with Excel, a better choice might be Excel 2016 Bible (John Walkenbach, Wiley Publishing), which provides comprehensive coverage of all the features of Excel and is meant for users of all levels.
To get the most out of this book, you should be a relatively experienced Excel user who knows how to:
It is important to have a full installation of Excel and, if you want to try the more advanced chapters involving communication between Excel and other Office applications, you will need a full installation of Office.
Although most of the material also applies to earlier versions of Excel, we assume that you are using Excel 2016. If you plan to develop applications that will be used in earlier versions of Excel, we strongly suggest that you use the earliest version of Excel that your target audience will be using.
The version of Windows you use is not important. Any computer system that can run Windows will suffice, but you’ll be much better off with a fast machine with plenty of memory. Excel is a large program, and using it on a slower system or a system with minimal memory can be extremely frustrating.
This book is not intended for any version of Excel for Mac.
Take a minute to skim this section and learn some of the typographic conventions used throughout this book.
Excel uses a context-sensitive Ribbon menu system. The words along the top (such as Insert and View) are known as tabs. Click a tab, and the Ribbon of icons displays the commands that are most suited to the task at hand. Each icon has a name that is (usually) displayed next to or below the icon. The icons are arranged in groups, and the group name appears below the icons.
The convention used in this book is to indicate the tab name, followed by the group name, followed by the icon name. So, for example, the command used to toggle word wrap in a cell is indicated as:
Clicking the first tab, labeled File, takes you to the Backstage window. The Backstage window has commands along the left side of the window. To indicate Backstage commands, we use the word File, followed by the command. For example, the following command displays the Excel Options dialog box:
Visual Basic Editor is the window in which you work with your VBA code. VB Editor uses the traditional menu-and-toolbar interface. A command like the following means to click the Tools menu and select the References menu item:
You need to use the keyboard to enter data. In addition, you can work with menus and dialog boxes directly from the keyboard — a method that you might find easier if your hands are already positioned over the keys.
Inputs that you are supposed to type from the keyboard will appear in boldface — for example, enter =SUM(B2: B50) in cell B51.
Lengthier inputs will appear on a separate line in a monospace font. For example, we might instruct you to enter the following formula:
=VLOOKUP(StockNumber,PriceList,2)
This book contains many snippets of VBA code, as well as complete procedure listings. Each listing appears in a monospace font; each line of code occupies a separate line. (I copied these listings directly from the VBA module and pasted them into my word processor.) To make the code easier to read, we often use one or more tabs to create indentations. Indentation is optional, but it does help to delineate statements that go together.
If a line of code doesn’t fit on a single line in this book, we use the standard VBA line continuation sequence: At the end of a line, a space followed by an underscore character indicates that the line of code extends to the next line. For example, the following two lines are a single code statement:
columnCount = Application.WorksheetFunction. _ CountA(Range(“A:A”)) + 1
You can enter this code either on two lines, exactly as shown, or on a single line without the space and underscore character.
Excel’s worksheet functions appear in uppercase font, like so: “Enter a SUM formula in cell C20.” For VBA procedure names, properties, methods, and objects, we often use mixed uppercase and lowercase letters to make these names easier to read.
Throughout the book, I use icons to call your attention to points that are particularly important:
The chapters of this book are grouped into five main parts.
In Part I we introduce you to VBA, providing the programming fundamentals you will need to create and manage Excel subroutines and functions. Chapter 1 sets the stage with a conceptual overview of Excel application development. Chapters 2 through 6 cover everything you need to know to start coding in VBA. Chapter 7 rounds out your introduction to VBA with many useful examples.
Part II covers additional techniques that are often considered advanced. Chapters 8 and 9 discuss how to use VBA to work with pivot tables and charts (including Sparkline graphics). Chapter 10 discusses various techniques that you can use to interact with other applications (such as Word and Outlook). Chapter 11 concludes Part II with a discussion on how to work with files and external data sources.
The four chapters in Part III cover custom dialog boxes (UserForms). Chapter 12 presents some built-in alternatives to creating custom UserForms. Chapter 13 provides an introduction to UserForms and the various controls that you can use. Chapters 14 and 15 present many examples of custom dialog boxes, ranging from basic to advanced.
The chapters in Part IV deal with important elements of creating user-oriented applications. Chapter 16 offers a hands-on discussion of creating add-ins. Chapters 17 and 18 discuss how to modify Excel’s Ribbon and shortcut menus. Chapter 19 demonstrates several ways to provide online help for your applications. In Chapter 20, we present a primer on developing user-oriented applications. Chapter 21 rounds out your exploration of Excel VBA programming with some information regarding compatibility.
Part V includes an appendix that offers a reference guide to all statements and functions exposed to VBA as keywords.
Nearly everything discussed in this book has examples with it. You can (and should) download the many useful examples included with this book.
The files are at www.wiley.com/go/excel2016powerprogramming.
Toward the back of the book, you’ll find a coupon that you can redeem for a discounted copy of John Walkenbach’s award-winning Power Utility Pak, which comprises a collection of useful Excel utilities, plus many worksheet functions.
You can also use this coupon to purchase the complete VBA source code for a nominal fee. Studying the code is an excellent way to pick up some useful programming techniques.
You can download a 30-day trial version of the most recent version of the Power Utility Pak from John’s website:
http://spreadsheetpage.com
If you find it useful, use the coupon to purchase a licensed copy at a discount.