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)

2 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 :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.

3 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. 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.

4 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.

5 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.

6 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. 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.

7 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.

8 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.

9 Membersof various newsgroups and the support staff from Microsoft were instrumen-tal in helping me overcome obstacles. 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.

10 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. Editorial Assistant:Cherie CaseCartoons:Rich Tennant( )Composition ServicesProject Coordinator: Adrienne MartinezLayout and Graphics: Carl Byers, Stephanie D.