Home > Store

VBA and Macros: Microsoft Excel 2010

Register your product to gain access to bonus material or receive a coupon.

VBA and Macros: Microsoft Excel 2010


  • Sorry, this book is no longer in print.
Not for Sale

eBook (Watermarked)

  • Your Price: $29.43
  • List Price: $36.79
  • Includes EPUB and PDF
  • About eBook Formats
  • This eBook includes the following formats, accessible from your Account page after purchase:

    ePub EPUB The open industry format known for its reflowable content and usability on supported mobile devices.

    Adobe Reader PDF The popular standard, used most often with the free Acrobat® Reader® software.

    This eBook requires no passwords or activation to read. We customize your eBook by discreetly watermarking it with your name, making it uniquely yours.


  • Copyright 2011
  • Dimensions: 7" x 9-1/8"
  • Pages: 656
  • Edition: 1st
  • Book
  • ISBN-10: 0-7897-4314-0
  • ISBN-13: 978-0-7897-4314-5





Microsoft Excel 2010



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

Sample Content

Online Sample Chapter

Unleash the Power of Excel with VBA

Sample Pages

Download the sample pages (includes Chapter 1 and Index)

Table of Contents


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


    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


    Pivot Tables


    Conditional Formatting




Learning the New Objects and Methods

Compatibility Mode



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





    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_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_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_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

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


    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_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)

    Chart_BeforeRightClick(Cancel As Boolean).



    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_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long).

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



Application-Level Events.


    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


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


    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


    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

More Information

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.


Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.


If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.


This site is not directed to children under the age of 13.


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020