VBA and Macros: Microsoft Excel 2010

Product Author Bios

Bill Jelen, known worldwide as MrExcel, presents live Excel seminars to accountants throughout the U.S. His 20 books on Microsoft Excel include Special Edition Using Excel 2007, Pivot Table Data Crunching, and VBA and Macros for Microsoft Excel. Honored by Microsoft as an Excel MVP, he has produced 500 episodes of the "Learn Excel from MrExcel" video podcast. Tracy Syrstad, a Mr.Excel.com consultant, has developed VBA solutions for clients around the world. She co-authored VBA and Macros for Microsoft Office Excel 2007 and co-edited Holy Macro! It's 600 Excel VBA Examples.

AUTOMATE REPORTS

BUILD FUNCTIONS

VISUALIZE DATA

WRITE FAST, RELIABLE SCRIPTS

Microsoft Excel 2010

VBA AND MACROS:

SAVE TIME AND SUPERCHARGE EXCEL 2010 WITH VBA AND MACROS!

Use Excel 2010 VBA and macros to automate virtually any routine task, and save yourself hours, days, maybe even weeks. Then learn how to make Excel do things you thought were simply impossible! This book reveals scripting techniques you won’t find anywhere else and shows you how to create automated reports that are amazingly powerful and useful. It helps you instantly visualize information so you can understand and act on it. It also shows you how to capture data from anywhere and use it anywhere, and helps you automate Excel 2010’s most powerful new features. Learning advanced Excel scripting has never been easier. You’ll find simple, step-by-step instructions, real-world examples and case studies, and 50 workbooks packed with bonus examples, macros, and solutions, straight from MrExcel.

• Work efficiently with ranges, cells, and R1C1-style formulas

• Build super-fast applications with arrays

• Customize the Excel 2010 Ribbon to run your macros

• Write Excel 2010 VBA code that works on older versions of Excel

• Create custom dialog boxes to collect information from your users

• Use error handling to make your VBA scripts more resilient

• Use Web queries to import data from virtually any online source

• Master advanced techniques such as classes and collections

• Use Excel VBA to control other Office programs…even control Windows itself,

via the Windows API

• Create add-ins to share or sell your programs

About MrExcel Library: Every book in the MrExcel Library pinpoints a specific set of crucial Excel tasks and presents focused skills and examples for performing them rapidly and effectively. Selected by Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website MrExcel.com, these books will

• Dramatically increase your productivity—saving you 50 hours a year or more

• Present proven, creative strategies for solving real-world problems

• Show you how to get great results, no matter how much data you have

• Help you avoid critical mistakes that even experienced users make

Online Sample Chapter

Unleash the Power of Excel with VBA

Table of Contents

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

Sample Pages

Download the sample pages (includes Chapter 1 and Index)

Errata

Download the errata