Microsoft Excel 2019 VBA and Macros

Title Page
Copyright Page
Dedication Page
Contents at a Glance
Contents
Acknowledgments
About the Authors
Introduction
Chapter 1 Unleashing the power of Excel with VBA
	Barriers to entry
	Knowing your tools: The Developer tab
	Understanding which file types allow macros
	Macro security
	Overview of recording, storing, and running a macro
	Running a macro
	Understanding the VB Editor
	Understanding shortcomings of the macro recorder
	Next steps
Chapter 2 This sounds like BASIC, so why doesn’t it look familiar?
	Understanding the parts of VBA “speech”
	VBA is not really hard
	Examining recorded macro code: Using the VB Editor and Help
	Using debugging tools to figure out recorded code
	Object Browser: The ultimate reference
	Seven tips for cleaning up recorded code
	Next steps
Chapter 3 Referring to ranges
	The Range object
	Referencing ranges in other sheets
	Referencing a range relative to another range
	Using the Cells property to select a range
	Using the Offset property to refer to a range
	Using the Resize property to change the size of a range
	Using the Columns and Rows properties to specify a range
	Using the Union method to join multiple ranges
	Using the Intersect method to create a new range from overlapping ranges
	Using the IsEmpty function to check whether a cell is empty
	Using the CurrentRegion property to select a data range
	Using the Areas collection to return a noncontiguous range
	Referencing tables
	Next steps
Chapter 4 Looping and flow control
	For...Next loops
	Do loops
	The VBA loop: For Each
	Flow control: Using If...Then...Else and Select Case
	Next steps
Chapter 5 R1C1-style formulas
	Toggling to R1C1-style references
	Witnessing the miracle of Excel formulas
	Understanding the R1C1 reference style
	Using R1C1 formulas with array formulas
	Next steps
Chapter 6 Creating and manipulating names in VBA
	Global versus local names
	Adding names
	Deleting names
	Adding comments
	Types of names
	Hiding names
	Checking for the existence of a name
	Next steps
Chapter 7 Event programming
	Levels of events
	Using events
	Workbook events
	Worksheet events
	Chart events
	Application-level events
	Next steps
Chapter 8 Arrays
	Declaring an array
	Declaring a multidimensional array
	Filling an array
	Retrieving data from an array
	Using arrays to speed up code
	Using dynamic arrays
	Passing an array
	Next steps
Chapter 9 Creating classes and collections
	Inserting a class module
	Trapping application and embedded chart events
	Creating a custom object
	Using a custom object
	Using collections
	Using dictionaries
	Using user-defined types to create custom properties
	Next steps
Chapter 10 Userforms: An introduction
	Input boxes
	Message boxes
	Creating a userform
	Calling and hiding a userform
	Programming userforms
	Programming controls
	Using basic form controls
	Verifying field entry
	Illegal window closing
	Getting a file name
	Next steps
Chapter 11 Data mining with Advanced Filter
	Replacing a loop with AutoFilter
	Advanced Filter—easier in VBA than in Excel
	Using Advanced Filter to extract a unique list of values
	Using Advanced Filter with criteria ranges
	Using filter in place in Advanced Filter
	The real workhorse: xlFilterCopy with all records rather than unique records only
	Next steps
Chapter 12 Using VBA to create pivot tables
	Understanding how pivot tables evolved over various Excel versions
	While building a pivot table in Excel VBA
	Using advanced pivot table features
	Filtering a data set
	Using the Data Model in Excel 2019
	Using other pivot table features
	Next steps
Chapter 13 Excel power
	File operations
	Combining and separating workbooks
	Working with cell comments
	Tracking user changes
	Techniques for VBA pros
	Next steps
Chapter 14 Sample user-defined functions
	Creating user-defined functions
	Sharing UDFs
	Useful custom Excel functions
	Next steps
Chapter 15 Creating charts
	Using .AddChart2 to create a chart
	Understanding chart styles
	Formatting a chart
	Creating a combo chart
	Creating map charts
	Creating waterfall charts
	Exporting a chart as a graphic
	Considering backward compatibility
	Next steps
Chapter 16 Data visualizations and conditional formatting
	VBA methods and properties for data visualizations
	Adding data bars to a range
	Adding color scales to a range
	Adding icon sets to a range
	Using visualization tricks
	Using other conditional formatting methods
	Next steps
Chapter 17 Dashboarding with sparklines in Excel 2019
	Creating sparklines
	Scaling sparklines
	Formatting sparklines
	Creating a dashboard
	Next steps
Chapter 18 Reading from and writing to the web
	Getting data from the web
	Using Application.OnTime to periodically analyze data
	Publishing data to a web page
	Next steps
Chapter 19 Text file processing
	Importing from text files
	Writing Text Files
	Next steps
Chapter 20 Automating Word
	Using early binding to reference a Word object
	Using late binding to reference a Word object
	Using the New keyword to reference the Word application
	Using the CreateObject function to create a new instance of an object
	Using the GetObject function to reference an existing instance of Word
	Using constant values
	Understanding Word’s objects
	Controlling form fields in Word
	Next steps
Chapter 21 Using Access as a back end to enhance multiuser access to data
	ADO versus DAOs
	The tools of ADO
	Adding a record to a database
	Retrieving records from a database
	Updating an existing record
	Deleting records via ADO
	Summarizing records via ADO
	Other utilities via ADO
	SQL Server examples
	Next steps
Chapter 22 Advanced userform techniques
	Using the UserForm toolbar in the design of controls on userforms
	More userform controls
	Controls and collections
	Modeless userforms
	Using hyperlinks in userforms
	Adding controls at runtime
	Adding help to a userform
	Creating transparent forms
	Next steps
Chapter 23 The Windows Application Programming Interface (API)
	Understanding an API declaration
	Using an API declaration
	Making 32-bit- and 64-bit-compatible API declarations
	API function examples
	Next steps
Chapter 24 Handling errors
	What happens when an error occurs?
	Basic error handling with the On Error GoTo syntax
	Generic error handlers
	Training your clients
	Errors that won’t show up in debug mode
	Errors while developing versus errors months later
	The ills of protecting code
	More problems with passwords
	Errors caused by different versions
	Next steps
Chapter 25 Customizing the ribbon to run macros
	Where to add code: The customui folder and file
	Creating a tab and a group
	Adding a control to a ribbon
	Accessing the file structure
	Understanding the RELS file
	Renaming an Excel file and opening a workbook
	Using images on buttons
	Troubleshooting error messages
	Other ways to run a macro
	Next steps
Chapter 26 Creating add-ins
	Characteristics of standard add-ins
	Converting an Excel workbook to an add-in
	Having a client install an add-in
	Standard add-ins are not secure
	Closing add-ins
	Removing add-ins
	Using a hidden workbook as an alternative to an add-in
	Next steps
Chapter 27 An introduction to creating Office add-ins
	Creating your first Office add-in—Hello World
	Adding interactivity to an Office add-in
	A basic introduction to HTML
	Using XML to define an Office add-in
	Using JavaScript to add interactivity to an Office add-in
	Next steps
Chapter 28 What’s new in Excel 2019 and what’s changed
	Office 365 subscription versus Excel 2019 perpetual
	If it has changed in the front end, it has changed in VBA
	Learning the new objects and methods
	Compatibility mode
	Next steps
Index
Code Snippets