Microsoft Excel

Bill Jelen, Mr. Excel Tracy Syrstad

Contents at a Glance

Introduction 1

I First Steps up the VBA Learning Curve

1 Unleash the Power of Excel with VBA! 11

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

3 Referring to Ranges 61

4 User-Defined Functions 75

5 Looping 103

6 R1C1 Style Formulas 117

7 Names 131

8 Event Programming 141

9 UserForms—An Introduction 161

II Automating Excel Power in VBA

10 Charts 175

11 Data Mining with Advanced Filter 207

12 Pivot Tables 235

13 Excel Power 291

14 Reading from and Writing to the Web 331

15 XML in Excel 2003 Professional 349

16 Automating Word 359

III Techie Stuff You Will Need to Produce Applications for the Administrator to Run

17 Arrays 379

18 Text File Processing 387

19 Using Access as a Back End to Enhance Multi-User

Access to Data 401

20 Creating Classes, Records,and Collections 415

21 Advanced UserForm Techniques 433

22 Windows Application Programming Interface (API) 453

23 Handling Errors 467

24 Using Custom Menus to Run Macros 481

25 Add-Ins 497

26 Case Study—Designing an Excel Application 505

Index 517

800 East 96th Street Indianapolis, Indiana 46240

VBA and Macros for Microsoft Excel

Copyright © 2004 by Sams Publishing

All rights reserved. No part of this book shall be reproduced, stored in a retrieval system, or transmitted by any means, electronic, mechanical, photocopying, recording, or otherwise, without written permission from the publisher. No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions. Nor is any liability assumed for damages resulting from the use of the information contained herein.

International Standard Book Number: 0-7897-3129-0

Library of Congress Catalog Card Number: 2004102247

Printed in the United States of America

First Printing: April 2004


All terms mentioned in this book that are known to be trademarks or service marks have been appropriately capitalized. Sams Publishing cannot attest to the accuracy of this information. Use of a term in this book should not be regarded as affecting the validity of any trademark or service mark.

Warning and Disclaimer

Every effort has been made to make this book as complete and as accurate as possible, but no warranty or fitness is implied. The information provided is on an "as is" basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book.

Associate Publisher

Michael Stephens

Acquisitions Editor

Loretta Yates

Development Editor

Sean Dixon

Managing Editor

Charlotte Clapp

Project Editor

Andy Beaster

Copy Editor

Margo Catts


Erika Millen


Kathy Bidwell

Technical Editor

Tom Urtis

Publishing Coordinator

Cindy Teeters

Book Designer

Anne Jones

Page Layout

Bronkella Publishing Kelly Maish Michelle Mitchell

Bulk Sales

Sams Publishing offers excellent discounts on this book when ordered in quantity for bulk purchases or special sales. For more information, please contact

U.S. Corporate and Government Sales 1-800-382-3419

[email protected]

For sales outside of the U.S., please contact

International Sales 1-317-428-3341

[email protected]


Introduction 1

Getting Results with VBA 1

What Is in This Book 3

Getting Up the Learning Curve 3

Excel VBA Power 3

The Techie Stuff Needed to Produce Applications for Others 3

Will This Book Teach Excel? 4

A Brief History of Spreadsheets and Macros 4

The Future of VBA and Excel 5

Special Elements and Typographical Conventions 6

Next Steps 7

I FIRST STEPS UP THE VBA LEARNING CURVE 1 Unleash the Power of Excel with VBA! 11

The Power of Excel 11

Barriers to Entry 11

The Macro Recorder Doesn't Work! 11

Visual Basic Is Not Like BASIC 12

The Good News—It Is Easy to Climb the Learning Curve 12

The Great News—Excel with VBA Is Worth the Effort 12

Knowing Your Tools—The Visual Basic Toolbar 13

Macro Security 14

Very High Security 14

High Security 15

Medium Security 15

Low Security 15

Overview of Recording, Storing, and Running a Macro 15

Filling Out the Record Macro Dialog 15

Running a Macro 16

Creating a Macro Button 16

Assigning a Macro to a Form Control 17

Understanding the Visual Basic Editor (VBE) 18

VBE Settings 18

The Project Explorer 19

The Properties Window 20

Examining Code in the Programming Window 22

Running the Macro on Another Day Produces Undesired Results 24

A Possible Solution: Using Relative References when Recording 25

Frustration 28

Next Steps: Learning VBA Is the Solution 28

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

I Can't Understand This Code 29

Understanding the Parts of VBA "Speech" 30

Is VBA Really This Hard? No! 33

VBA Help Files—Using Fl to Find Anything 33

Using Help Topics 35

Examining Recorded Macro Code—Using the VB Editor and Help 36

Optional Parameters 37

Defined Constants 37

Properties Can Return Objects 41

Using Debugging Tools to Figure Out Recorded Code 42

Stepping Through Code 42

More Debugging Options—Breakpoints 44

Backing Up or Moving Forward in Code 45

Not Stepping Through Each Line of Code 45

Querying Anything While Stepping Through Code 45

Using a Watch to Set a Breakpoint 50

Using a Watch on an Object 50

The Ultimate Reference to All Objects, Methods, Properties 51

Five Easy Tips for Cleaning Up Recorded Code 53

Tip 1: Don't Select Anything 53

Tip 2: Ride the Range from the Bottom to Find Last Row 54

Tip 3: Use Variables to Avoid Hard-coding Rows and Formulas 55

Tip 4: Learn to Copy and Paste in a Single Statement 55

Tip 5:Usewith...Endwith If You Are Performing Multiple Actions to the Same

Cell or Range of Cells 56

Putting It All Together—Fixing the Recorded Code 56

Next Steps 59

Contents ^

3 Referring to Ranges 61

The Range Object 61

Using the Top-Left and Bottom-Right Corners of a Selection to Specify a Range 62

Shortcut for Referencing Ranges 62

Named Ranges 62

Referencing Ranges in Other Sheets 63

Referencing a Range Relative to Another Range 63

Using the Cells Property to Select a Range 64

Using the Cells Property in the Range Property 65

Using the Offset Property to Refer to a Range 65

Using the Resize Property to Change the Size of a Range 67

Using the Columns and Rows Properties to Specify a Range 68

Using the Union Method to Join Multiple Ranges 68

Using the Intersect Method to Create a New Range from Overlapping Ranges 69

Using the IsEmpty Function to Check Whether a Cell Is Empty 69

Using the CurrentRegion Property to Quickly Select a Data Range 70

Using the Areas Collection to Return a Non-contiguous Range 74

Next Steps 74

4 User-Defined Functions 75

Creating User-Defined Functions 75

Useful Custom Excel Functions 77

Set the Current Workbook's Name in a Cell 77

Set the Current Workbook's Name and File-Path in a Cell 77

Check Whether a Workbook Is Open 77

Check Whether a Sheet in an Open Workbook Exists 78

Count the Number of Workbooks in a Directory 79

Retrieve UserlD 79

Retrieve Date and Time of Last Save 81

Retrieve Permanent Date and Time 81

Validate an Email Address 82

Sum Cells Based on the Interior Color 83

Retrieve Interior Cell Color Name or Index 84

Retrieve Text Color Index 87

Count Unique Values 88

Remove Duplicates from a Range 89

Find the First Non-Zero-Length Cell in a Range 91

Substitute Multiple Characters 92

Retrieve Numbers from Mixed Text 93

Convert Week Number into Date 94

Separate Delimited String 94

Sort and Concatenate 95

Sort Numeric and Alpha Characters 97

Search for a String Within Text 98

Reverse the Contents of a Cell 99

Multiple Max 99

Return Hyperlink Address 100

Return the Column Letter of a Cell Address 101

Static Random 101

Using Select Case on a Worksheet 101

Next Steps 102

5 Looping 103

For...NextLoops 103

Using Variables in the For Statement 106

Variations on the For...Next Loop 106

Exiting a Loop Early After a Condition Is Met 107

Nesting One Loop Inside Another Loop 107

Do Loops 108

Using the While or Until Clause in Do Loops 111

whiie...wend Loops 113

The VBA Loop: For Each 113

Object Variables 113

Next Steps 116

6 R1C1 Style Formulas 117

Referring to Cells: A1 Versus R1C1 References 117

Why Care About R1C1 Style? 117

Not Just an Annoyance 117

Switching Excel to Display R1C1 Style References 118

The Miracle of Excel Formulas 119

Enter a Formula Once and Copy 1,000 Times 119

The Secret—It Is Not That Amazing 120

Explanation of R1C1 Reference Style 121

Using R1C1 with Relative References 121

Using R1C1 with Absolute References 122

Using R1C1 with Mixed References 123

Referring to Entire Columns or Rows with R1C1 Style 123

Replacing Many A1 Formulas with a Single R1C1 Formula 124

Remembering Column Numbers Associated with Column Letters 125

Conditional Formatting—R1C1 Required 126

Setting Up Conditional Formatting in the User Interface 126

Setting Up Conditional Formats in VBA 127

Array Formulas Require Conditional Formatting 129

Next Steps 130

7 Names 131

Global Versus Local Names 131

Adding Names 132

Deleting Names 134

Types of Names 134

Formulas 134

Strings 135

Numbers 136

Using Arrays in Names 136

Reserved Names 137

Hiding Names 138

Checking for the Existence of a Name 138

Next Steps 140

8 Event Programming 141

Levels of Events 141

Using Events 141

Event Parameters 142

Enabling Events 142

Workbook Events 143

Workbook_Activate() 143

Workbook_Deactivate() 143

Workbook_Open() 143

Workbook_BeforeSave(ByVal SaveAsUI As Boolean,

Cancel As Boolean) 144

Workbook_BeforePrint(Cancel As Boolean) 144

Workbook_BeforeClose(Cancel As Boolean) 145

Workbook_NewSheet(ByVal Sh As Object) 146

Workbook_WindowResize(ByVal Wn As Window) 146

Workbook_WindowActivate(ByVal Wn As Window) 146

Workbook_WindowDeactivate(ByVal Wn As Window) 146

Workbook_AddInInstall() 147

Workbook_AddInUninstall 147

Workbook_SheetActivate(ByVal Sh As Object) 147

Workbook_SheetBeforeDoubleClick (ByVal Sh As Object,

ByVal Target As Range, Cancel As Boolean) 147

Workbook_SheetBeforeRightClick(ByVal Sh As Object,

ByVal Target As Range, Cancel As Boolean) 147

Workbook_SheetCalculate (ByVal Sh As Object) 147

Workbook_SheetChange (ByVal Sh As Object, ByVal Target As Range) 148

Workbook_SheetDeactivate (ByVal Sh As Object) 148

Workbook_SheetFollowHyperlink (ByVal Sh As Object,

ByVal Target As Hyperlink) 148

Workbook_SheetSelectionChange (ByVal Sh As Object, ByVal Target As Range) 148

Worksheet Events 148

Worksheet_Activate() 149

Worksheet_Deactivate() 149

Worksheet_BeforeDoubleClick(ByVal Target As Range,

Cancel As Boolean) 149

Worksheet_BeforeRightClick(ByVal Target As Range,

Cancel As Boolean) 150

Worksheet_Calculate() 150

Worksheet_Change(ByVal Target As Range) 150

Worksheet_SelectionChange(ByVal Target As Range) 152

Worksheet_FollowHyperlink(ByVal Target As Hyperlink) 152

Chart Sheet Events 152

Embedded Charts 153

Chart_Activate() 153

Chart_BeforeDoubleClick(ByVal ElementID As Long,

ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean) 154

Chart_BeforeRightClick(Cancel As Boolean) 154

Chart_Calculate() 154

Chart_Deactivate() 154

Chart_DragOver() 154

Chart_DragPlot() 154

Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long,

ByVal x As Long, ByVal y As Long) 154

Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long,

ByVal x As Long, ByVal y As Long) 155

Chart_MouseUp(ByVal Button As Long, ByVal Shift As Long,

ByVal x As Long, ByVal y As Long) 155

Chart_Resize() 155

Chart_Select(ByVal ElementID As Long, ByVal Arg1 As Long,

ByVal Arg2 As Long) 155

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

Application-Level Events 156

AppEvent_NewWorkbook(ByVal Wb As Workbook) 157

AppEvent_SheetActivate(ByVal Sh As Object) 157

AppEvent_SheetBeforeDoubleClick(ByVal Sh As Object,

ByVal Target As Range, Cancel As Boolean) 158

AppEvent_SheetBeforeRightClick(ByVal Sh As Object,

ByVal Target As Range, Cancel As Boolean) 158

AppEvent_SheetCalculate(ByVal Sh As Object) 158

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

AppEvent_SheetFollowHyperlink(ByVal Sh As Object,

ByVal Target As Hyperlink) 158

AppEvent_SheetSelectionChange(ByVal Sh As Object,

ByVal Target As Range) 158

AppEvent_WindowActivate(ByVal Wb As Workbook,

ByVal Wn As Window) 158

AppEvent_WindowDeactivate(ByVal Wb As Workbook,

ByVal Wn As Window) 159

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

AppEvent_WorkbookActivate(ByVal Wb As Workbook) 159

AppEvent_WorkbookAddinInstall(ByVal Wb As Workbook) 159

AppEvent_WorkbookAddinUninstall(ByVal Wb As Workbook) 159

AppEvent_WorkbookBeforeClose(ByVal Wb As Workbook,

Cancel As Boolean) 159

AppEvent_WorkbookBeforePrint(ByVal Wb As Workbook,

Cancel As Boolean) 160

AppEvent_WorkbookBeforeSave(ByVal Wb As Workbook,

ByVal SaveAsUI As Boolean, Cancel As Boolean) 160

AppEvent_WorkbookNewSheet(ByVal Wb As Workbook,

ByVal Sh As Object) 160

AppEvent_WorkbookOpen(ByVal Wb As Workbook) 160

Next Steps 160

9 UserForms—An Introduction 161

User Interaction Methods 161

InputBox 161

MsgBox 161

Creating a Userform 162

Calling and Hiding a Userform 163

Programming the UserForm 164

Using Basic Form Controls 165

Deciding Whether to Use ListBoxes or ComboBoxes in Your Forms 166

Adding Option Buttons to a UserForm 167

Adding Graphics to a UserForm 168

Using Spinbutton on a Userform 169

Using the Multipage Control to Combine Forms 170

Verifying Field Entry 170

Illegal Window Closing 171

Next Steps 172

0 0

Post a comment