VBA - Dummies collection

1 VBAFORDUMmIES 5TH EDITIONby John Paul Mueller01_046500 12/5/06 5:32 PM Page iVBA For Dummies , 5th EditionPublished byWiley Publishing, River StreetHoboken, NJ 2007 by Wiley Publishing, Inc., Indianapolis, IndianaPublished by Wiley Publishing, Inc., Indianapolis, IndianaPublished simultaneously in CanadaNo part of this publication may be reproduced, stored in a retrieval system or transmitted in any form orby any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permit-ted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior writtenpermission of the Publisher, or authorization through payment of the appropriate per-copy fee to theCopyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing,Inc.

2 , 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4355, or online :Wiley, the Wiley Publishing logo, For Dummies , the Dummies Man logo, A Reference for theRest of Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, , and related tradedress are trademarks or registered trademarks of John Wiley & Sons, Inc., and/or its affiliates in the UnitedStates and other countries, and may not be used without written permission. All other trademarks are theproperty of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendormentioned in this OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REP-RESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THECONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUTLIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE.

3 NO WARRANTY MAY BE CRE-ATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CON-TAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THEUNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OROTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF ACOMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THEAUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATIONOR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FUR-THER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THEINFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAYMAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORKMAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN ITIS general information on our other products and services, please contact our Customer CareDepartment within the at 800-762-2974, outside the at 317-572-3993, or fax technical support, please visit also publishes its books in a variety of electronic formats.

4 Some content that appears in print maynot be available in electronic of Congress Control Number: 2006936829 ISBN: 978-0-470-04650-0 Manufactured in the United States of America10 9 8 7 6 5 4 3 2 15O/SS/RS/QW/IN01_046500 12/5/06 5:32 PM Page iiAbout the AuthorJohn Muelleris a freelance author and technical editor. He has writing in hisblood, having produced 71 books and over 300 articles to date. The topicsrange from networking to artificial intelligence and from database manage-ment to heads-down programming. Some of his current books include aWindows power optimization book, a book on .NET security, and books onAmazon Web Services, Google Web Services, and eBay Web Services. Histechnical editing skills have helped more than 50 authors refine the contentof their manuscripts. John has provided technical editing services to bothData Based Advisorand Coast Computemagazines.

5 He has also contributedarticles to magazines like DevSource, InformIT, Informant, DevX, SQL ServerProfessional, Visual C++ Developer, Hardcore Visual Basic, ,Software Test & Performance, and Visual Basic John isn t working at the computer, you can find him in his s an avid woodworker and candle maker. On any given afternoon, you canfind him working at a lathe or putting the finishing touches on a bookcase. Healso likes making glycerin soap and candles, which comes in handy for giftbaskets. You can reach John on the Internet at He isalso setting up a Web site at http:// ~jmueller/; feel freeto look and make suggestions on how he can improve it. Check out John sweekly blog at 12/5/06 5:32 PM Page iii01_046500 12/5/06 5:32 PM Page ivDedicationThis book is dedicated to Uncle Bob on his birthday; thanks for helping meunderstand the need to enjoy some time s AcknowledgmentsThanks to my wife, Rebecca, for working with me to get this book really don t know what I would have done without her help in researchingand compiling some of the information that appears in this book.

6 She also dida fine job of proofreading my rough Mullen deserves thanks for his technical edit of this book. He greatlyadded to the accuracy and depth of the material that you see here. I reallyappreciate the time he devoted to checking my code for accuracy. I alsospent a good deal of time bouncing ideas off Russ as I wrote this book, whichis a valuable aid to any number of people read all or part of this book to help me refine theapproach, test the examples, and generally provide input that every readerwishes they could have. These unpaid volunteers helped in ways too numer-ous to mention here. I especially appreciate the efforts of Eva Beattie, whoread the entire book and selflessly devoted herself to this project. Membersof various newsgroups and the support staff from Microsoft were instrumen-tal in helping me overcome obstacles.

7 A number of other people, includingTom Rider and Chellingi Prasad, helped me in ways too numerous to men-tion. I d love to thank by name each person who wrote me with an idea, butthere are simply too many to , I would like to thank Kyle Looper, Nicole Sholly, Rebecca Whitney, and the rest of the editorial and production staff for their assistance in bring-ing this book to print. It s always nice to work with such a great group of 12/5/06 5:32 PM Page vPublisher s AcknowledgmentsWe re proud of this book; please send us your comments through our online registration formlocated at of the people who helped bring this book to market include the following:Acquisitions, Editorial, and Media DevelopmentProject Editor:Nicole ShollyAcquisitions Editor:Kyle LooperCopy Editor:Rebecca WhitneyTechnical Editor:Russ MullenEditorial Manager:Kevin KirschnerMedia Development Specialists:Angela Denny,Kate Jenkins, Steven Kudirka, Kit MaloneMedia Project Supervisor:Laura MossMedia Development Manager:Laura VanWinkleEditorial Assistant:Amanda FoxworthSr.

8 Editorial Assistant:Cherie CaseCartoons:Rich Tennant( )Composition ServicesProject Coordinator: Adrienne MartinezLayout and Graphics: Carl Byers, Stephanie D. Jumper, Barbara Moore, Barry Offringa, Heather Ryan, Ronald TerryProofreaders: Susan Moritz, Dwight Ramsey,TechbooksIndexer: TechbooksAnniversary Logo Design:Richard PacificoPublishing and Editorial for Technology DummiesRichard Swadley,Vice President and Executive Group PublisherAndy Cummings,Vice President and PublisherMary Bednarek,Executive Acquisitions DirectorMary C. Corder,Editorial DirectorPublishing for Consumer DummiesDiane Graves Steele,Vice President and PublisherJoyce Pepple,Acquisitions DirectorComposition ServicesGerry Fahey,Vice President of Production ServicesDebbie Stailey,Director of Composition Services01_046500 12/5/06 5:32 PM Page viContents at a GlanceIntroduction.

9 1 Part I: An Overview of VBA ..7 Chapter 1: Getting to Know VBA ..9 Chapter 2: Your First VBA Program ..33 Part II: Learning the Ropes ..49 Chapter 3: Writing Structured VBA Programs ..51 Chapter 4: Storing and Modifying Information ..79 Chapter 5: Creating Structured Programs ..111 Chapter 6: Trapping Errors and Squashing Bugs ..133 Chapter 7: Interacting with the III: Expanding Your VBA 8: Object-Oriented Programming ..181 Chapter 9: Working with Arrays and Collections ..205 Chapter 10: Working with Disk Files ..229 Chapter 11: VBA Programming with XML ..239 Part IV: Programming for Applications ..255 Chapter 12: VBA Programming in Office ..257 Chapter 13: VBA Programming in Word ..277 Chapter 14: VBA Programming in Excel ..305 Chapter 15: VBA Programming in Access ..329 Chapter 16: Applications that Work Together ..355 Part V: The Part of Tens.

10 379 Chapter 17: Ten Kinds of VBA 18: Ten Ways to Update Your Old VBA Code Quickly ..389On the WebBonus Chapter 1: VBA Programming in FrontPage ..BC1 Bonus Chapter 2: VBA Programming in Visio ..BC25 Bonus Chapter 3: Ten Really Cool Things You Can Do with ..39702_046500 12/5/06 5:32 PM Page vii02_046500 12/5/06 5:32 PM Page viiiTable of ContentsIntroduction ..1 About This Used in This Book ..2 What You Should Read ..2 What You Don t Have to Read ..3 Foolish Assumptions ..3 How This Book Is I: An Overview of VBA ..4 Part II: Learning the Ropes ..4 Part III: Expanding Your VBA IV: Programming for V: The Part of accompanying Web site ..5 Icons Used in This Book ..6 Where to Go from I: An Overview of VBA ..7 Chapter 1: Getting to Know VBA ..9 Batteries Included VBA Comes with : It s Not Just for Programmers.