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
Understanding Which File Types Allow Macros
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
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 or Quick Analysis 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: Use More Reliable Ways to Find the 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
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
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...ElseIf...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
Toggling to 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
6 Create and Manipulate Names in VBA
Checking for the Existence of a Name
Workbook Level Sheet and Chart Events
Declare a Multidimensional Array
9 Creating Classes, Records, and Collections
Trapping Application and Embedded Chart Events
Using Property Let and Property Get to Control How Users Utilize Custom Objects
Using Collections to Hold Multiple Records
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
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
Excel in Practice: Turning Off a Few Drop-Downs in the AutoFilter
12 Using VBA to Create Pivot Tables
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 2013 Pivot Table
Using the Data Model in Excel 2013
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 Recordset
Changing the Layout from the Design Tab
Settings for the Report Layout
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
Using Conditional Formatting to Highlight Selected Cell
Highlight Selected Cell Without Using Conditional Formatting
Select/Deselect Noncontiguous Cells
Using VBA Extensibility to Add Code to New Workbooks
14 Sample User-Defined Functions
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
Considering Backward Compatibility
Referencing the Chart Container
Understanding the Global Settings
Specifying a Built-in Chart Type
Specifying Location and Size of the Chart
Creating a Chart in Various Excel Versions
Using .AddChart2 Method in Excel 2013
Creating Charts in Excel 2007–2013
Creating Charts in Excel 2003–2013
Quickly Formatting a Chart Using New Excel 2013 Features
Using SetElement to Emulate Changes from the Plus Icon
Using the Format Method to Micromanage Formatting Options
Creating True Open-High-Low-Close Stock Charts
Creating Bins for a Frequency Chart
Exporting a Chart as a Graphic
16 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
17 Dashboarding with Sparklines in Excel 2013
Creating Hundreds of Individual Sparklines in a Dashboard
18 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 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
Reading Text Files One Row at a Time
Using Early Binding to Reference the Word Object
Using Late Binding to Reference the 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 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
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 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
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?
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
25 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
Using Microsoft Office Icons on Your Ribbon
Adding Custom Icon Images to Your Ribbon
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 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 Your Client Install the Add-In
Using a Hidden Workbook as an Alternative to an Add-In
27 An Introduction to Creating Apps for Office
Creating Your First App—Hello World
Adding Interactivity to Your App
Using JavaScript to Add Interactivity to Your App
How to Do an if Statement in JS
How to Do a Select..Case Statement in JS
How to Do a For each..next Statement in JS
Mathematical, Logical, and Assignment Operators
Writing to the Content or Task Pane
JavaScript Changes for Working in the Office App
Napa Office 365 Development Tools
28 What Is New in Excel 2013 and What Has Changed
If It Has Changed in the Front End, It Has Changed in VBA
Single Document Interface (SDI)