VBA and Macros

Introduction

Chapter 1 Unleash the Power of Excel with VBA

The Power of Excel

Barriers to Entry

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

Macro Security

Adding a Trusted Location

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

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

Using New File Types in Excel 2010

Understanding the VB Editor

VB Editor Settings

The Project Explorer

The Properties Window

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

Next Steps

Chapter 2 This Sounds Like BASIC, So Why Doesn't It Look Familiar?

I Can't Understand This Code

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: 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

Next Steps

Chapter 3 Referring to Ranges

The Range Object

Syntax to Specify a Range

Named Ranges

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 Union Method to Join Multiple Ranges

Use the ISEMPTY Function to Check Whether a Cell Is Empty

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

Referencing Tables

Next Steps

Chapter 4 User-Defined Functions

Creating User-Defined Functions

Sharing UDFs

Useful Custom Excel 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 USERID

Retrieve Date and Time of Last Save

Retrieve Permanent Date and Time

Validate an E-mail Address

Sum Cells Based on Interior Color

Count Unique Values

Remove Duplicates from a Range

Find the First Nonzero-Length Cell in a Range

Substitute Multiple Characters

Retrieve Numbers from Mixed Text

Convert Week Number into Date

Separate Delimited String

Sort and Concatenate

Sort Numeric and Alpha Characters

Search for a String Within Text

Reverse the Contents of a Cell

Multiple Max

Return Hyperlink Address

Return the Column Letter of a Cell Address

Static Random

Using Select Case on a Worksheet

Next Steps

Chapter 5 Looping and Flow Control

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

While...Wend Loops

VBA Loop: For Each

Object Variables

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

Basic Flow Control: If...Then...Else

Conditions

If...Then...End If

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

Nesting If Statements

Next Steps

Chapter 6 R1C1-Style Formulas

Referring to Cells: A1 Versus R1C1 References

Switching Excel to Display R1C1-Style References

The Miracle of Excel Formulas

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

Next Steps

Chapter 7 What Is New in Excel 2010 and What Has Changed.

If It Has Changed in the Front End, It Has Changed in VBA

The Ribbon

Charts

Pivot Tables

Slicers

Conditional Formatting

Tables

Sorting

SmartArt

Learning the New Objects and Methods

Compatibility Mode

Version

Excel8CompatibilityMode

Next Steps

Chapter 8 Create and Manipulate Names in VBA

Excel Names

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 9 Event Programming

Levels of Events

Using Events

Event Parameters

Enabling Events

Workbook Events

Workbook_Activate()

Workbook_Deactivate()

Workbook_Open()

Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Workbook_BeforePrint(Cancel As Boolean)

Workbook_BeforeClose(Cancel As Boolean)

Workbook_NewSheet(ByVal Sh As Object).

Workbook_WindowResize(ByVal Wn As Window).

Workbook_WindowActivate(ByVal Wn As Window)

Workbook_WindowDeactivate(ByVal Wn As Window).

Workbook_AddInInstall().

Workbook_AddInUninstall.

Workbook_Sync(ByVal SyncEventType As Office.MsoSyncEventType).

Workbook_PivotTableCloseConnection(ByVal Target As PivotTable)

Workbook_PivotTableOpenConnection(ByVal Target As PivotTable).

Workbook_RowsetComplete(ByVal Description As String, ByVal Sheet As String, ByVal Success As Boolean).

Workbook_BeforeXmlExport(ByVal Map As XmlMap, ByVal Url As String, Cancel As Boolean)

Workbook_AfterXmlExport(ByVal Map As XmlMap, ByVal Url As String, ByVal Result As XlXmlExportResult).

Workbook_BeforeXmlImport(ByVal Map As XmlMap, ByVal Url As String, ByVal IsRefresh As Boolean, Cancel As Boolean).

Workbook_AfterXmlImport(ByVal Map As XmlMap, ByVal IsRefresh As Boolean, ByVal Result As XlXmlImportResult).

Workbook Level Sheet and Chart Events.

Worksheet Events

Worksheet_Activate()

Worksheet_Deactivate()

Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean).

Worksheet_Calculate()

Worksheet_Change(ByVal Target As Range)

Worksheet_SelectionChange(ByVal Target As Range).

Worksheet_FollowHyperlink(ByVal Target As Hyperlink).

Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Chart Sheet Events.

Embedded Charts

Chart_Activate()

Chart_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)

Chart_BeforeRightClick(Cancel As Boolean).

Chart_Calculate()

Chart_Deactivate().

Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long).

Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long).

Chart_MouseUp(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long).

Chart_Resize().

Chart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long).

Chart_SeriesChange(ByVal SeriesIndex As Long, ByVal PointIndex As Long).

Chart_DragOver()

Chart_DragPlot()

Application-Level Events.

AppEvent_AfterCalculate()

AppEvent_NewWorkbook(ByVal Wb As Workbook)

AppEvent_ProtectedViewWindowActivate(ByVal Pvw As ProtectedViewWindow).

AppEvent_ProtectedViewWindowBeforeClose(ByVal Pvw As ProtectedViewWindow, ByVal Reason As XlProtectedViewCloseReason, Cancel As Boolean).

AppEvent_ProtectedViewWindowDeactivate(ByVal Pvw As ProtectedViewWindow)

AppEvent_ProtectedViewWindowOpen(ByVal Pvw As ProtectedViewWindow).

AppEvent_ProtectedViewWindowResize(ByVal Pvw As ProtectedViewWindow)

AppEvent_SheetActivate (ByVal Sh As Object).

AppEvent_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean).

AppEvent_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean).

AppEvent_SheetCalculate(ByVal Sh As Object)

AppEvent_SheetChange(ByVal Sh As Object, ByVal Target As Range).

AppEvent_SheetDeactivate(ByVal Sh As Object).

AppEvent_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

AppEvent_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range).

AppEvent_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)

AppEvent_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)

AppEvent_WindowDeactivate(ByVal Wb As Workbook, ByVal Wn As Window).

AppEvent_WindowResize(ByVal Wb As Workbook, ByVal Wn As Window).

AppEvent_WorkbookActivate(ByVal Wb As Workbook)

AppEvent_WorkbookAddinInstall(ByVal Wb As Workbook)

AppEvent_WorkbookAddinUninstall(ByVal Wb As Workbook).

AppEvent_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)

AppEvent_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)

AppEvent_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)

AppEvent_WorkbookNewSheet(ByVal Wb As Workbook, ByVal Sh As Object).

AppEvent_WorkbookOpen(ByVal Wb As Workbook)

AppEvent_WorkbookPivotTableCloseConnection(ByVal Wb As Workbook, ByVal Target As PivotTable)

AppEvent_WorkbookPivotTableOpenConnection(ByVal Wb As Workbook, ByVal Target As PivotTable)

AppEvent_WorkbookRowsetComplete(ByVal Wb As Workbook, ByVal Description As String, ByVal Sheet As String, ByVal Success As Boolean).

AppEvent_WorkbookSync(ByVal Wb As Workbook, ByVal SyncEventType As Office.MsoSyncEventType).

AppEvent_WorkbookBeforeXmlExport(ByVal Wb As Workbook, ByVal Map As XmlMap, ByVal Url As String, Cancel As Boolean)

AppEvent_WorkbookAfterXmlExport(ByVal Wb As Workbook, ByVal Map As XmlMap, ByVal Url As String, ByVal Result As XlXmlExportResult)

AppEvent_WorkbookBeforeXmlImport(ByVal Wb As Workbook, ByVal Map As XmlMap, ByVal Url As String, ByVal IsRefresh As Boolean, Cancel As Boolean).

AppEvent_WorkbookAfterXmlImport(ByVal Wb As Workbook, ByVal Map As XmlMap, ByVal IsRefresh As Boolean, ByVal Result As XlXmlImportResult)

Next Steps

Chapter 10 Userforms: An Introduction

User Interaction Methods

Input Boxes.

Message Boxes

Creating a Userform

Calling and Hiding a Userform

Programming the Userform

Userform Events

Programming Controls

Using Basic Form Controls

Using Labels, Text Boxes, and Command Buttons

Using a Spin Button on a Userform

Using the MultiPage Control to Combine Forms

Verifying Field Entry

Illegal Window Closing

Getting a Filename

Next Steps

Chapter 11 Creating Charts

Charting in Excel 2010

Referencing Charts and Chart Objects in VBA Code.

Creating a Chart.

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

Using SetElement to Emulate Changes on the Layout Tab

Using SetElement to Emulate Changes on the Layout Tab

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 Advanced Charts

Creating True Open-High-Low-Close Stock Charts

Creating Bins for a Frequency Chart

Creating a Stacked Area Chart

Exporting a Chart as a Graphic

Creating a Dynamic Chart in a Userform

Creating Pivot Charts.

Next Steps.

Chapter 12 Data Mining with Advanced Filter

Replacing a Loop with AutoFilter.

Using New AutoFilter Techniques

Selecting Visible Cells Only

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.

The Most Complex Criteria: Replacing the List of Values with a Condition Created as the Result of a Formula

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

The Real Workhorse: xlFilterCopy with All Records Rather Than Unique Records Only

Copying All Columns

Copying a Subset of Columns and Reordering

The Real Workhorse: xlFilterCopy with All Records Rather Than Unique Records Only

The Real Workhorse: xlFilterCopy with All Records Rather Than Unique Records Only

Using Filter in Place with Unique Records Only

Excel in Practice: Turning Off a Few Drop-Downs in the AutoFilter

Next Steps

Chapter 13 Using VBA to Create Pivot Tables

Introducing Pivot Tables

Understanding Versions

New in Excel 2010

New Beginning with Excel 2007

Creating a Vanilla Pivot Table in the Excel Interface

Understanding Compact Layout

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 Size of a Finished Pivot Table to Convert the Pivot Table to Values

Using Advanced Pivot Table Features

Using Multiple Value Fields

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

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

Filtering an OLAP Pivot Table Using Named Sets

Using Other Pivot Table Features

Calculated Data Fields

Calculated Items

Using ShowDetail to Filter a Recordset

Changing the Layout from the Design Tab

Suppressing Subtotals for Multiple Row Fields

Next Steps

Chapter 14 Excel Power

File Operations

List Files in a Directory

Import CSV

Read Entire TXT to Memory and Parse

Combining and Separating Workbooks

Separate Worksheets into Workbooks

Combine Workbooks

Filter and Copy Data to Separate Worksheets

Export Data to Word

Working with Cell Comments

List Comments

Resize Comments

Resize Comments with Centering

Place a Chart in a Comment

Utilities to Wow Your Clients

Using Conditional Formatting to Highlight Selected Cell

Highlight Selected Cell Without Using Conditional Formatting

Custom Transpose Data

Select/Deselect Noncontiguous Cells

Techniques for VBA Pros

Pivot Table Drill-Down

Speedy Page Setup

Calculating Time to Execute Code

Custom Sort Order

Cell Progress Indicator

Protected Password Box

Change Case

Selecting with SpecialCells

ActiveX Right-Click Menu

Cool Applications

Historical Stock/Fund Quotes

Using VBA Extensibility to Add Code to New Workbooks

Next Steps.

Chapter 15 Data Visualizations and Conditional Formatting

Introduction to Data Visualizations

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 16 Reading from and Writing to the Web

Getting Data from 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 Verbal Reminder

Scheduling a Macro to Run Every 2 Minutes

Publishing Data to a Web Page

Using VBA to CreateCustom Web Pages

Using Excel as a Content Management System

Bonus: FTP from Excel

Next Steps

Chapter 17 Dashboarding with Sparklines in Excel 2010

Creating Sparklines

Scaling the Sparklines

Formatting Sparklines

Using Theme Colors

Using RGB Colors

Formatting Sparkline Elements

Formatting Win/Loss Charts

Creating a Dashboard

Observations About Sparklines

Creating 100's of Individual Sparklines in a Dashboard

Next Steps

Chapter 18 Automating Word

Early Binding

Compile Error: Can't Find Object or Library

Late Binding

Creating and Referencing Objects

The New Keyword

CreateObject Function

GetObject Function

Using Constant Values

Using the Watch 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

Document Object

Selection Object

Range Object

Bookmarks

Controlling Form Fields in Word

Next Steps

Chapter 19 Arrays

Declare an Array

Multidimensional Arrays

Fill an Array

Empty an Array

Arrays Make It Easier to Manipulate Data, but Is That All?

Dynamic Arrays

Passing an Array

Next Steps

Chapter 20 Text File Processing

Importing from Text Files

Importing Text Files with Fewer Than 1,048,576 Rows

Reading Text Files with More Than 1,048,576 Rows

Writing Text Files

Next Steps

Chapter 21 Using Access as a Back End to Enhance Multiuser Access to Data

ADO Versus DAO

The Tools of ADO

Adding a Record to the Database

Retrieving Records from the 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 Creating Classes, Records, 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 Property Let and Property Get to Control How Users Utilize Custom Objects

Collections

Creating a Collection in a Standard Module

Creating a Collection in a Class Module

User-Defined Types

Next Steps

Chapter 23 Advanced Userform Techniques

Using the UserForm Toolbar in the Design of Controls on Userforms

More Userform Controls

Check Boxes

Tab Strips

RefEdit

Toggle Buttons

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 the Userform

Showing Accelerator Keys

Adding Control Tip Text

Creating the Tab Order

Coloring the Active Control

Transparent Forms

Next Steps

Chapter 24 Windows API

What Is the Windows API?

Understanding an API Declaration

Using an API Declaration

API Examples

Retrieve the Computer Name

Check Whether an Excel File Is Open on a Network

Retrieve Display-Resolution Information

Custom About Dialog

Disable the X for Closing a Userform

Running Timer

Playing Sounds

Retrieving a File Path

Finding More API Declarations

Next Steps

Chapter 25 Handling Errors

What Happens When an Error Occurs?

Debug Error Inside Userform Code Is Misleading

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

Train Your Clients

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 26 Customizing the Ribbon to Run Macros

Out with the Old, In with the New

Where to Add Your Code: customui Folder and File

Creating the Tab and Group

Adding a Control to Your Ribbon

Accessing the File Structure

Understanding the RELS File

Renaming the Excel File and Opening the Workbook

Custom UI Editor Tool

Using Images on Buttons

Microsoft Office Icons

Custom Icon Images

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"

Excel Found Unreadable Content

Wrong Number of Arguments or Invalid Property Assignment

Nothing Happens

Other Ways to Run a Macro

Keyboard Shortcut

Attach a Macro to a Command Button

Attach a Macro to a Shape

Attach a Macro to an ActiveX Control

Running a Macro from a Hyperlink

Next Steps

Chapter 27 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 Your Client Install the 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





9780789743145 TOC 6/1/2010