Techie Stuff Needed to Produce Applications
The Future of VBA and Windows Versions of Excel
Special Elements and Typographical Conventions
1. Unleash the Power of Excel with VBA
The Macro Recorder Doesn’t Work!
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
Using Macro Settings to Enable Macros in Workbooks Outside of 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
Using New File Types in Excel 2010
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 the AutoSum Button While Recording a Macro
Three Tips When 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: Cells(2,5) Is More Convenient Than Range(“E2”)
Tip 3: Ride the Range from the Bottom to Find Last Row
Tip 4: Use Variables to Avoid Hard-Coding Rows and Formulas
Tip 5: R1C1 Formulas That Make Your Life Easier
Tip 6: Learn to 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
Use the Cells
Property to Select a Range
Using the Cells
Property in the Range
Property
Use the Offset
Property to Refer to a Range
Use the Resize
Property to Change the Size of a Range
Use the Columns and Rows Properties to Specify a Range
Use the Union
Method to Join Multiple Ranges
Use the Intersect
Method to Create a New Range from Overlapping Ranges
Use the ISEMPTY
Function to Check Whether a Cell Is Empty
Use the CurrentRegion
Property to Select a Data Range
Use the Areas
Collection to Return a Noncontiguous Range
Creating User-Defined Functions
Set the Current Workbook’s Name in a Cell
Set the Current Workbook’s Name and File Path in a Cell
Check Whether a Workbook Is Open
Check Whether a Sheet in an Open Workbook Exists
Count the Number of Workbooks in a Directory
Retrieve Date and Time of Last Save
Retrieve Permanent Date and Time
Sum Cells Based on Interior Color
Remove Duplicates from a Range
Find the First Nonzero-Length Cell in a Range
Substitute Multiple Characters
Retrieve Numbers from Mixed Text
Sort Numeric and Alpha Characters
Search for a String Within Text
Reverse the Contents of a Cell
Return the Column Letter of a Cell Address
Using Select Case
on a Worksheet
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
Either/Or Decisions: If...Then...Else...End If
Using If...Else If...End If
for Multiple Conditions
Using Select Case...End Select
for Multiple Conditions
Complex Expressions in Case
Statements
Referring to Cells: A1 Versus R1C1 References
Switching Excel to Display R1C1-Style References
Enter a Formula Once and Copy 1,000 Times
The Secret: It’s Not That Amazing
Explanation of 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
Array Formulas Require R1C1 Formulas
7. What Is New in Excel 2010 and What Has Changed
If It Has Changed in the Front End, It Has Changed in VBA
Learning the New Objects and Methods
8. Create and Manipulate Names in VBA
Checking for the Existence of a Name
Workbook Level Sheet and Chart Events
10. Userforms: An Introduction
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
Referencing Charts and Chart Objects in VBA Code
Specifying the Size and Location of a Chart
Later Referring to a Specific Chart
Recording Commands from the Layout or Design Tabs
Specifying a Built-in Chart Type
Specifying a Template Chart Type
Changing a Chart’s Layout or Style
Using SetElement
to Emulate Changes on the Layout Tab
Changing a Chart Title Using VBA
Emulating Changes on the Format Tab
Using the Format
Method to Access Formatting Options
Creating True Open-High-Low-Close Stock Charts
Creating Bins for a Frequency Chart
Exporting a Chart as a Graphic
Creating a Dynamic Chart in a Userform
12. Data Mining with Advanced Filter
Replacing a Loop with AutoFilter
Using New AutoFilter Techniques
Advanced Filter Is 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 Filter in Place
Showing All Records After Filter in Place
The Real Workhorse: xlFilterCopy
with All Records Rather Than Unique Records Only
Copying a Subset of Columns and Reordering
Using Filter in Place with Unique Records Only
Excel in Practice: Turning Off a Few Drop-Downs in the AutoFilter
13. Using VBA to Create Pivot Tables
Creating a Vanilla Pivot Table in the Excel Interface
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 Size of a Finished Pivot Table to Convert the Pivot Table to Values
Using Advanced Pivot Table Features
Counting the Number of Records
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
Filtering an OLAP Pivot Table Using Named Sets
Using Other Pivot Table Features
Using ShowDetail
to Filter a Recordset
Changing the Layout from the Design Tab
Suppressing Subtotals for Multiple Row Fields
Read Entire TXT to Memory and Parse
Combining and Separating Workbooks
Separate Worksheets into Workbooks
Filter and Copy Data to Separate Worksheets
Resize Comments with Centering
Using Conditional Formatting to Highlight Selected Cell
Highlight Selected Cell Without Using Conditional Formatting
Select/Deselect Noncontiguous Cells
Calculating Time to Execute Code
Using VBA Extensibility to Add Code to New Workbooks
15. Data Visualizations and Conditional Formatting
Introduction to Data Visualizations
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
16. Reading from and Writing to the Web
Manually Creating a Web Query and Refreshing with VBA
Using VBA to Update an Existing Web Query
Building Many Web Queries with VBA
Using Application.OnTime
to Periodically Analyze Data
Scheduled Procedures Require Ready Mode
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 2 Minutes
Using VBA to Create Custom Web Pages
Using Excel as a Content Management System
17. Dashboarding with Sparklines in Excel 2010
Creating 100’s of Individual Sparklines in a Dashboard
Compile Error: Can’t Find Object or Library
Creating and Referencing Objects
Using the Watch 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
Arrays Make It Easier to Manipulate Data, but Is That All?
Importing Text Files with Fewer Than 1,048,576 Rows
Reading Text Files with More Than 1,048,576 Rows
21. Using Access as a Back End to Enhance Multiuser Access to Data
Adding a Record to the Database
Retrieving Records from the Database
Checking for the Existence of Tables
Checking for the Existence of a Field
22. Creating Classes, Records, and Collections
Trapping Application and Embedded Chart Events
Using Property Let
and Property Get
to Control How Users Utilize Custom Objects
Creating a Collection in a Standard Module
Creating a Collection in a Class Module
23. Advanced Userform Techniques
Using the UserForm Toolbar in the Design of Controls on Userforms
Using a Scrollbar As a Slider to Select Values
Resizing the Userform On-the-fly
Understanding an API Declaration
Check Whether an Excel File Is Open on a Network
Retrieve Display-Resolution Information
Disable the X for Closing a Userform
What Happens When an Error Occurs?
Debug Error Inside Userform Code Is Misleading
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
26. Customizing the Ribbon to Run Macros
Out with the Old, In with the New
Where to Add Your Code: customui Folder and File
Adding a Control to Your Ribbon
Renaming the Excel File and Opening the Workbook
Troubleshooting Error Messages
The Attribute “Attribute Name” on the Element “customui Ribbon” Is Not Defined in the DTD/Schema
Illegal Qualified Name Character
Excel Found Unreadable Content
Wrong Number of Arguments or Invalid Property Assignment
Attach a Macro to a Command Button
Attach 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 Your Client Install the Add-In
Standard Add-Ins Are Not Secure