www.sharexxx.net - free books & magazines
Excel® 2007 VBA
Programmer’s Reference
John Green
Stephen Bullen
Rob Bovey
Michael Alexander
Excel® 2007 VBA
Programmer’s Reference
Excel® 2007 VBA
Programmer’s Reference
John Green
Stephen Bullen
Rob Bovey
Michael Alexander
Excel®2007 VBA Programmer’s Reference
Published by
Wiley Publishing, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256
www.wiley.com
Copyright © 2007 by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
ISBN: 978-0-470-04643-2
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any
means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107
or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or autho-
rization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive,
Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed
to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax
(317) 572-4355, or online at http://www.wiley.com/go/permissions.
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REP-
RESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CON-
TENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT
LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED
OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED
HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTAND-
ING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PRO-
FESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT
PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE
LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS
REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMA-
TION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE
ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READ-
ERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR
DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ.
For general information on our other products and services or to obtain technical support, please contact our Cus-
tomer Care Department within the U.S. at (800) 762-2974, outside the U.S. at (317) 572-3993 or fax (317) 572-4002.
Library of Congress Cataloging-in-Publication Data
Excel 2007 VBA programmer’s reference / John Green ... [et al.].
p. cm.
Includes index.
ISBN 978-0-470-04643-2 (paper/website)
1. Microsoft Excel (Computer file) 2. Business—Computer programs. I. Green, John, 1945-
HF5548.4.M523E92988 2007
005.54—dc22
2007004976
Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, Programmer to Programmer, and related trade dress are
registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and
may not be used without written permission. Microsoft and Excel are registered trademarks of Microsoft Corpora-
tion in the United States and/or other countries. All other trademarks are the property of their respective owners.
Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.
Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be
available in electronic books.
About the Authors
John Green lives and works in Sydney, Australia, as an independent computer consultant, specializing in
Excel and Access. He has 35 years of computing experience, a Chemical Engineering degree, and an MBA.
He wrote his first programs in FORTRAN, took a part in the evolution of specialized planning languages
on mainframes and, in the early ‘80s, became interested in spreadsheet systems, including 1-2-3 and Excel.
John established his company, Execuplan Consulting, in 1980, specializing in developing computer-
based planning applications and in training. He has led training seminars for software applications and
operating systems both in Australia and overseas.
John has had regular columns in a number of Australian magazines and has contributed chapters to a num-
ber of books including Excel Expert Solutions and Using Visual Basic for Applications 5. He also co-authored
Professional Excel Development with Stephen Bullen and Rob Bovey.
From 1995 to 2005 he was accorded the status of MVP (Most Valuable Professional) by Microsoft for his
contributions to the CompuServe Excel forum and MS Internet newsgroups.
John Green contributed the Introduction, Chapters 1–11, 13, 15–17, and 19 to this book.
Stephen Bullen lives in Woodford Green, London, England, with his partner Clare, daughter Becky, and
their dogs, Fluffy and Charlie. He has two other daughters, Jane and Katie, from his first marriage.
A graduate of Oxford University, Stephen has an MA in Engineering, Economics, and Management,
providing a unique blend of both business and technical skills. He has been providing Excel consulting
and application development services since 1994, originally as an employee of Price Waterhouse
Management Consultants and later as an independent consultant trading under the names of Business
Modelling Solutions Limited and Office Automation Limited. Stephen now works for Barclays Capital in
London, developing trading systems for complex exotic derivative products.
The Office Automation web site, www.oaltd.co.uk, provides a number of helpful and interesting utili-
ties, examples, tips and techniques to help in your use of Excel and development of Excel applications.
As well as co-authoring previous editions of the Excel VBA Programmer’s Reference, Stephen co-authored
Professional Excel Development.
In addition to his consulting and writing assignments, Stephen actively supports the Excel user community
in Microsoft’s peer-to-peer support newsgroups and the Daily Dose of Excel blog. In recognition of his
knowledge, skills and contributions, Microsoft has awarded him the title of Most Valuable Professional
each year since 1996.
Stephen Bullen contributed Chapters 14, 18, 24–27, and Appendix B to this book.
Rob Bovey is president of Application Professionals, a software development company specializing in
Microsoft Office, Visual Basic, and SQL Server applications. He brings many years’ experience creating
financial, accounting, and executive information systems for corporate users to Application
Professionals. You can visit the Application Professionals web site at www.appspro.com.
Rob developed several add-ins shipped by Microsoft for Microsoft Excel and co-authored the Microsoft
Excel 97 Developers Kit and Professional Excel Development. He earned his Bachelor of Science degree from
The Rochester Institute of Technology and his MBA from the University of North Carolina at Chapel
Hill. He is a Microsoft Certified Systems Engineer (MCSE) and a Microsoft Certified Solution Developer
(MCSD). Microsoft has awarded him the title of Most Valuable Professional each year since 1995.
Rob Bovey contributed Chapters 20–22 to this book.
Michael Alexander is a Microsoft Certified Application Developer (MCAD) with more than 14 years’
experience consulting and developing office solutions. He parlayed his experience with VBA and VB
into a successful consulting practice in the private sector, developing middleware and reporting solu-
tions for a wide variety of industries. He currently lives in Frisco, Texas, where he serves as a Senior
Program Manager for a top technology firm. Michael is the author of several books on Microsoft Access
and Excel, and is the principle behind DataPig Technologies, where he shares Access and Excel knowl-
edge with the Office community.
Michael Alexander contributed Chapters 12 and 23 and Appendices A and C to this book.
Credits
Acquisitions Editor Vice President and Executive Publisher
Katie Mohr Joseph B. Wikert
Development Editor Project Coordinator
Brian Herrmann Jennifer Theriot
Technical Editor Graphics and Production Specialists
Dick Kusleika Carrie A. Foster
Denny Hager
Production Editor Joyce Haughey
William A. Barton Jennifer Mayberry
Barbara Moore
Copy Editor Barry Offringa
Kim Cofer Heather Ryan
Editorial Manager Quality Control Technicians
Mary Beth Wakefield Jessica Kramer
Christine Pingleton
Production Manager
Tim Tate Proofreading and Indexing
Kevin Broccoli
Vice President and Executive Group Publisher Sean Medlock
Richard Swadley
Contents
Acknowledgments xxi
Introduction xxiii
Chapter 1: Primer in Excel VBA 1
Using the Macro Recorder 2
Recording Macros 2
Running Macros 6
The Visual Basic Editor 8
Other Ways to Run Macros 11
User-Defined Functions 17
Creating a UDF 18
What UDFs Cannot Do 21
The Excel Object Model 21
Objects 22
Getting Help 27
Experimenting in the Immediate Window 29
The VBA Language 30
Basic Input and Output 30
Calling Functions and Sub Procedures 35
Parentheses and Argument Lists 37
Variable Declaration 38
Scope and Lifetime of Variables 40
Variable Type 42
Object Variables 45
Making Decisions 47
Looping 50
Arrays 55
Run-Time Error-Handling 59
Summary 62
Chapter 2: The Application Object 63
Globals 63
The Active Properties 64
Display Alerts 65
Screen Updating 66
Contents
Evaluate 66
InputBox 68
StatusBar 70
SendKeys 70
OnTime 71
OnKey 72
Worksheet Functions 73
Caller 74
Summary 75
Chapter 3: Workbooks and Worksheets 77
The Workbooks Collection 77
Getting a Filename from a Path 78
Files in the Same Directory 81
Overwriting an Existing Workbook 81
Saving Changes 82
The Sheets Collection 83
Worksheets 83
Copy and Move 85
Grouping Worksheets 87
The Window Object 89
Synchronizing Worksheets 90
Summary 91
Chapter 4: Using Ranges 93
Activate and Select 93
Range Property 95
Shortcut Range References 96
Ranges on Inactive Worksheets 96
Range Property of a Range Object 97
Cells Property 97
Cells Used in Range 98
Ranges of Inactive Worksheets 99
More on the Cells Property of the Range Object 99
Single-Parameter Range Reference 101
Offset Property 102
Resize Property 103
SpecialCells Method 105
Last Cell 105
Deleting Numbers 107
x
Contents
CurrentRegion Property 108
End Property 110
Referring to Ranges with End 110
Summing a Range 111
Columns and Rows Properties 112
Areas 113
Union and Intersect Methods 115
Empty Cells 115
Transferring Values between Arrays and Ranges 118
Deleting Rows 121
Summary 123
Chapter 5: Using Names 125
Naming Ranges 127
Using the Name Property of the Range Object 128
Special Names 128
Storing Values in Names 129
Storing Arrays 130
Hiding Names 131
Working with Named Ranges 132
Searching for a Name 133
Searching for the Name of a Range 135
Determining which Names Overlap a Range 136
Summary 139
Chapter 6: Data Lists 141
Structuring the Data 141
Sorting a Range 142
Older Excel Versions 144
Creating a Table 144
Sorting a Table 145
AutoFilter 146
AutoFilter Object 147
Filter Object 148
Date Custom Filter 148
Adding Combo Boxes 149
Copying the Visible Rows 153
Finding the Visible Rows 154
Advanced Filter 156
Data Form 158
Summary 159
xi
Contents
Chapter 7: PivotTables 161
Creating a PivotTable Report 162
PivotCaches 165
PivotTables Collection 165
PivotFields 166
CalculatedFields 170
PivotItems 171
Grouping 171
Visible Property 175
CalculatedItems 176
PivotCharts 177
External Data Sources 178
Summary 180
Chapter 8: Charts 181
Chart Sheets 182
The Recorded Macro 184
Adding a Chart Sheet Using VBA Code 184
Embedded Charts 185
Using the Macro Recorder 186
Adding an Embedded Chart Using VBA Code 186
Editing Data Series 187
Defining Chart Series with Arrays 190
Converting a Chart to Use Arrays 193
Determining the Ranges Used in a Chart 194
Chart Labels 195
Summary 196
Chapter 9: Event Procedures 199
Worksheet Events 199
Enable Events 200
Worksheet Calculate 201
Chart Events 202
Before Double Click 202
Workbook Events 205
Save Changes 206
Headers and Footers 207
Summary 208
xii
Contents
Chapter 10: Adding Controls 209
Form and ActiveX Controls 209
ActiveX Controls 210
Scrollbar Control 211
Spin Button Control 211
CheckBox Control 212
Option Button Controls 212
Forms Controls 214
Dynamic ActiveX Controls 216
Controls on Charts 220
Summary 221
Chapter 11: Text Files and File Dialog 223
Opening Text Files 223
Writing to Text Files 224
Reading Text Files 226
Writing to Text Files Using Print 227
Reading Data Strings 229
Flexible Separators and Delimiters 230
FileDialog 233
FileDialogFilters 235
FileDialogSelectedItems 235
Dialog Types 235
Execute Method 235
MultiSelect 236
Summary 238
Chapter 12: Working with XML and the Open XML File Formats 239
The Basics of Using XML Data in Excel 240
XML Fundamentals 240
Consuming XML Data Directly 246
Creating and Managing Your Own XML Maps 249
Using VBA to Program XML Processes 253
Programming XML Maps 253
Leveraging DOM and XPath to Manipulate XML Files 258
Using VBA to Program Open XML Files 265
Programming Open XML Files with VBA 266
Programmatically Zipping an Excel Container 267
Summary 272
xiii
Contents
Chapter 13: UserForms 273
Displaying a UserForm 273
Creating a UserForm 275
Directly Accessing Controls in UserForms 277
Stopping the Close Button 281
Maintaining a Data List 282
Modeless UserForms 288
Progress Indicator 288
Variable UserForm Name 291
Summary 291
Chapter 14: RibbonX 293
Overview 293
Prerequisites 294
Adding the Customizations 294
XML Structure 295
RibbonX and VBA 298
Control Types 299
Basic Controls 299
Container Controls 300
Control Attributes 301
Control Callbacks 303
Managing Control Images 305
Other RibbonX Elements, Attributes, and Callbacks 307
Sharing Controls among Multiple Workbooks 308
Updating Controls at Run Time 309
Hooking Built-In Controls 311
RibbonX in Dictator Applications 312
Customizing the Office Menu 312
Customizing the QAT 313
Controlling Tabs, Tab Sets, and Groups 313
Dynamic Controls 314
dropDown, comboBox, and gallery 315
dynamicMenu 315
CommandBar Extensions for the Ribbon 316
RibbonX Limitations 317
Summary 318
xiv
Contents
Chapter 15: Command Bars 319
Toolbars, Menu Bars, and Popups 320
Excel’s Built-in Command Bars 322
Controls at All Levels 325
FaceIds 328
Creating New Menus 330
The OnAction Macros 332
Passing Parameter Values 333
Deleting a Menu 334
Creating a Toolbar 335
Popup Menus 338
Showing Popup Command Bars 342
Table-Driven Command Bar Creation 344
Summary 354
Chapter 16: Class Modules 355
Creating Your Own Objects 356
Property Procedures 357
Creating Collections 359
Class Module Collection 360
Encapsulation 363
Trapping Application Events 363
Embedded Chart Events 365
A Collection of UserForm Controls 368
Referencing Classes Across Projects 370
Summary 371
Chapter 17: Add-ins 373
Hiding the Code 374
Creating an Add-in 374
Closing Add-ins 375
Code Changes 376
Saving Changes 377
Interface Changes 377
Installing an Add-in 379
AddinInstall Event 381
Removing an Add-in from the Add-ins List 381
Summary 382
xv
Contents
Chapter 18: Automation Add-Ins and COM Add-Ins 383
Automation Add-Ins 383
A Simple Add-In — Sequence 384
Registering Automation Add-Ins with Excel 385
Using Automation Add-Ins 386
Introducing the IDTExtensibility2 Interface 388
COM Add-Ins 394
The IDTExtensibility2 Interface (Continued) 395
Registering a COM Add-In with Excel 395
The COM Add-In Designer 396
Summary 409
Chapter 19: Interacting with Other Office Applications 411
Establishing the Connection 411
Late Binding 412
Early Binding 414
Opening a Document in Word 416
Accessing an Active Word Document 417
Creating a New Word Document 418
Access and ADO 419
Access, Excel, and, Outlook 420
Better than Mail Merge 423
Readable Document Variables 428
Summary 430
Chapter 20: Data Access with ADO 431
An Introduction to Structured Query Language (SQL) 431
The SELECT Statement 432
The INSERT Statement 434
The UPDATE Statement 434
The DELETE Statement 435
An Overview of ADO 436
The Connection Object 437
The Recordset Object 441
The Command Object 445
Using ADO in Microsoft Excel Applications 447
Using ADO with Microsoft Access 448
Using ADO with Microsoft SQL Server 454
Using ADO with Non-Standard Data Sources 463
Summary 468
xvi
Contents
Chapter 21: Managing External Data 469
The External Data User Interface 469
Get External Data 470
Manage Connections 471
The QueryTable and ListObject 472
A QueryTable from a Relational Database 472
A Query Table Associated with a ListObject 475
QueryTables and Parameter Queries 476
QueryTables from Web Queries 479
A QueryTable from a Text File 482
Creating and Using Connection Files 484
The WorkbookConnection Object and the Connections Collection 487
External Data Security Settings 489
Summary 490
Chapter 22: The Trust Center and Document Security 491
The Trust Center 491
Trusted Publishers 492
Trusted Locations 492
Add-ins 494
ActiveX Settings 495
Macro Settings 497
Message Bar 498
External Content 499
Privacy Options 501
Automating Document Inspection 503
The RemoveDocumentInformation Method 503
The DocumentInspectors Collection 505
Summary 506
Chapter 23: Browsing OLAP Data Sources with Excel 507
Analyzing OLAP Data via Pivot Tables 508
Connecting to an OLAP Data Source 508
Browsing the OLAP Data Source 510
Understanding the MDX behind OLAP-based Pivot Tables 512
The Basics of MDX 513
Browsing OLAP Data Sources without Pivot Tables 517
Using ADO to Return Flattened Recordsets 517
Using ADO MD to Get Cube Schema Information 518
Creating an Inventory of Dimensions, Hierarchies, and Levels 519
xvii
Copyright by webtailieu.net