Techie Stuff Needed to Produce Applications
The Future of VBA and Windows Versions of Excel
Special Elements and Typographical Conventions
1 Unleashing the Power of Excel with VBA
The Macro Recorder Doesn’t Work!
No One 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
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
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 Shortcomings of the Macro Recorder
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
2 This Sounds Like BASIC, So Why Doesn’t It Look Familiar?
Understanding the Parts of VBA “Speech”
VBA Help Files: Using F1 to Find Anything
Examining Recorded Macro Code: Using the VB Editor and Help
Using Debugging Tools to Figure Out Recorded 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
Object Browser: The Ultimate Reference
Seven Tips for Cleaning Up Recorded Code
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
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
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
Using the While
or Until
Clause in Do
Loops
Flow Control: Using If...Then...Else
and Select Case
Basic Flow Control: If...Then...Else
Using Select Case...End Select
for Multiple Conditions
Referring to Cells: A1 Versus R1C1 References
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
6 Creating and Manipulating Names in VBA
Checking for the Existence of a Name
Workbook-Level Sheet and Chart Events
Embedded Chart and Chart Sheet Events
Declaring a Multidimensional Array
9 Creating Classes and Collections
Trapping Application and Embedded Chart Events
Creating a Collection in a Standard Module
Creating a Collection in a Class Module
Using User-Defined Types to Create Custom Properties
Using Labels, Text Boxes, and Command Buttons
Deciding Whether to Use List Boxes or Combo Boxes in Forms
Adding Option Buttons to a Userform
Using a Spin Button on a Userform
Using the MultiPage
Control to Combine Forms
11 Data Mining with Advanced Filter
Replacing a Loop with AutoFilter
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
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 a Subset of Columns and Reordering
Excel in Practice: Turning Off a Few Drop-downs in the AutoFilter
12 Using VBA to Create Pivot Tables
Understanding How Pivot Tables Evolved Over Various Excel Versions
While Building a Pivot Table in Excel VBA
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
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
Manually Filtering Two or More Items in a Pivot Field
Setting Up Slicers to Filter a Pivot Table
Setting Up a Timeline to Filter an Excel 2016 Pivot Table
Using the Data Model in Excel 2016
Adding Both Tables to the Data Model
Creating a Relationship Between the Two Tables
Defining the PivotCache and Building the Pivot Table
Adding Model Fields to the Pivot Table
Adding Numeric Fields to the Values Area
Using Other Pivot Table Features
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
Importing and Deleting a CSV File
Reading a Text File into Memory and Parsing
Combining and Separating Workbooks
Separating Worksheets into Workbooks
Filtering and Copying Data to Separate Worksheets
Copying Data to Separate Worksheets Without Using Filter
Using Conditional Formatting to Highlight the Selected Cell
Highlighting the Selected Cell Without Using Conditional Formatting
Selecting/Deselecting Noncontiguous Cells
Creating an Excel State Class Module
Filtering an OLAP Pivot Table by a List of Items
Creating a Cell Progress Indicator
Using a Protected Password Box
Getting Historical Stock/Fund Quotes
Using VBA Extensibility to Add Code to New Workbooks
14 Sample User-Defined Functions
Creating User-Defined 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 Date and Time of Last Save
Retrieving Permanent Date and Time
Summing Cells Based on Interior Color
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 Numeric and Alpha Characters
Searching for a String Within Text
Reversing the Contents of a Cell
Returning the Addresses of Duplicate Max Values
Returning the Column Letter of a Cell Address
Using Select Case
on a Worksheet
Contrasting the Good and Bad VBA to Create Charts
Planning for More Charts to Break
Using .AddChart2
to Create a Chart
Using SetElement
to Emulate Changes from the Plus Icon
Using the Format
Method to Micromanage Formatting Options
Exporting a Chart as a Graphic
Considering Backward Compatibility
16 Data Visualizations and Conditional Formatting
VBA Methods and Properties for Data Visualizations
Adding Color Scales to a Range
Specifying Ranges for Each Icon
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
17 Dashboarding with Sparklines in Excel 2016
Creating Hundreds of Individual Sparklines in a Dashboard
18 Reading from and Writing to the Web
Building Multiple Queries with VBA
Finding Results from Retrieved Data
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 Macro to Run Every Two Minutes
Using VBA to Create Custom Web Pages
Using Excel as a Content Management System
Importing Text Files with Fewer Than 1,048,576 Rows
Dealing with Text Files with More Than 1,048,576 Rows
Using Early Binding to Reference a Word Object
Using Late Binding to Reference a Word Object
Using the New
Keyword to Reference a 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 the Watches Window to Retrieve the Real Value of a Constant
Using the Object Browser to Retrieve the Real Value of a Constant
Controlling Form Fields in Word
21 Using Access as a Back End to Enhance Multiuser Access to Data
Retrieving Records from a Database
Checking for the Existence of Tables
Checking for the Existence of a Field
22 Advanced Userform Techniques
Using the UserForm Toolbar in the Design of Controls on Userforms
Resizing the Userform On the Fly
23 The Windows Application Programming Interface (API)
Understanding an API Declaration
Making 32-Bit- and 64-Bit-Compatible API Declarations
Checking Whether an Excel File Is Open on a Network
Retrieving Display-Resolution Information
Disabling the X for Closing a Userform
What Happens When an Error Occurs?
A Misleading Debug Error in Userform Code
Basic Error Handling with the On Error GoTo
Syntax
Handling Errors by Choosing to Ignore Them
Encountering Errors on Purpose
Errors While Developing Versus Errors Months Later
Runtime Error 9: Subscript Out of Range
Runtime Error 1004: Method Range of Object Global Failed
Errors Caused by Different Versions
25 Customizing the Ribbon to Run Macros
Where to Add Code: The customui Folder and File
Renaming an Excel File and Opening a Workbook
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
Found a Problem with Some Content
Wrong Number of Arguments or Invalid Property Assignment
Invalid File Format or File Extension
Using a Keyboard Shortcut to Run a Macro
Attaching a Macro to a Command Button
Attaching a Macro to an ActiveX Control
Running a Macro from a Hyperlink
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
Using a Hidden Workbook as an Alternative to an Add-in
27 An Introduction to Creating Office Add-ins
Creating Your First Office Add-in—Hello World
Adding Interactivity to an Office Add-in
Using XML to Define an Office Add-in
Using JavaScript to Add Interactivity to an Office Add-in
How to Do an if
Statement in JavaScript
How to Do a Select..Case
Statement in JavaScript
How to Do a For each..next
Statement in JavaScript
Mathematical, Logical, and Assignment Operators
Writing to the Content Pane or Task Pane
JavaScript Changes for Working in an Office Add-in
Napa Office 365 Development Tools
28 What’s New in Excel 2016 and What’s Changed
If It Has Changed in the Front End, It Has Changed in VBA
Single Document Interface (SDI)
Learning the New Objects and Methods