E x c e l ® 2 0 1 6 VBA and MACROS This book is part of Que’s exciting new Content Update Program, which provides automatic content updates for major technology improvements! 4 As Microsoft makes significant updates to Excel 2016, sections of this book will be updated or new sections will be added to match the updates to the software. 4 The updates will be delivered to you via a free Web Edition of this book, which can be accessed with any Internet connection. 4 This means your purchase is protected from immediately outdated information! For more information on Que’s Content Update program, see the inside back cover or go to www.quepublishing.com/CUP. If you have additional questions, please email our Customer Service department at [email protected]. C o n t e n t s a t a G l a n c e Introduction ...............................................................................................................1 1 Unleashing the Power of Excel with VBA .......................................................7 2 This Sounds Like BASIC, So Why Doesn’t It Look Familiar? ...................33 3 Referring to Ranges .............................................................................................59 4 Looping and Flow Control .................................................................................73 5 R1C1-Style Formulas ...........................................................................................93 6 Creating and Manipulating Names in VBA ...............................................103 7 Event Programming ...........................................................................................115 Excel® 2016 8 Arrays ......................................................................................................................131 9 Creating Classes and Collections ...................................................................139 10 Userforms: An Introduction ............................................................................157 VBA and Macros 11 Data Mining with Advanced Filter ...............................................................177 12 Using VBA to Create Pivot Tables ..................................................................211 13 Excel Power ...........................................................................................................251 14 Sample User-Defined Functions ...................................................................283 15 Creating Charts ....................................................................................................309 16 Data Visualizations and Conditional Formatting ....................................333 17 Dashboarding with Sparklines in Excel 2016 ...........................................355 18 Reading from and Writing to the Web .......................................................375 19 Text File Processing ............................................................................................391 20 Automating Word ...............................................................................................405 21 Using Access as a Back End to Enhance Multiuser Access to Data ...423 Bill Jelen 22 Advanced Userform Techniques ....................................................................439 23 The Windows Application Programming Interface (API) ....................463 Tracy Syrstad 24 Handling Errors ....................................................................................................473 25 Customizing the Ribbon to Run Macros ....................................................487 26 Creating Add-ins .................................................................................................509 27 An Introduction to Creating Office Add-ins ..............................................517 28 What’s New in Excel 2016 and What’s Changed .....................................539 Index ........................................................................................................................545 Pearson Education 800 E. 96th Street Indianapolis, Indiana 4 6240 Excel® 2016 VBA and Macros Editor-in-Chief Greg Wiegand Copyright © 2016 by Pearson Education, Inc. Acquisitions Editor All rights reserved. No part of this book shall be reproduced, stored in a Joan Murray retrieval system, or transmitted by any means, electronic, mechanical, pho- Development Editor tocopying, recording, or otherwise, without written permission from the Charlotte Kughen publisher. No patent liability is assumed with respect to the use of the infor- Managing Editor mation contained herein. Although every precaution has been taken in the Sandra Schroeder preparation of this book, the publisher and author assume no responsibility for errors or omissions. Nor is any liability assumed for damages resulting Project Editor from the use of the information contained herein. Mandie Frank ISBN-13: 978-0-7897-5585-8 Copy Editor ISBN-10: 0-7897-5585-8 Kitty Wilson Indexer Library of Congress Control Number: 2015950785 Ken Johnson Printed in the United States of America Proofreader First Printing: November 2015 Dan Knott Technical Editor Trademarks Bob Umlas All terms mentioned in this book that are known to be trademarks or service Editorial Assistant marks have been appropriately capitalized. Que Publishing cannot attest to Cindy Teeters the accuracy of this information. Use of a term in this book should not be Designer regarded as affecting the validity of any trademark or service mark. Chuti Prasertsith Warning and Disclaimer Compositor Trina Wurst Every effort has been made to make this book as complete and as accurate as possible, but no warranty or fitness is implied. The information provided is on an “as is” basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book. Special Sales For information about buying this title in bulk quantities, or for special sales opportunities (which may include electronic versions; custom cover designs; and content particular to your business, training goals, marketing focus, or branding interests), please contact our corporate sales department at [email protected] or (800) 382-3419. For government sales inquiries, please contact [email protected]. For questions about sales outside the U.S., please contact [email protected]. Contents Introduction ..........................................................................................................................................................................................................................................................1 What Is in This Book? ......................................................................................................................................................................................................................................1 Reducing the Learning Curve ............................................................................................................................................................................................................1 Excel VBA Power .......................................................................................................................................................................................................................................2 Techie Stuff Needed to Produce Applications .........................................................................................................................................................................2 Does This Book Teach Excel? ..............................................................................................................................................................................................................2 The Future of VBA and Windows Versions of Excel ........................................................................................................................................................................4 Versions of Excel .......................................................................................................................................................................................................................................4 Differences for Mac Users ....................................................................................................................................................................................................................4 Special Elements and Typographical Conventions .........................................................................................................................................................................5 Code Files ...............................................................................................................................................................................................................................................................5 Next Steps ..............................................................................................................................................................................................................................................................5 1 Unleashing the Power of Excel with VBA ....................................................................................................................................................................7 The Power of Excel ............................................................................................................................................................................................................................................7 Barriers to Entry .................................................................................................................................................................................................................................................7 The Macro Recorder Doesn’t Work! ...............................................................................................................................................................................................7 No One on the Excel Team Is Focused on the Macro Recorder .....................................................................................................................................8 Visual Basic Is Not Like BASIC............................................................................................................................................................................................................8 Good News: Climbing the Learning Curve Is Easy ................................................................................................................................................................9 Great News: Excel with VBA Is Worth the Effort ....................................................................................................................................................................9 Knowing Your Tools: The Developer Tab ...............................................................................................................................................................................................9 Understanding Which File Types Allow Macros ............................................................................................................................................................................10 Macro Security .................................................................................................................................................................................................................................................12 Adding a Trusted Location ...............................................................................................................................................................................................................12 Using Macro Settings to Enable Macros in Workbooks Outside Trusted Locations .......................................................................................13 Using Disable All Macros with Notification............................................................................................................................................................................14 Overview of Recording, Storing, and Running a Macro ...........................................................................................................................................................14 Filling Out the Record Macro Dialog ..........................................................................................................................................................................................15 Running a Macro ............................................................................................................................................................................................................................................16 Creating a Macro Button on the Ribbon ..................................................................................................................................................................................16 Creating a Macro Button on the Quick Access Toolbar ...................................................................................................................................................17 Assigning a Macro to a Form Control, Text Box, or Shape ...........................................................................................................................................18 Understanding the VB Editor...................................................................................................................................................................................................................19 VB Editor Settings .................................................................................................................................................................................................................................20 The Project Explorer .............................................................................................................................................................................................................................20 The Properties Window .....................................................................................................................................................................................................................21 Understanding Shortcomings of the Macro Recorder ..............................................................................................................................................................21 Recording the Macro ...........................................................................................................................................................................................................................23 Examining Code in the Programming Window ..................................................................................................................................................................23 Running the Macro on Another Day Produces Undesired Results ...........................................................................................................................25 Possible Solution: Use Relative References When Recording .....................................................................................................................................26 Never Use AutoSum or Quick Analysis While Recording a Macro ............................................................................................................................30 iv Excel 2016 VBAs and Macros Four Tips for Using the Macro Recorder...................................................................................................................................................................................31 Next Steps ..................................................................................................................................................................................................................................................32 2 This Sounds Like BASIC, So Why Doesn’t It Look Familiar?....................................................................................................................33 I Can’t Understand This Code ...................................................................................................................................................................................................................33 Understanding the Parts of VBA “Speech” ......................................................................................................................................................................................34 VBA Is Not Really Hard ................................................................................................................................................................................................................................37 VBA Help Files: Using F1 to Find Anything ............................................................................................................................................................................38 Using Help Topics ..................................................................................................................................................................................................................................38 Examining Recorded Macro Code: Using the VB Editor and Help ......................................................................................................................................39 Optional Parameters ...........................................................................................................................................................................................................................39 Defined Constants .................................................................................................................................................................................................................................40 Properties Can Return Objects .......................................................................................................................................................................................................43 Using Debugging Tools to Figure Out Recorded Code ..............................................................................................................................................................43 Stepping Through Code .....................................................................................................................................................................................................................43 More Debugging Options: Breakpoints ....................................................................................................................................................................................45 Backing Up or Moving Forward in Code ..................................................................................................................................................................................45 Not Stepping Through Each Line of Code ...............................................................................................................................................................................46 Querying Anything While Stepping Through Code ..........................................................................................................................................................46 Using a Watch to Set a Breakpoint .............................................................................................................................................................................................49 Using a Watch on an Object ............................................................................................................................................................................................................49 Object Browser: The Ultimate Reference ..........................................................................................................................................................................................50 Seven Tips for Cleaning Up Recorded Code.....................................................................................................................................................................................51 Tip 1: Don’t Select Anything ...........................................................................................................................................................................................................51 Tip 2: Use Cells(2,5) Because It’s More Convenient Than Range("E2") ..............................................................................52 Tip 3: Use More Reliable Ways to Find the Last Row .......................................................................................................................................................52 Tip 4: Use Variables to Avoid Hard-Coding Rows and Formulas...............................................................................................................................53 Tip 5: Use R1C1 Formulas That Make Your Life Easier ....................................................................................................................................................54 Tip 6: Copy and Paste in a Single Statement ........................................................................................................................................................................54 Tip 7: Use With...End With to Perform Multiple Actions .......................................................................................................................54 Next Steps ...........................................................................................................................................................................................................................................................57 3 Referring to Ranges..........................................................................................................................................................................................................................59 The Range Object ......................................................................................................................................................................................................................................59 Syntax for Specifying a Range ................................................................................................................................................................................................................60 Named Ranges .................................................................................................................................................................................................................................................60 Shortcut for Referencing Ranges ..........................................................................................................................................................................................................60 Referencing Ranges in Other Sheets ..................................................................................................................................................................................................61 Referencing a Range Relative to Another Range ........................................................................................................................................................................61 Using the Cells Property to Select a Range ...........................................................................................................................................................................62 Using the Offset Property to Refer to a Range ...................................................................................................................................................................63 Using the Resize Property to Change the Size of a Range ...........................................................................................................................................65 Using the Columns and Rows Properties to Specify a Range ..................................................................................................................................66 Using the Union Method to Join Multiple Ranges ...............................................................................................................................................................66 Contents v Using the Intersect Method to Create a New Range from Overlapping Ranges ......................................................................................67 Using the IsEmpty Function to Check Whether a Cell Is Empty ................................................................................................................................67 Using the CurrentRegion Property to Select a Data Range ...............................................................................................................................68 Using the Areas Collection to Return a Noncontiguous Range ...................................................................................................................................70 Referencing Tables .........................................................................................................................................................................................................................................71 Next Steps ...........................................................................................................................................................................................................................................................72 4 Looping and Flow Control ..........................................................................................................................................................................................................73 For...Next Loops .............................................................................................................................................................................................................................73 Using Variables in the For Statement ..................................................................................................................................................................................75 Variations on the For...Next Loop ..............................................................................................................................................................................76 Exiting a Loop Early After a Condition Is Met .......................................................................................................................................................................77 Nesting One Loop Inside Another Loop ...................................................................................................................................................................................78 Do Loops ............................................................................................................................................................................................................................................................78 Using the While or Until Clause in Do Loops .......................................................................................................................................................81 The VBA Loop: For Each .................................................................................................................................................................................................................82 Object Variables......................................................................................................................................................................................................................................83 Flow Control: Using If...Then...Else and Select Case ...............................................................................................................86 Basic Flow Control: If...Then...Else ..............................................................................................................................................................86 Using Select Case...End Select for Multiple Conditions .......................................................................................................88 Next Steps ...........................................................................................................................................................................................................................................................91 5 R1C1-Style Formulas ........................................................................................................................................................................................................................93 Referring to Cells: A1 Versus R1C1 References ..............................................................................................................................................................................93 Toggling to R1C1-Style References ......................................................................................................................................................................................................94 Witnessing the Miracle of Excel Formulas .......................................................................................................................................................................................95 Entering a Formula Once and Copying 1,000 Times ........................................................................................................................................................95 The Secret: It’s Not That Amazing ...............................................................................................................................................................................................96 Understanding the R1C1 Reference Style........................................................................................................................................................................................97 Using R1C1 with Relative References........................................................................................................................................................................................97 Using R1C1 with Absolute References ......................................................................................................................................................................................98 Using R1C1 with Mixed References ............................................................................................................................................................................................98 Referring to Entire Columns or Rows with R1C1 Style....................................................................................................................................................99 Replacing Many A1 Formulas with a Single R1C1 Formula .........................................................................................................................................99 Remembering Column Numbers Associated with Column Letters ......................................................................................................................101 Using R1C1 Formulas with Array Formulas .................................................................................................................................................................................101 Next Steps ........................................................................................................................................................................................................................................................102 6 Creating and Manipulating Names in VBA ..........................................................................................................................................................103 Global Versus Local Names ....................................................................................................................................................................................................................103 Adding Names ...............................................................................................................................................................................................................................................104 Deleting Names ............................................................................................................................................................................................................................................105 Adding Comments ......................................................................................................................................................................................................................................106 Types of Names .............................................................................................................................................................................................................................................106 Formulas ..................................................................................................................................................................................................................................................106 vi Excel 2016 VBAs and Macros Strings .......................................................................................................................................................................................................................................................107 Numbers ..................................................................................................................................................................................................................................................108 Tables.........................................................................................................................................................................................................................................................109 Using Arrays in Names ....................................................................................................................................................................................................................109 Reserved Names ..................................................................................................................................................................................................................................110 Hiding Names ................................................................................................................................................................................................................................................111 Checking for the Existence of a Name ............................................................................................................................................................................................111 Next Steps ........................................................................................................................................................................................................................................................114 7 Event Programming .....................................................................................................................................................................................................................115 Levels of Events ............................................................................................................................................................................................................................................115 Using Events ...................................................................................................................................................................................................................................................116 Event Parameters ...............................................................................................................................................................................................................................116 Enabling Events ...................................................................................................................................................................................................................................117 Workbook Events .........................................................................................................................................................................................................................................117 Workbook-Level Sheet and Chart Events ............................................................................................................................................................................119 Worksheet Events ........................................................................................................................................................................................................................................120 Chart Events ....................................................................................................................................................................................................................................................123 Embedded Charts ...............................................................................................................................................................................................................................123 Embedded Chart and Chart Sheet Events ............................................................................................................................................................................124 Application-Level Events.........................................................................................................................................................................................................................125 Next Steps ........................................................................................................................................................................................................................................................130 8 Arrays ............................................................................................................................................................................................................................................................131 Declaring an Array ......................................................................................................................................................................................................................................131 Declaring a Multidimensional Array ................................................................................................................................................................................................132 Filling an Array ..............................................................................................................................................................................................................................................133 Retrieving Data from an Array.............................................................................................................................................................................................................134 Using Arrays to Speed Up Code ...........................................................................................................................................................................................................135 Using Dynamic Arrays ...............................................................................................................................................................................................................................136 Passing an Array ...........................................................................................................................................................................................................................................137 Next Steps ........................................................................................................................................................................................................................................................138 9 Creating Classes and Collections.....................................................................................................................................................................................139 Inserting a Class Module .........................................................................................................................................................................................................................139 Trapping Application and Embedded Chart Events ................................................................................................................................................................140 Application Events .............................................................................................................................................................................................................................140 Embedded Chart Events .................................................................................................................................................................................................................141 Creating a Custom Object .......................................................................................................................................................................................................................143 Using a Custom Object .............................................................................................................................................................................................................................145 Using Collections .........................................................................................................................................................................................................................................145 Creating a Collection ........................................................................................................................................................................................................................146 Creating a Collection in a Standard Module.......................................................................................................................................................................146 Creating a Collection in a Class Module ................................................................................................................................................................................148 Contents vii Using Dictionaries .......................................................................................................................................................................................................................................150 Using User-Defined Types to Create Custom Properties .......................................................................................................................................................153 Next Steps ........................................................................................................................................................................................................................................................156 10 Userforms: An Introduction .................................................................................................................................................................................................157 Input Boxes .....................................................................................................................................................................................................................................................157 Message Boxes ..............................................................................................................................................................................................................................................158 Creating a Userform ...................................................................................................................................................................................................................................158 Calling and Hiding a Userform ............................................................................................................................................................................................................159 Programming Userforms ........................................................................................................................................................................................................................160 Userform Events ..................................................................................................................................................................................................................................160 Programming Controls .............................................................................................................................................................................................................................162 Using Basic Form Controls ......................................................................................................................................................................................................................163 Using Labels, Text Boxes, and Command Buttons .........................................................................................................................................................163 Deciding Whether to Use List Boxes or Combo Boxes in Forms .............................................................................................................................165 Adding Option Buttons to a Userform ...................................................................................................................................................................................167 Adding Graphics to a Userform ..................................................................................................................................................................................................169 Using a Spin Button on a Userform .........................................................................................................................................................................................170 Using the MultiPage Control to Combine Forms ................................................................................................................................................171 Verifying Field Entry ..................................................................................................................................................................................................................................174 Illegal Window Closing ............................................................................................................................................................................................................................174 Getting a Filename .....................................................................................................................................................................................................................................175 Next Steps ........................................................................................................................................................................................................................................................176 11 Data Mining with Advanced Filter................................................................................................................................................................................177 Replacing a Loop with AutoFilter ......................................................................................................................................................................................................177 Using AutoFilter Techniques ........................................................................................................................................................................................................180 Selecting Visible Cells Only ...........................................................................................................................................................................................................183 Advanced Filter—Easier in VBA Than in Excel ..........................................................................................................................................................................184 Using the Excel Interface to Build an Advanced Filter .................................................................................................................................................185 Using Advanced Filter to Extract a Unique List of Values ....................................................................................................................................................186 Extracting a Unique List of Values with the User Interface ......................................................................................................................................186 Extracting a Unique List of Values with VBA Code .........................................................................................................................................................187 Getting Unique Combinations of Two or More Fields ..................................................................................................................................................191 Using Advanced Filter with Criteria Ranges ................................................................................................................................................................................192 Joining Multiple Criteria with a Logical OR ........................................................................................................................................................................193 Joining Two Criteria with a Logical AND ..............................................................................................................................................................................194 Other Slightly Complex Criteria Ranges ................................................................................................................................................................................194 The Most Complex Criteria: Replacing the List of Values with a Condition Created as the Result of a Formula ......................194 Using Filter in Place in Advanced Filter ..........................................................................................................................................................................................201 Catching No Records When Using a Filter in Place ........................................................................................................................................................202 Showing All Records After Running a Filter in Place ....................................................................................................................................................202 The Real Workhorse: xlFilterCopy with All Records Rather Than Unique Records Only ................................................................203 Copying All Columns .........................................................................................................................................................................................................................203 viii Excel 2016 VBAs and Macros Copying a Subset of Columns and Reordering .................................................................................................................................................................204 Excel in Practice: Turning Off a Few Drop-downs in the AutoFilter .....................................................................................................................209 Next Steps ........................................................................................................................................................................................................................................................210 12 Using VBA to Create Pivot Tables ..................................................................................................................................................................................211 Understanding How Pivot Tables Evolved Over Various Excel Versions .......................................................................................................................211 While Building a Pivot Table in Excel VBA ....................................................................................................................................................................................212 Defining the Pivot Cache ...............................................................................................................................................................................................................212 Creating and Configuring the Pivot Table ...........................................................................................................................................................................213 Adding Fields to the Data Area ..................................................................................................................................................................................................214 Learning Why You Cannot Move or Change Part of a Pivot Report.....................................................................................................................216 Determining the Size of a Finished Pivot Table to Convert the Pivot Table to Values .............................................................................217 Using Advanced Pivot Table Features ..............................................................................................................................................................................................219 Using Multiple Value Fields ..........................................................................................................................................................................................................220 Grouping Daily Dates to Months, Quarters, or Years ....................................................................................................................................................221 Changing the Calculation to Show Percentages .............................................................................................................................................................222 Eliminating Blank Cells in the Values Area .........................................................................................................................................................................225 Controlling the Sort Order with AutoSort ............................................................................................................................................................................225 Replicating the Report for Every Product ............................................................................................................................................................................225 Filtering a Data Set .....................................................................................................................................................................................................................................228 Manually Filtering Two or More Items in a Pivot Field ...............................................................................................................................................228 Using the Conceptual Filters ........................................................................................................................................................................................................229 Using the Search Filter ....................................................................................................................................................................................................................233 Setting Up Slicers to Filter a Pivot Table ..............................................................................................................................................................................235 Setting Up a Timeline to Filter an Excel 2016 Pivot Table .........................................................................................................................................239 Using the Data Model in Excel 2016 ................................................................................................................................................................................................242 Adding Both Tables to the Data Model .................................................................................................................................................................................242 Creating a Relationship Between the Two Tables ..........................................................................................................................................................243 Defining the PivotCache and Building the Pivot Table................................................................................................................................................243 Adding Model Fields to the Pivot Table ................................................................................................................................................................................244 Adding Numeric Fields to the Values Area .........................................................................................................................................................................244 Putting It All Together.....................................................................................................................................................................................................................245 Using Other Pivot Table Features .......................................................................................................................................................................................................247 Calculated Data Fields .....................................................................................................................................................................................................................247 Calculated Items .................................................................................................................................................................................................................................247 Using ShowDetail to Filter a Record Set .................................................................................................................................................................248 Changing the Layout from the Design Tab .........................................................................................................................................................................248 Settings for the Report Layout ...................................................................................................................................................................................................248 Suppressing Subtotals for Multiple Row Fields................................................................................................................................................................249 Next Steps ........................................................................................................................................................................................................................................................250 13 Excel Power ............................................................................................................................................................................................................................................251 File Operations ..............................................................................................................................................................................................................................................251 Listing Files in a Directory .............................................................................................................................................................................................................251 Importing and Deleting a CSV File ...........................................................................................................................................................................................254 Reading a Text File into Memory and Parsing ..................................................................................................................................................................254