book

Microsoft Excel VBA and Macros (Office 2021 and Microsoft 365)

Name: Microsoft Excel VBA and Macros (Office 2021 and Microsoft 365)
ISBN: 9780137521531

by Bill Jelen, Tracy Syrstad

April 2022

Intermediate to advanced

640 pages

19h 53m

English

Microsoft Press

Read now

Unlock full access

Cover Page
Title Page
Copyright Page
Humans are born to learn
Dedication
Contents at a Glance
Contents
Acknowledgments
About the Authors
Introduction
In this Introduction, you will:Is TypeScript a threat to VBA?What is in this book?Versions of ExcelSpecial elements and typographical conventionsAbout the companion contentErrata, updates, and book supportStay in touch

Chapter 1. Unleashing the power of Excel with VBA
Barriers to entryKnowing your tools: The Developer tabUnderstanding which file types allow macrosMacro securityOverview of recording, storing, and running a macroRunning a macroUnderstanding the VB EditorUnderstanding shortcomings of the macro recorderNext steps
Chapter 2. This sounds like BASIC, so why doesn’t it look familiar?
Understanding the parts of VBA “speech”VBA is not really hardExamining recorded macro code: Using the VB Editor and HelpUsing debugging tools to figure out recorded codeObject Browser: The ultimate referenceSeven tips for cleaning up recorded codeNext steps
Chapter 3. Referring to ranges
The Range objectReferencing ranges in other sheetsReferencing a range relative to another rangeUsing the Cells property to select a rangeUsing the Offset property to refer to a rangeUsing the Resize property to change the size of a rangeUsing the Columns and Rows properties to specify a rangeUsing the Union method to join multiple rangesUsing the Intersect method to create a new range from overlapping rangesUsing the IsEmpty function to check whether a cell is emptyUsing the CurrentRegion property to select a data rangeUsing the Areas collection to return a noncontiguous rangeReferencing tablesNext steps
Chapter 4. Looping and flow control
For...Next loopsDo loopsThe VBA loop: For EachFlow control: Using If...Then...Else and Select CaseNext steps
Chapter 5. R1C1-style formulas
Toggling to R1C1-style referencesWitnessing the miracle of Excel formulasUnderstanding the R1C1 reference styleNext steps
Chapter 6. Creating and manipulating names in VBA
Global versus local namesAdding namesDeleting namesAdding commentsTypes of namesHiding namesChecking for the existence of a nameNext steps
Chapter 7. Event programming
Levels of eventsUsing eventsWorkbook eventsWorksheet eventsChart eventsApplication-level eventsNext steps
Chapter 8. Arrays
Declaring an arrayDeclaring a multidimensional arrayFilling an arrayRetrieving data from an arrayUsing arrays to speed up codeUsing dynamic arraysPassing an arrayNext steps
Chapter 9. Creating classes and collections
Inserting a class moduleTrapping application and embedded chart eventsCreating a custom objectUsing a custom objectUsing collectionsUsing dictionariesUsing user-defined types to create custom propertiesNext steps
Chapter 10. Userforms: An introduction
Input boxesMessage boxesCreating a userformCalling and hiding a userformProgramming userformsProgramming controlsUsing basic form controlsVerifying field entryIllegal window closingGetting a file nameNext steps
Chapter 11. Data mining with Advanced Filter
Replacing a loop with AutoFilterAdvanced Filter—easier in VBA than in ExcelUsing Advanced Filter to extract a unique list of valuesUsing Advanced Filter with criteria rangesUsing Filter In Place in Advanced FilterThe real workhorse: xlFilterCopy with all records rather than unique records onlyNext steps
Chapter 12. Using VBA to create pivot tables
Understanding how pivot tables evolved over various Excel versionsBuilding a pivot table in Excel VBAUsing advanced pivot table featuresFiltering a data setFormatting the intersection of values in a pivot tableUsing the Data Model in ExcelUsing other pivot table featuresComparing VBA to TypeScriptNext steps
Chapter 13. Excel power
File operationsCombining and separating workbooksPlacing a chart in a cell noteTracking user changesTechniques for VBA prosNext steps
Chapter 14. Sample user-defined functions
Creating user-defined functionsSharing UDFsUseful custom Excel functionsCreating LAMBDA functionsNext steps
Chapter 15. Creating charts
Using .AddChart2 to create a chartUnderstanding chart stylesFormatting a chartCreating a combo chartCreating map chartsCreating waterfall chartsExporting a chart as a graphicConsidering backward compatibilityNext steps
Chapter 16. Data visualizations and conditional formatting
VBA methods and properties for data visualizationsAdding data bars to a rangeAdding color scales to a rangeAdding icon sets to a rangeUsing visualization tricksUsing other conditional formatting methodsNext steps
Chapter 17. Dashboarding with sparklines in Excel
Creating sparklinesScaling sparklinesFormatting sparklinesCreating a dashboardNext steps
Chapter 18. Reading from the web using M and VBA
Get credentials for accessing an APIBuild a query in Power Query using the M language to retrieve data from the web for one specific valueGeneralizing the queries using VBAUsing global variables and loops in MApplication.OnTime to periodically analyze dataNext steps
Chapter 19. Text file processing
Importing from text filesWriting text filesNext steps
Chapter 20. Automating Word
Using early binding to reference a Word objectUsing late binding to reference a Word objectUsing the New keyword to reference the Word applicationUsing the CreateObject function to create a new instance of an objectUsing the GetObject function to reference an existing instance of WordUsing constant valuesUnderstanding Word’s objectsControlling form fields in WordNext steps
Chapter 21. Using Access as a back end to enhance multiuser access to data
ADO versus DAOThe tools of ADOAdding a record to a databaseRetrieving records from a databaseUpdating an existing recordDeleting records via ADOSummarizing records via ADOOther utilities via ADOSQL Server examplesNext steps
Chapter 22. Advanced userform techniques
Using the UserForm toolbar in the design of controls on userformsMore userform controlsControls and collectionsModeless userformsUsing hyperlinks in userformsAdding controls at runtimeAdding help to a userformCreating transparent formsNext steps
Chapter 23. The Windows Application Programming Interface (API)
Understanding an API declarationUsing an API declarationMaking 32-bit- and 64-bit-compatible API declarationsAPI function examplesNext steps
Chapter 24. Handling errors
What happens when an error occurs?Basic error handling with the On Error GoTo syntaxGeneric error handlersTraining your clientsErrors that won’t show up in Debug modeErrors while developing versus errors months laterThe ills of protecting codeMore problems with passwordsErrors caused by different versionsNext steps
Chapter 25. Customizing the ribbon to run macros
Where to add code: The customui folder and fileCreating a tab and a groupAdding a control to a ribbonAccessing the file structureUnderstanding the RELS fileRenaming an Excel file and opening a workbookUsing images on buttonsTroubleshooting error messagesOther ways to run a macroNext steps
Chapter 26. Creating Excel add-ins
Characteristics of standard add-insConverting an Excel workbook to an add-inHaving a client install an add-inAdd-in securityClosing add-insRemoving add-insUsing a hidden workbook as an alternative to an add-inNext steps
Chapter 27. An introduction to creating Office add-ins
Creating your first Office add-in—Hello WorldAdding interactivity to an Office add-inA basic introduction to HTMLUsing XML to define an Office add-inUsing JavaScript to add interactivity to an Office add-inNext steps
Chapter 28. What’s new in Excel 365 and what’s changed
Office 365 subscription versus Excel 2021 perpetualIf it has changed in the front end, it has changed in VBALearning the new objects and methodsCompatibility modeNext steps
Index
Code Snippets

Content preview from Microsoft Excel VBA and Macros (Office 2021 and Microsoft 365)

Chapter 11 Data mining with Advanced Filter

In this chapter, you will:

Replace a loop by using AutoFilter
Get to know Advanced Filter
Use Advanced Filter to extract a unique list of values
Use Advanced Filter with criteria ranges
Use Filter In Place in Advanced Filter
Use Advanced Filter to return all records that match the criteria

Read this chapter.

Although very few people use Advanced Filter in Excel, it is a workhorse in Excel VBA. I estimate that I end up using one of these filtering techniques as the core of a macro in 80% of the macros I develop for clients. Given that Advanced Filter is used in fewer than 1% of Excel sessions, this is a dramatic statistic.

So even if you hardly ever use Advanced Filter in regular Excel, you should ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Microsoft Excel Step by Step (Office 2021 and Microsoft 365)

Publisher Resources

ISBN: 9780137521531

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Microsoft Excel VBA and Macros (Office 2021 and Microsoft 365)

by Bill Jelen, Tracy Syrstad

Chapter 11

Data mining with Advanced Filter

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.