Excel 2002 Power Programming With Vba By John Wiley & Sons.pdf

This preview shows page 1 out of 986 pages.

Unformatted text preview: W D John Walkenbach is a With this unique guide, renowned spreadsheet authority John Walkenbach invites you to harness the full potential of Excel—and join the elite group of users and developers who truly understand what the product is capable of. Offering plenty application development and VBA programming—and then gives you a lucid, • Build user-friendly toolbars, menus, and help systems ,!7IA7G4-fehj h!:p;o;t;T;T CD-ROM included TM O F E S S I O N *85 5 -AGBGEe E Shelving Category Programming/Spreadsheets ISBN 0-7645-4799-2 R Reader Level Intermediate to Advanced A • Get advice on compatibility issues and answers to frequently asked Excel programming questions W • Manipulate files and Visual Basic components and understand class modules D R • Incorporate event-handling and interactions with other applications N • Develop applications that maximize the power of PivotTables and charts I P • Create stellar UserForms as well as custom dialog box alternatives M $49.99 USA $74.99 CANADA £39.99 UK incl. VAT • Work with VBA sub procedures and function procedures Visit us at mandtbooks.com L System Requirements: Pentium PC with Windows 98 or greater; 32 MB RAM; Microsoft Excel 2002. See About the CD appendix for details and complete system requirements. • Take Excel to the next level with formula tricks and techniques Power Utility Pak trial and more on CD-ROM A • A trial version of the author’s award-winning Power Utility Pak 2000 • A demo of the author’s Sound-Proof 2000 • Over 175 example Excel workbooks from the book Your Road Map to Excel Application Development N that leverage the power of Excel 2002. BONUS CD-ROM INCLUDES O development issues. It’s all you need to create user-oriented custom applications — Microsoft OfficePRO magazine on the previous edition I wide-ranging seminar on advanced programming techniques and application “An outstanding reference. . . . If you use Excel, you need this book.” S of detailed, well-thought-out examples, he walks you through the basics of Excel Author of Excel 2002 Bible S A Develop Powerful, User-Oriented Excel 2002 Applications John Walkenbach E principal of JWalk and Associates, Inc., a consulting firm that specializes in spreadsheet application development, and is the author of more than two dozen books, including, most recently, Excel 2002 Bible and Excel 2002 Formulas. He maintains “The Spreadsheet Page,” a popular Web resource at . Walkenbach F N Excel 2002 Power Programming with VBA Excel 2002 Power Programming with VBA O I Page 1 R M 3:06 PM P L 5/31/01 Excel 2002 Power Programming with VBA A R E TM 4799-2 Cover_rb3.qxp 4799-2 FM.F 6/11/01 1:12 PM Page i Excel 2002 Power Programming with VBA 4799-2 FM.F 6/11/01 1:12 PM Page ii 4799-2 FM.F 6/11/01 1:12 PM Page iii Excel 2002 Power Programming with VBA John Walkenbach M&T Books An imprint of Hungry Minds, Inc. New York, NY ✦ Cleveland, OH ✦ Indianapolis, IN 4799-2 FM.F 6/11/01 1:12 PM Page iv Excel 2002 Power Programming with VBA Published by M&T Books An imprint of Hungry Minds, Inc. 909 Third Avenue New York, NY 10022 Copyright © 2001 Hungry Minds, Inc. All rights reserved. No part of this book, including interior design, cover design, and icons, may be reproduced or transmitted in any form, by any means (electronic, photocopying, recording, or otherwise) without the prior written permission of the publisher. Library of Congress Control Number 2001089315 ISBN: 0-7645-4799-2 Printed in the United States of America 10 9 8 7 6 5 4 3 2 1 1O/SY/QW/QR/IN Distributed in the United States by Hungry Minds, Inc. Distributed by CDG Books Canada Inc. for Canada; by Transworld Publishers Limited in the United Kingdom; by IDG Norge Books for Norway; by IDG Sweden Books for Sweden; by IDG Books Australia Publishing Corporation Pty. Ltd. for Australia and New Zealand; by TransQuest Publishers Pte Ltd. for Singapore, Malaysia, Thailand, Indonesia, and Hong Kong; by Gotop Information Inc. for Taiwan; by ICG Muse, Inc. for Japan; by Intersoft for South Africa; by Eyrolles for France; by International Thomson Publishing for Germany, Austria, and Switzerland; by Distribuidora Cuspide for Argentina; by LR International for Brazil; by Galileo Libros for Chile; by Ediciones ZETA S.C.R. Ltda. for Peru; by WS Computer Publishing Corporation, Inc., for the Philippines; by Contemporanea de Ediciones for Venezuela; by Express Computer Distributors for the Caribbean and West Indies; by Micronesia Media Distributor, Inc. for Micronesia; by Chips Computadoras S.A. de C.V. for Mexico; by Editorial Norma de Panama S.A. for Panama; by American Bookshops for Finland. For general information on Hungry Minds’ products and services please contact our Customer Care department within the U.S. at 800-762-2974, outside the U.S. at 317-572-3993 or fax 317-572-4002. For sales inquiries and reseller information, including discounts, premium and bulk quantity sales, and foreign-language translations, please contact our Customer Care department at 800-434-3422, fax 317-572-4002 or write to Hungry Minds, Inc., Attn: Customer Care Department, 10475 Crosspoint Boulevard, Indianapolis, IN 46256. For information on licensing foreign or domestic rights, please contact our Sub-Rights Customer Care department at 212-884-5000. For information on using Hungry Minds’ products and services in the classroom or for ordering examination copies, please contact our Educational Sales department at 800-434-2086 or fax 317-572-4005. For press review copies, author interviews, or other publicity information, please contact our Public Relations department at 317-572-3168 or fax 317-572-4168. For authorization to photocopy items for corporate, personal, or educational use, please contact Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, or fax 978-750-4470. LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND AUTHOR HAVE USED THEIR BEST EFFORTS IN PREPARING THIS BOOK. THE PUBLISHER AND AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS BOOK AND SPECIFICALLY DISCLAIM ANY IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. THERE ARE NO WARRANTIES WHICH EXTEND BEYOND THE DESCRIPTIONS CONTAINED IN THIS PARAGRAPH. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES REPRESENTATIVES OR WRITTEN SALES MATERIALS. THE ACCURACY AND COMPLETENESS OF THE INFORMATION PROVIDED HEREIN AND THE OPINIONS STATED HEREIN ARE NOT GUARANTEED OR WARRANTED TO PRODUCE ANY PARTICULAR RESULTS, AND THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY INDIVIDUAL. NEITHER THE PUBLISHER NOR AUTHOR SHALL BE LIABLE FOR ANY LOSS OF PROFIT OR ANY OTHER COMMERCIAL DAMAGES, INCLUDING BUT NOT LIMITED TO SPECIAL, INCIDENTAL, CONSEQUENTIAL, OR OTHER DAMAGES. FULFILLMENT OF EACH COUPON OFFER IS THE SOLE RESPONSIBILITY OF THE OFFEROR. Trademarks: Professional Mindware is a trademark or registered trademark of Hungry Minds. Microsoft is a registered trademark or trademark of Microsoft Corporation. All other trademarks are property of their respective owners. Hungry Minds, Inc. is not associated with any product or vendor mentioned in this book. is a trademark of is a trademark of Hungry Minds, Inc. Hungry Minds, Inc. 4799-2 FM.F 6/11/01 1:12 PM Page v About the Author John Walkenbach is a leading authority on spreadsheet software and is principal of JWalk and Associates Inc., a small, San Diego-based consulting firm that specializes in spreadsheet application development. John is the author of more than two dozen spreadsheet books and has written more than 300 articles and reviews for a variety of publications, including PC World, InfoWorld, Windows, and PC/Computing. He also maintains The Spreadsheet Page, a popular Internet Web site ( ), and is the developer of Power Utility Pak, an award-winning add-in for Microsoft Excel. John graduated from the University of Missouri and earned a master’s and a Ph.D. from the University of Montana. In addition to computers and spreadsheet software, John’s other interests include guitar, music, novels, digital photography, and puttering around in the garden. 4799-2 FM.F 6/11/01 1:12 PM Page vi Credits Acquisitions Editor Greg Croy Project Editor Susan Christophersen Quality Control Technicians Andy Hollandbeck Carl Pierce Dwight Ramsey Charles Spencer Technical Editor Bill Manville Senior Permissions Editor Carmen Krikorian Copy Editor Jennifer Mario Media Development Specialist Travis Silvers Editorial Manager Kyle Looper Media Development Coordinator Marisa Pearman Project Coordinator Dale White Proofreading and Indexing TECHBOOKS Production Services Graphics and Production Specialists Amy Adrian Joyce Haughey LeAndra Johnson Betty Schulte Brian Torwelle Stephanie D. Jumper Cover Image © Noma/Images.com 4799-2 FM.F 6/11/01 1:12 PM Page vii This one’s for Michelle. 4799-2 FM.F 6/11/01 1:12 PM Page viii 4799-2 FM.F 6/11/01 1:12 PM Page ix Preface W elcome to Excel 2002 Power Programming with VBA. If your job involves developing spreadsheets that others will use — or if you simply want to get the most out of Excel — you’ve come to the right place. Why I Wrote This Book Quite a few advanced Excel books are available, but this book is still the only one that deals with spreadsheet application development from a larger perspective. VBA is just one component (albeit a fairly large component) of application development. Excel is an extremely deep software product: It has many interesting features that lurk in the background, unbeknownst to the typical user. And you can use some of the well-known features in novel ways. Millions of people throughout the world use Excel. I monitor the spreadsheet-related newsgroups on the Internet, and it’s very clear to me that people need (and want) help in the areas that this book covers. My guess is that only five percent of Excel users really understand what the product is capable of. In this book, I attempt to nudge you into that elite company. Are you up to it? What You Need to Know This is not a book for beginning Excel users. If you have no experience with Excel, I recommend that you read either of the following books: ✦ Excel 2002 for Windows For Dummies, by Greg Harvey, is written for users who want to know just enough to get by, and want to be entertained in the process. ✦ Excel 2002 Bible (by yours truly) provides comprehensive coverage of all the features of Excel. It is meant for users of all levels. To get the most out of this book, you should be a relatively experienced Excel user. I didn’t spend much time writing basic how-to information. In fact, I assume that you know the following: ✦ How to create workbooks, insert sheets, save files, and so on ✦ How to navigate through a workbook ✦ How to use the menus and shortcut menus 4799-2 FM.F x 6/11/01 1:12 PM Page x Preface ✦ How to manage Excel’s toolbars ✦ How to enter formulas ✦ How to use Excel’s worksheet functions ✦ How to name cells and ranges ✦ How to use basic Windows features, such as file management techniques and the clipboard If you don’t know how to perform the preceding tasks, you may find some of this material over your head, so consider yourself warned. If you’re an experienced spreadsheet user who hasn’t used Excel 2002, Chapter 2 presents a short overview of what this product offers. What You Need to Have To make the best use of this book, you need a copy of Excel. Although the book was written with Excel 2002 in mind, most of the material also applies to Excel 2000 and Excel 97. If you use an earlier version of Excel, you’re reading the wrong book. Most of the material in this book also applies to Excel for Macintosh. However, I did no compatibility testing with the Mac version, so you’re on your own. Any computer system that can run Windows will suffice, but you’ll be much better off with a fast Pentium-based machine with plenty of memory. Excel is a large program, and using it on a slower system or a system with minimal memory can be extremely frustrating. I recommend using a high-resolution video driver (800 × 600 is okay, 1024 × 768 is excellent, and 1600 × 1024 is sheer heaven). A standard VGA resolution will do in a pinch, but it just doesn’t let you see enough on-screen. To make use of the examples on the companion CD, you will also need a CD-ROM drive. Conventions in This Book Take a minute to skim this section and learn some of the typographic conventions used throughout this book. 4799-2 FM.F 6/11/01 1:12 PM Page xi Preface Keyboard conventions You need to use the keyboard to enter data. In addition, you can work with menus and dialog boxes directly from the keyboard — a method you may find easier if your hands are already positioned over the keys. Input Input that you type from the keyboard appears in boldface — for example, enter =SUM(B2: B50) into cell B51. More lengthy input usually appears on a separate line in a monospace font. For example, I may instruct you to enter the following formula: =VLOOKUP(STOCKNUMBER,PRICELIST,2) VBA code This book contains many snippets of VBA code, as well as complete procedure listings. Each listing appears in a monospace font; each line of code occupies a separate line. (I copied these listings directly from the VBA module and pasted them into my word processor.) To make the code easier to read, I often use one or more tabs to create indentations. Indentation is optional, but it does help to delineate statements that go together. If a line of code doesn’t fit on a single line in this book, I use the standard VBA line continuation sequence: At the end of a line, a space followed by an underscore character indicates that the line of code extends to the next line. For example, the following two lines are a single line of code: If Right(ActiveCell, 1) = “!” Then ActiveCell _ = Left(ActiveCell, Len(ActiveCell) - 1) You can enter this code either on two lines, exactly as shown, or on a single line without the underscore character. Functions, filenames, and named ranges Excel’s worksheet functions appear in uppercase monospace font, like so: “Enter a SUM formula in cell C20.” VBA procedure names, properties, methods, and objects appear in monospace font: “Execute the GetTotals procedure.” I often use mixed upper- and lowercase to make these names easier to read. Mouse conventions If you’re reading this book, you’re well versed in mouse usage. The mouse terminology I use is all standard fare: pointing, clicking, right-clicking, dragging, and so on. xi 4799-2 FM.F xii 6/11/01 1:12 PM Page xii Preface What the Icons Mean Throughout the book, I’ve used icons in the left margin to call your attention to points that are particularly important. I use this icon to indicate that the material discussed is new to Excel 2002. If you’re developing an application that will be used for earlier versions of Excel, pay particular attention to these icons. New Feature Note I use Note icons to tell you that something is important — perhaps a concept that may help you master the task at hand or something fundamental for understanding subsequent material. Tip Tip icons indicate a more efficient way of doing something or a technique that may not be obvious. On the CD-ROM Caution CrossReference These icons indicate that an example file is on the companion CD-ROM (see “About the Companion CD-ROM,” later in the introduction). This CD holds many of the examples that I cover in the book, as well as a trial copy of my popular Power Utility Pak software. I use Caution icons when the operation that I’m describing can cause problems if you’re not careful. I use the Cross Reference icon to refer you to other chapters that have more to say on a subject. How This Book Is Organized The chapters of this book are grouped into seven main parts. In addition, I’ve included a few appendixes that provide supplemental information. Part I: Some Essential Background In this part, I set the stage for the rest of the book. Chapter 1 presents a brief history of spreadsheets so that you can see how Excel fits into the big picture. In Chapter 2, I offer a conceptual overview of Excel 2002 — quite useful for experienced spreadsheet users who are switching to Excel. In Chapter 3, I cover the essentials of formulas, including some clever techniques that may be new to you. Chapter 4 covers the ins and outs of the various files used and generated by Excel. 4799-2 FM.F 6/11/01 1:12 PM Page xiii Preface Part II: Excel Application Development This part consists of just two chapters. In Chapter 5, I broadly discuss the concept of a spreadsheet application. Chapter 6 goes into more detail and covers the steps typically involved in a spreadsheet application development project. Part III: Understanding Visual Basic for Applications Chapters 7 through 11 make up Part III, and these chapters include everything you need to know to learn VBA. In this part, I introduce you to VBA, provide programming fundamentals, and detail how to develop VBA subroutines and functions. Chapter 11 contains tons of useful VBA examples. Part IV: Working with UserForms The four chapters in this part cover custom dialog boxes (also known as UserForms). Chapter 12 presents some built-in alternatives to creating custom UserForms. Chapter 13 provides an introduction to UserForms and the various controls you can use. Chapters 14 and 15 present many examples of custom dialog boxes, ranging from basic to advanced. Part V: Advanced Programming Techniques Part V covers additional techniques that are often considered advanced. The first three chapters discuss how to develop utilities and how to use VBA to work with pivot tables and charts. Chapter 19 covers the topic of event-handling, which enables you to execute procedures automatically when certain events occur. Chapter 20 discusses various techniques that you can use to interact with other applications (such as Word). Chapter 21 concludes Part V with an in-depth discussion of creating add-ins. Part VI: Developing Applications The chapters in Part VI deal with important elements of creating user-oriented applications. Chapters 22 and 23 provide information on creating custom toolbars and menus. Chapter 24 presents several different ways to provide online help for your applications. In Chapter 25, I present some basic information about developing user-oriented applications, and I describe such an application in detail. Part VII: Other Topics The five chapters in Part VII cover additional topics that you may find helpful. Chapter 26 presents information regarding compatibility. In Chapter 27, I discuss various ways to use VBA to work with files. In Chapter 28, I explain how to use VBA xiii 4799-2 FM.F xiv 6/11/01 1:12 PM Page xiv Preface to manipulate Visual Basic components such as UserForms and modules. Chapter 29 covers the topic of class modules. I finish the part with a useful chapter that answers many common questions about Excel programming. Appendixes Five appendixes round out the book. Appendix A contains useful information about Excel resources online. Appendix B is a reference guide to all of VBA’s keywords (statements and functions). I explain VBA error codes in Appendix C, and Appendix D is a handy ANSI code reference chart. The final appendix describes the files available on the companion CD-ROM. About the Companion CD-ROM The inside back cover of this book contains a CD-ROM that holds many useful examples that I discuss in the text. When I write about computer-related material, I emphasize learning by example. I know that I learn more from a well-thought-out example than from reading a dozen pages in a book. I assume that this is true for many other people. Consequently, I spent more time developing the examples on the CD-ROM than I did writing chapters. The files on the companion CD-ROM are not compressed, so you can access them directly from the CD. CrossReference Note Refer to Appendix E for a description of each file on the CD-ROM. All CD-RO...
View Full Document

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture