Contents

Introduction

Chapter 1   Unleashing the power of Excel with VBA

Barriers to entry

The macro recorder doesn’t work!

No one person on the Excel team is focused on the macro recorder

Visual Basic is not like BASIC

Good news: Climbing the learning curve is easy

Great news: Excel with VBA is worth the effort

Knowing your tools: The Developer tab

Understanding which file types allow macros

Macro security

Adding a trusted location

Using macro settings to enable macros in workbooks outside trusted locations

Using Disable All Macros With Notification

Overview of recording, storing, and running a macro

Filling out the Record Macro dialog box

Running a macro

Creating a macro button on the ribbon

Creating a macro button on the Quick Access Toolbar

Assigning a macro to a form control, text box, or shape

Understanding the VB Editor

VB Editor settings

The Project Explorer

The Properties window

Understanding shortcomings of the macro recorder

Recording the macro

Examining code in the Programming window

Running the macro on another day produces undesired results

Possible solution: Use relative references when recording

Never use AutoSum or Quick Analysis while recording a macro

Four tips for using 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

VBA Help files: Using F1 to find anything

Using Help topics

Examining recorded macro code: Using the VB Editor and Help

Optional parameters

Defined constants

Properties can return objects

Using debugging tools to figure out recorded code

Stepping through code

More debugging options: Breakpoints

Backing up or moving forward in code

Not stepping through each line of code

Querying anything while stepping through code

Using a watch to set a breakpoint

Using a watch on an object

Object Browser: The ultimate reference

Seven tips for cleaning up recorded code

Tip 1: Don’t select anything

Tip 2: Use Cells(2,5) because it’s more convenient than Range("E2")

Tip 3: Use more reliable ways to find the last row

Tip 4: Use variables to avoid hard-coding rows and formulas

Tip 5: Use R1C1 formulas that make your life easier

Tip 6: Copy and paste in a single statement

Tip 7: Use With...End With to perform multiple actions

Next steps

Chapter 3   Referring to ranges

The Range object

Syntax for specifying a range

Referencing named ranges

Shortcut for referencing ranges

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

Using variables in the For statement

Variations on the For...Next loop

Exiting a loop early after a condition is met

Nesting one loop inside another loop

Do loops

Using the While or Until clause in Do loops

The VBA loop: For Each

Object variables

Flow control: Using If...Then...Else and Select Case

Basic flow control: If...Then...Else

Using Select Case...End Select for multiple conditions

Next steps

Chapter 5   R1C1-style formulas

Toggling to R1C1-style references

Witnessing the miracle of Excel formulas

Entering a formula once and copying 1,000 times

The secret: It’s not that amazing

Understanding the R1C1 reference style

Using R1C1 with relative references

Using R1C1 with absolute references

Using R1C1 with mixed references

Referring to entire columns or rows with R1C1 style

Replacing many A1 formulas with a single R1C1 formula

Remembering column numbers associated with column letters

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

Formulas

Strings

Numbers

Tables

Using arrays in names

Reserved names

Hiding names

Checking for the existence of a name

Next steps

Chapter 7   Event programming

Levels of events

Using events

Event parameters

Enabling events

Workbook events

Workbook-level sheet events

Worksheet events

Chart events

Embedded charts

Embedded chart and chart sheet 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

Application events

Embedded chart events

Creating a custom object

Using a custom object

Using collections

Creating a collection

Creating a collection in a standard module

Creating a collection in a class module

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

Userform events

Programming controls

Using basic form controls

Using labels, text boxes, and command buttons

Deciding whether to use list boxes or combo boxes in forms

Using the MultiSelect property of a list box

Adding option buttons to a userform

Adding graphics to a userform

Using a spin button on a userform

Using the MultiPage control to combine forms

Verifying field entry

Illegal window closing

Getting a file name

Next steps

Chapter 11 Data mining with Advanced Filter

Replacing a loop with AutoFilter

Using AutoFilter techniques

Selecting visible cells only

Advanced Filter—easier in VBA than in Excel

Using the Excel interface to build an advanced filter

Using Advanced Filter to extract a unique list of values

Extracting a unique list of values with the user interface

Extracting a unique list of values with VBA code

Getting unique combinations of two or more fields

Using Advanced Filter with criteria ranges

Joining multiple criteria with a logical OR

Joining two criteria with a logical AND

Other slightly complex criteria ranges

The most complex criteria: Replacing the list of values with a condition created as the result of a formula

Setting up a condition as the result of a formula

Using filter in place in Advanced Filter

Catching no records when using a filter in place

Showing all records after running a filter in place

The real workhorse: xlFilterCopy with all records rather than unique records only

Copying all columns

Copying a subset of columns and reordering

Excel in practice: Turning off a few drop-down menus in the AutoFilter

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

Defining the pivot cache

Creating and configuring the pivot table

Adding fields to the data area

Learning why you cannot move or change part of a pivot report

Determining the size of a finished pivot table to convert the pivot table to values

Using advanced pivot table features

Using multiple value fields

Grouping daily dates to months, quarters, or years

Changing the calculation to show percentages

Eliminating blank cells in the values area

Controlling the sort order with AutoSort

Replicating the report for every product

Filtering a data set

Manually filtering two or more items in a pivot field

Using the conceptual filters

Using the search filter

Setting up slicers to filter a pivot table

Setting up a timeline to filter an Excel 2019 pivot table

Using the Data Model in Excel 2019

Adding both tables to the Data Model

Creating a relationship between the two tables

Defining the pivot cache and building the pivot table

Adding model fields to the pivot table

Adding numeric fields to the values area

Putting it all together

Using other pivot table features

Calculated data fields

Calculated items

Using ShowDetail to filter a record set

Changing the layout from the Design tab

Settings for the report layout

Suppressing subtotals for multiple row fields

Next steps

Chapter 13 Excel power

File operations

Listing files in a directory

Importing and deleting a CSV file

Reading a text file into memory and parsing

Combining and separating workbooks

Separating worksheets into workbooks

Combining workbooks

Copying data to separate worksheets without using Filter

Exporting data to an XML file

Working with cell comments

Resizing comments

Placing a chart in a comment

Tracking user changes

Techniques for VBA pros

Creating an Excel state class module

Drilling-down a pivot table

Filtering an OLAP pivot table by a list of items

Creating a custom sort order

Creating a cell progress indicator

Using a protected password box

Changing case

Selecting with SpecialCells

Resetting a table’s format

Using VBA Extensibility to add code to new workbooks

Next steps

Chapter 14 Sample user-defined functions

Creating user-defined functions

Building a simple custom function

Sharing UDFs

Useful custom Excel functions

Setting the current workbook’s name in a cell

Setting the current workbook’s name and file path in a cell

Checking whether a workbook is open

Checking whether a sheet in an open workbook exists

Counting the number of workbooks in a directory

Retrieving the user ID

Retrieving date and time of last save

Retrieving permanent date and time

Validating an email address

Summing cells based on interior color

Counting unique values

Removing duplicates from a range

Finding the first nonzero-length cell in a range

Substituting multiple characters

Retrieving numbers from mixed text

Converting week number into date

Extracting a single element from a delimited string

Sorting and concatenating

Sorting numeric and alpha characters

Searching for a string within text

Reversing the contents of a cell

Returning the addresses of duplicate maximum values

Returning a hyperlink address

Returning the column letter of a cell address

Using static random

Using Select…Case on a worksheet

Next steps

Chapter 15 Creating charts

Using .AddChart2 to create a chart

Understanding chart styles

Formatting a chart

Referring to a specific chart

Specifying a chart title

Applying a chart color

Filtering a chart

Using SetElement to emulate changes from the plus icon

Using the format method to micromanage formatting options

Changing an object’s fill

Formatting line settings

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

Specifying an icon set

Specifying ranges for each icon

Using visualization tricks

Creating an icon set for a subset of a range

Using two colors of data bars in a range

Using other conditional formatting methods

Formatting cells that are above or below average

Formatting cells in the top 10 or bottom 5

Formatting unique or duplicate cells

Formatting cells based on their value

Formatting cells that contain text

Formatting cells that contain dates

Formatting cells that contain blanks or errors

Using a formula to determine which cells to format

Using the new NumberFormat property

Next steps

Chapter 17 Dashboarding with sparklines in Excel 2019

Creating sparklines

Scaling sparklines

Formatting sparklines

Using theme colors

Using RGB colors

Formatting sparkline elements

Formatting win/loss charts

Creating a dashboard

Observations about sparklines

Creating hundreds of individual sparklines in a dashboard

Next steps

Chapter 18 Reading from and writing to the web

Getting data from the web

Building multiple queries with VBA

Finding results from retrieved data

Putting it all together

Examples of scraping websites using web queries

Using Application.OnTime to periodically analyze data

Using ready mode for scheduled procedures

Specifying a window of time for an update

Canceling a previously scheduled macro

Closing Excel cancels all pending scheduled macros

Scheduling a macro to run x minutes in the future

Scheduling a verbal reminder

Scheduling a macro to run every two minutes

Publishing data to a web page

Using VBA to create custom web pages

Using Excel as a content management system

Bonus: FTP from Excel

Next steps

Chapter 19 Text file processing

Importing from text files

Importing text files with fewer than 1,048,576 rows

Dealing with text files with more than 1,048,576 rows

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

Using the Watches window to retrieve the real value of a constant

Using the Object Browser to retrieve the real value of a constant

Understanding Word’s objects

The Document object

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

Checking for the existence of tables

Checking for the existence of a field

Adding a table on the fly

Adding a field on the fly

SQL Server examples

Next steps

Chapter 22 Advanced userform techniques

Using the UserForm toolbar in the design of controls on userforms

More userform controls

CheckBox controls

TabStrip controls

RefEdit controls

ToggleButton controls

Using a scrollbar as a slider to select values

Controls and collections

Modeless userforms

Using hyperlinks in userforms

Adding controls at runtime

Resizing the userform on the fly

Adding a control on the fly

Sizing on the fly

Adding other controls

Adding an image on the fly

Putting it all together

Adding help to a userform

Showing accelerator keys

Adding control tip text

Creating the tab order

Coloring the active control

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

Retrieving the computer name

Checking whether an Excel file is open on a network

Retrieving display-resolution information

Customizing the About dialog box

Disabling the X for closing a userform

Creating a running timer

Playing sounds

Next steps

Chapter 24 Handling errors

What happens when an error occurs?

A misleading debug error in userform code

Basic error handling with the On Error GoTo syntax

Generic error handlers

Handling errors by choosing to ignore them

Suppressing Excel warnings

Encountering errors on purpose

Training your clients

Errors that won’t show up in debug mode

Errors while developing versus errors months later

Runtime error 9: Subscript out of range

Runtime error 1004: Method range of object global failed

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

Using Microsoft Office icons on a ribbon

Adding custom icon images to a ribbon

Troubleshooting error messages

The attribute “Attribute Name” on the element “customui ribbon” is not defined in the DTD/schema

Illegal qualified name character

Element “customui Tag Name” is unexpected according to content model of parent element “customui Tag Name”

Found a problem with some content

Wrong number of arguments or invalid property assignment

Invalid file format or file extension

Nothing happens

Other ways to run a macro

Using a keyboard shortcut to run a macro

Attaching a macro to a command button

Attaching a macro to a shape

Attaching a macro to an ActiveX control

Running a macro from a hyperlink

Next steps

Chapter 26 Creating add-ins

Characteristics of standard add-ins

Converting an Excel workbook to an add-in

Using Save As to convert a file to an add-in

Using the VB Editor to convert a file 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 tags

Adding buttons

Using CSS files

Using XML to define an Office add-in

Using JavaScript to add interactivity to an Office add-in

The structure of a function

Curly braces and spaces

Semicolons and line breaks

Comments

Variables

Strings

Arrays

JavaScript for loops

How to do an if statement in JavaScript

How to do a Select..Case statement in JavaScript

How to use a For each..next statement in JavaScript

Mathematical, logical, and assignment Operators

Math functions in JavaScript

Writing to the content pane or task pane

JavaScript changes for working in 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

The ribbon

Single-document interface

Modern array formulas

Quick Analysis tool

Charts

Pivot tables

Slicers

Icons

3D Models

SmartArt

Learning the new objects and methods

Compatibility mode

Using the Version property

Using the Excel8CompatibilityMode property

Next steps

Index