book

Microsoft Excel VBA and Macros: Your guide to efficient automation

Name: Microsoft Excel VBA and Macros: Your guide to efficient automation
ISBN: 9780135410318

by Tracy Syrstad, Bill Jelen

October 2025

Intermediate to advanced

704 pages

17h 57m

English

Microsoft Press

Read now

Unlock full access

Cover Page
Title Page
Copyright Page
Dedication
Contents at a Glance
Contents
Acknowledgments
About the Authors
Introduction
Is TypeScript a threat to VBA?Is Python 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 macroUnderstanding the VB EditorUnderstanding the 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, names, and tables
The Range objectReferencing 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 ranges in other sheetsWorking with defined namesReferencing tables, a.k.a. ListObjectsNext steps
Chapter 4. Laying the groundwork with variables and structures
Declaring and assigning variablesDeclaring constants for safety and clarityUsing Set to assign object variablesControlling argument passing with ByVal and ByRefManaging code variations with compiler directivesUnderstanding Subs and FunctionsUsing Enum to create custom constantsUsing With statements to streamline codeNext steps
Chapter 5. Looping and flow control
For...Next loopsDo loopsThe VBA loop: For EachFlow control: Using If...Then...Else and Select CaseNext steps
Chapter 6. R1C1 style formulas
Toggling to R1C1 style referencesWitnessing the miracle of Excel formulasUnderstanding the R1C1 reference styleNext 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 arrayWorking with data in an arrayUsing dynamic arraysPassing an arrayNext steps
Chapter 9. Creating custom objects and collections
Inserting a class moduleCreating 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 onlySorting dataNext steps
Chapter 12. Using VBA to create pivot tables
Building 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 featuresOffice Scripts for pivot tablesNext steps
Chapter 13. Excel power
File operationsCombining and separating workbooksPlacing a chart in a cell noteTracking user changesUsing a settings tableTechniques for VBA prosLeveling up: Real project issues, real solutionsNext 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 graphicNext 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 a 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 valuesUsing early and late binding at the same timeUnderstanding Word’s objectsControlling form fields in WordNext steps
Chapter 21. Using Access as a back end to enhance multiuser access to data
Understanding Access database formats and compatibilityADO versus DAOThe tools of ADOConnecting to a databaseAdding a record to a databaseRetrieving records from a databaseUpdating an existing recordDeleting records via ADOSummarizing records via ADOOther utilities via ADOConnecting to SQL Server from ExcelNext 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 handlersHandling errors by choosing to ignore themSuppressing Excel warningsEncountering errors on purposeHelping others help you (when you’re not there)Errors 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-inIntroduction to Office Scripts in Excel OnlineNext steps
Index

Content preview from Microsoft Excel VBA and Macros: Your guide to efficient automation

Chapter 12 Using VBA to create pivot tables

In this chapter, you will:

Build a pivot table
Use advanced pivot table features
Filter a data set
Use the Data Model in Excel
Use other pivot table features
Compare VBA to Office Scripts

Pivot tables are a powerful tool in Excel. The concept was first put into practice by Lotus, with its Improv product.

I love pivot tables because they help you very quickly summarize massive amounts of data. The name pivot table comes from the ability you have to drag fields in the PivotTable Field list and have them recalculate. You can use a basic pivot table to produce a concise summary in seconds. However, pivot tables come in so many varieties that they can be the tools of choice for many different uses. You ...

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 VBA and Macros (Office 2021 and Microsoft 365)

Publisher Resources

ISBN: 9780135410318

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Microsoft Excel VBA and Macros: Your guide to efficient automation

by Tracy Syrstad, Bill Jelen

Chapter 12

Using VBA to create pivot tables

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.