Transcription
Microsoft Excel VBA andMacros (Office 2021 andMicrosoft 365)Bill JelenTracy Syrstad9780137521524 print.indb 101/02/22 8:43 PM
Microsoft Excel VBA and Macros (Office 2021 and Microsoft 365)Published with the authorization of Microsoft Corporation by:EDITOR-IN-CHIEFBrett BartowPearson Education, Inc.EXECUTIVE EDITORCopyright 2022 by Pearson Education, Inc.Loretta YatesAll rights reserved. This publication is protected by copyright, and permission must beobtained from the publisher prior to any prohibited reproduction, storage in a retrievalsystem, or transmission in any form or by any means, electronic, mechanical, photocopying, recording, or likewise. For information regarding permissions, request forms,and the appropriate contacts within the Pearson Education Global Rights & PermissionsDepartment, please visit www.pearson.com/permissions.No patent liability is assumed with respect to the use of the information containedherein. Although every precaution has been taken in the preparation of this book, thepublisher and author assume no responsibility for errors or omissions. Nor is any liability assumed for damages resulting from the use of the information contained herein.ISBN-13: 978-0-13-752152-4ISBN-10: 0-13-752152-9SPONSORING EDITORCharvi AroraDEVELOPMENT EDITORSonglin QiuMANAGING EDITORSandra SchroederSENIOR PROJECT EDITORTracey CroomCOPY EDITORSarah KearnsINDEXERLibrary of Congress Control Number: 2022930486Timothy WrightScoutAutomatedPrintCodePROOFREADERDonna E. MulderTrademarksMicrosoft and the trademarks listed at http://www.microsoft.com on the “Trademarks”webpage are trademarks of the Microsoft group of companies. All other marks areproperty of their respective owners. Figures 18-1, and 18-2 are 2022 Spotify AB.TECHNICAL EDITORBob UmlasCOVER DESIGNERTwist Creative, SeattleWarning and DisclaimerCOMPOSITOREvery 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. Theauthor, the publisher, and Microsoft Corporation shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from theinformation contained in this book or from the use of the programs accompanying it.codeMantraSpecial SalesFor information about buying this title in bulk quantities, or for special salesopportunities (which may include electronic versions; custom cover designs; andcontent particular to your business, training goals, marketing focus, or brandinginterests), please contact our corporate sales department at corpsales@pearsoned.comor (800) 382-3419.For government sales inquiries, please contact governmentsales@pearsoned.com.For questions about sales outside the U.S., please contact intlcs@pearson.com.9780137521524 print.indb 201/02/22 8:43 PM
Pearson’s Commitment toDiversity, Equity, and InclusionPearson is dedicated to creating bias-free content that reflects the diversity of all learners.We embrace the many dimensions of diversity, including but not limited to race, ethnicity,gender, socioeconomic status, ability, age, sexual orientation, and religious or politicalbeliefs.Education is a powerful force for equity and change in our world. It has the potential todeliver opportunities that improve lives and enable economic mobility. As we work withauthors to create content for every product and service, we acknowledge our responsibilityto demonstrate inclusivity and incorporate diverse scholarship so that everyone can achievetheir potential through learning. As the world’s leading learning company, we have a dutyto help drive change and live up to our purpose to help more people create a better life forthemselves and to create a better world.Our ambition is to purposefully contribute to a world where: Everyone has an equitable and lifelong opportunity to succeed through learning.Our educational products and services are inclusive and represent the rich diversityof learners.Our educational content accurately reflects the histories and experiences of thelearners we serve.Our educational content prompts deeper discussions with learners and motivatesthem to expand their own learning (and worldview).While we work hard to present unbiased content, we want to hear from you about anyconcerns or needs with this Pearson product so that we can investigate and address them. Please contact us with concerns about any potential bias 137521524 print.indb 301/02/22 8:43 PM
This page intentionally left blank9780137521524 print.indb 401/02/22 8:43 PM
DedicationFor Skipper Geanangel, Patricia Garick, Jim Lantz, Robert Mucci,Bill & Jean Esposito. Thanks for launching a writing career.—Bill JelenTo John. Giraffe.—Tracy Syrstad9780137521524 print.indb 501/02/22 8:43 PM
Contents at a GlanceAcknowledgmentsxxvAbout the AuthorsxxviiIntroductionxxixCHAPTER 1Unleashing the power of Excel with VBA1CHAPTER 2This sounds like BASIC, so why doesn’t it look familiar?27CHAPTER 3Referring to ranges53CHAPTER 4Looping and flow control69CHAPTER 5R1C1-style formulas87CHAPTER 6Creating and manipulating names in VBA97CHAPTER 7Event programming111CHAPTER 8Arrays125CHAPTER 9Creating classes and collections133CHAPTER 10Userforms: An introduction153CHAPTER 11Data mining with Advanced Filter175CHAPTER 12Using VBA to create pivot tables211CHAPTER 13Excel power257CHAPTER 14Sample user-defined functions285CHAPTER 15Creating charts313CHAPTER 16Data visualizations and conditional formatting339CHAPTER 17Dashboarding with sparklines in Excel363CHAPTER 18Reading from the web using M and VBA385CHAPTER 19Text file processing413CHAPTER 20Automating Word427CHAPTER 21 Using Access as a back end to enhance multiuseraccess to data447CHAPTER 22Advanced userform techniques465CHAPTER 23The Windows Application Programming Interface (API)491CHAPTER 24Handling errors501CHAPTER 25Customizing the ribbon to run macros517CHAPTER 26Creating Excel add-ins539CHAPTER 27An introduction to creating Office add-ins549CHAPTER 28What’s new in Excel 365 and what’s changed571Index579vi9780137521524 print.indb 601/02/22 8:43 PM
ContentsAcknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvAbout the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxviiIntroduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxixChapter 1Unleashing the power of Excel with VBA1Barriers to entry. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1The macro recorder doesn’t work!. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2No one person on the Excel team is focused on themacro recorder. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2Visual Basic is not like BASIC. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2Good news: Climbing the learning curve is easy. . . . . . . . . . . . . . . . . . 3Great news: Excel with VBA is worth the effort. . . . . . . . . . . . . . . . . . . . 3Knowing your tools: The Developer tab. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3Understanding which file types allow macros . . . . . . . . . . . . . . . . . . . . . . . . . . 4Macro security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6Adding a trusted location. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6Using macro settings to enable macros in workbooks outsidetrusted locations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7Using Disable All Macros With Notification. . . . . . . . . . . . . . . . . . . . . . . 8Overview of recording, storing, and running a macro. . . . . . . . . . . . . . . . . . . 8Filling out the Record Macro dialog box . . . . . . . . . . . . . . . . . . . . . . . . . 9Running a macro. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10Creating a macro button on the ribbon. . . . . . . . . . . . . . . . . . . . . . . . . 10Creating a macro button on the Quick Access Toolbar. . . . . . . . . . . . 11Assigning a macro to a form control, text box, or shape. . . . . . . . . . 12Understanding the VB Editor. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13VB Editor settings. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14The Project Explorer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14The Properties window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15vii9780137521524 print.indb 701/02/22 8:43 PM
Understanding shortcomings of the macro recorder . . . . . . . . . . . . . . . . . . 15Recording the macro . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17Examining code in the Programming window. . . . . . . . . . . . . . . . . . . 17Running the macro on another day producesundesired results. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19Possible solution: Use relative references when recording. . . . . . . . 20Never use AutoSum or Quick Analysis whilerecording a macro. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24Four tips for using the macro recorder. . . . . . . . . . . . . . . . . . . . . . . . . . 25Next steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26Chapter 2This sounds like BASIC, so why doesn’t it lookfamiliar? 27Understanding the parts of VBA “speech”. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28VBA is not really hard. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32VBA Help files: Using F1 to find anything. . . . . . . . . . . . . . . . . . . . . . . . 32Using Help topics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32Examining recorded macro code: Using the VB Editor and Help. . . . . . . . 33Optional parameters. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34Defined constants. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35Properties can return objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38Using debugging tools to figure out recorded code. . . . . . . . . . . . . . . . . . . 38Stepping through code. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38More debugging options: Breakpoints. . . . . . . . . . . . . . . . . . . . . . . . . . 40Backing up or moving forward in code. . . . . . . . . . . . . . . . . . . . . . . . . . 40Not stepping through each line of code. . . . . . . . . . . . . . . . . . . . . . . . . 41Querying anything while stepping through code. . . . . . . . . . . . . . . . 41Using a watch to set a breakpoint. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43Using a watch on an object. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44Object Browser: The ultimate reference. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45Seven tips for cleaning up recorded code. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45Tip 1: Don’t select anything. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46Tip 2: Use Cells(2,5) because it’s more convenientthan Range("E2"). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47Tip 3: Use more reliable ways to find the last row. . . . . . . . . . . . . . . . 47Tip 4: Use variables to avoid hard-coding rows and formulas. . . . . 49viiiContents9780137521524 print.indb 801/02/22 8:43 PM
Tip 5: Use R1C1 formulas that make your life easier. . . . . . . . . . . . . . . 49Tip 6: Copy and paste in a single statement . . . . . . . . . . . . . . . . . . . . . 49Tip 7: Use With.End With to perform multiple actions. . . . . . . . . 50Next steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52Chapter 3Referring to ranges53The Range object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54Syntax for specifying a range. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54Referencing named ranges. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55Shortcut for referencing ranges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55Referencing ranges in other sheets. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55Referencing a range relative to another range. . . . . . . . . . . . . . . . . . . . . . . . . 56Using the Cells property to select a range. . . . . . . . . . . . . . . . . . . . . . . . . . . . 57Using the Offset property to refer to a range. . . . . . . . . . . . . . . . . . . . . . . . . 58Using the Resize property to change the size of a range. . . . . . . . . . . . . . . 60Using the Columns and Rows properties to specify a range. . . . . . . . . . . . . . 61Using the Union method to join multiple ranges. . . . . . . . . . . . . . . . . . . . . . . 62Using the Intersect method to create a new range fromoverlapping ranges. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62Using the IsEmpty function to check whether a cell is empty. . . . . . . . . . . 62Using the CurrentRegion property to select a data range. . . . . . . . . . . . . . 63Using the Areas collection to return a noncontiguous range. . . . . . . . . . . 66Referencing tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67Next steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68Chapter 4Looping and flow control 69For.Next loops. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .69Using variables in the For statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . 72Variations on the For.Next loop. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72Exiting a loop early after a condition is met . . . . . . . . . . . . . . . . . . . . . 73Nesting one loop inside another loop. . . . . . . . . . . . . . . . . . . . . . . . . . . 74Do loops. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .75Using the While or Until clause in Do loops . . . . . . . . . . . . . . . . . . . . . 77Contents9780137521524 print.indb 9ix01/02/22 8:43 PM
The VBA loop: For Each. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79Object variables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79Flow control: Using If.Then.Else and Select Case. . . . . . . . . . . . . . 81Basic flow control: If.Then.Else. . . . . . . . . . . . . . . . . . . . . . . . . 81Using Select Case.End Select for multiple conditions . . . . . . . 83Next steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86Chapter 5R1C1-style formulas 87Toggling to R1C1-style references . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88Witnessing the miracle of Excel formulas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89Entering a formula once and copying 1,000 times . . . . . . . . . . . . . . . 89The secret: It’s not that amazing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90Understanding the R1C1 reference style. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91Using R1C1 with relative references. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91Using R1C1 with absolute references. . . . . . . . . . . . . . . . . . . . . . . . . . . . 92Using R1C1 with mixed references . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93Referring to entire columns or rows with R1C1 style. . . . . . . . . . . . . . 93Replacing many A1 formulas with a single R1C1 formula. . . . . . . . . . 94Remembering column numbers associated withcolumn letters. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95Next steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96Chapter 6Creating and manipulating names in VBA 97Global versus local names. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97Adding names. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98Deleting names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100Adding comments. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100Types of names. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101Formulas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101Strings. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101Numbers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103Using arrays in names. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104Reserved names. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104Hiding names. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106xContents9780137521524 print.indb 1001/02/22 8:43 PM
Checking for the existence of a name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106Next steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109Chapter 7Event programming 111Levels of events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111Using events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112Event parameters. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112Enabling events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113Workbook events. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113Workbook-level sheet events. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115Worksheet events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116Chart events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118Embedded charts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118Embedded chart and chart sheet events. . . . . . . . . . . . . . . . . . . . . . . 119Application-level events. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120Next steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124Chapter 8Arrays 125Declaring an array. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125Declaring a multidimensional array. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126Filling an array. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127Retrieving data from an array . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128Using arrays to speed up code. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129Using dynamic arrays. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130Passing an array. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131Next steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132Chapter 9Creating classes and collections 133Inserting a class module . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133Trapping application and embedded chart events. . . . . . . . . . . . . . . . . . . . 134Application events. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134Embedded chart events. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136Creating a custom object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137Using a custom object. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139Contents9780137521524 print.indb 11xi01/02/22 8:43 PM
Using collections. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140Creating a collection. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140Creating a collection in a standard module. . . . . . . . . . . . . . . . . . . . . 141Creating a collection in a class module. . . . . . . . . . . . . . . . . . . . . . . . . 142Using dictionaries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145Using user-defined types to create custom properties. . . . . . . . . . . . . . . . 148Next steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152Chapter 10 Userforms: An introduction 153Input boxes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153Message boxes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154Creating a userform . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155Calling and hiding a userform. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156Programming userforms. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157Userform events. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157Programming controls. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158Using basic form controls. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159Using labels, text boxes, and command buttons. . . . . . . . . . . . . . . . 159Deciding whether to use list boxes or combo boxes in forms. . . . 162Using the MultiSelect property of a list box. . . . . . . . . . . . . . . . . . . 163Adding option buttons to a userform. . . . . . . . . . . . . . . . . . . . . . . . . . 165Adding graphics to a userform. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167Using a spin button on a userform. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168Using the MultiPage control to combine forms. . . . . . . . . . . . . . . . . 169Verifying field entry . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171Illegal window closing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172Getting a file name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173Next steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174Chapter 11 Data mining with Advanced Filter 175Replacing a loop with AutoFilter. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175Using AutoFilter techniques. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178Selecting visible cells only. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181xiiContents9780137521524 print.indb 1201/02/22 8:43 PM
Advanced Filter—easier in VBA than in Excel. . . . . . . . . . . . . . . . . . . . . . . . . 183Using the Excel interface to build an advanced filter. . . . . . . . . . . . 184Using Advanced Filter to extract a unique list of values . . . . . . . . . . . . . . . 184Extracting a unique list of values with the user interface . . . . . . . . 185Extracting a unique list of values with VBA code. . . . . . . . . . . . . . . . 186Getting unique combinations of two or more fields. . . . . . . . . . . . . 190Using Advanced Filter with criteria ranges . . . . . . . . . . . . . . . . . . . . . . . . . . . 191Joining multiple criteria with a logical OR. . . . . . . . . . . . . . . . . . . . . . 192Joining two criteria with a logical AND. . . . . . . . . . . . . . . . . . . . . . . . . 193Other slightly complex criteria ranges . . . . . . . . . . . . . . . . . . . . . . . . . 193The most complex criteria: Replacing the list of valueswith a condition created as the result of a formula. . . . . . . . . . . . 193Setting up a condition using computed criteria. . . . . . . . . . . . . . . . . 194Using Filter In Place in Advanced Filter. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201Catching no records when using a filter in place. . . . . . . . . . . . . . . . 201Showing all records after running a filter in place. . . . . . . . . . . . . . . 202The real workhorse: xlFilterCopy with all records rather thanunique records only . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202Copying all columns. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202Copying a subset of columns and reordering. . . . . . . . . . . . . . . . . . . 203Excel in practice: Turning off a few drop-down menusin the AutoFilter. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209Next steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210Chapter 12 Using VBA to create pivot tables 211Understanding how pivot tables evolved over variousExcel versions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211Building a pivot table in Excel VBA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212Defining the pivot cache. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213Creating and configuring the pivot table. . . . . . . . . . . . . . . . . . . . . . . 213Adding fields to the data area. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214Learning why you cannot move or change part of apivot report. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217Determining the size of a finished pivot table to convert thepivot table to values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217Contents9780137521524 print.indb 13xiii01/02/22 8:43 PM
Using advanced pivot table features. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220Using multiple value fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220Grouping daily dates to months, quarters, or years. . . . . . . . . . . . . 221Changing the calculation to show percentages. . . . . . . . . . . . . . . . . 223Eliminating blank cells in the Values area. . . . . . . . . . . . . . . . . . . . . . . 226Controlling the sort order with AutoSort. . . . . . . . . . . . . . . . . . . . . . . 226Replicating the report for every product. . . . . . . . . . . . . . . . . . . . . . . 226Filtering a data set. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229Manually filtering two or more items in a pivot field. . . . . . . . . . . . 229Using the conceptual filters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230Using the search filter. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 234Setting up slicers to filter a pivot table. . . . . . . . . . . . . . . . . . . . . . . . . 237Setting up a timeline to filter an Excel pivot table. . . . . . . . . . . . . . . 241Formatting the intersection of values in a pivot table. . . . . . . . . . . . . . . . . 243Using the Data Model in Excel. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244Adding both tables to the Data Model. . . . . . . . . . . . . . . . . . . . . . . . . 244Creating a relationship between the two tables . . . . . . . . . . . . . . . . 245Defining the pivot cache and building the pivot table. . . . . . . . . . . 245Adding model fields to the p
vi Contents at a Glance Acknowledgments xxv About the Authors xxvii Introduction xxix CHAPTER 1 Unleashing the power of Excel with VBA 1 CHAPTER 2 This sounds like BASIC, so why doesn't it look familiar? 27 CHAPTER 3 Referring to ranges 53 CHAPTER 4 Looping and flow control 69 CHAPTER 5 R1C1-style formulas 87 CHAPTER 6 Creating and manipulating names in VBA 97
![[MS-VBAL]: VBA Language Specification - Framework](/img/22/ms-vbal.jpg)
