Excel 2003 VBA programmer's reference PDF
Preview Excel 2003 VBA programmer's reference
Excel 2003 VBA Programmer’s Reference Excel 2003 VBA Programmer’s Reference Paul Kimmel Stephen Bullen John Green Rob Bovey Robert Rosenberg WileyPublishing,Inc. Excel 2003 VBA Programmer’s Reference Publishedby WileyPublishing,Inc. 10475CrosspointBoulevard Indianapolis,IN46256 www.wiley.com Copyright(cid:2)C 2004byWileyPublishing,Inc.Allrightsreserved. PublishedsimultaneouslyinCanada Nopartofthispublicationmaybereproduced,storedinaretrievalsystem,ortransmittedinanyform orbyanymeans,electronic,mechanical,photocopying,recording,scanning,orotherwise,exceptas permittedunderSection107or108ofthe1976UnitedStatesCopyrightAct,withouteithertheprior writtenpermissionofthePublisher,orauthorizationthroughpaymentoftheappropriateper-copyfee totheCopyrightClearanceCenter,Inc.,222RosewoodDrive,Danvers,MA01923,(978)750-8400, fax(978)646-8700.RequeststothePublisherforpermissionshouldbeaddressedtotheLegal Department,WileyPublishing,Inc.,10475CrosspointBlvd.,Indianapolis,IN46256,(317)572-3447, fax(317)572-4447,E-mail:[email protected]. LIMITOFLIABILITY/DISCLAIMEROFWARRANTY:THEPUBLISHERANDTHEAUTHORMAKENOREP- RESENTATIONSORWARRANTIESWITHRESPECTTOTHEACCURACYORCOMPLETENESSOFTHECON- TENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIM- ITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OREXTENDEDBYSALESORPROMOTIONALMATERIALS.THEADVICEANDSTRATEGIESCONTAINED HEREINMAYNOTBESUITABLEFOREVERYSITUATION.THISWORKISSOLDWITHTHEUNDERSTAND- INGTHATTHEPUBLISHERISNOTENGAGEDINRENDERINGLEGAL,ACCOUNTING,OROTHERPRO- FESSIONALSERVICES.IFPROFESSIONALASSISTANCEISREQUIRED,THESERVICESOFACOMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOT THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERREDTOINTHISWORKASACITATIONAND/ORAPOTENTIALSOURCEOFFURTHERINFORMA- TION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATIONORWEBSITEMAYPROVIDEORRECOMMENDATIONSITMAYMAKE.FURTHER,READ- ERSSHOULDBEAWARETHATINTERNETWEBSITESLISTEDINTHISWORKMAYHAVECHANGEDOR DISAPPEAREDBETWEENWHENTHISWORKWASWRITTENANDWHENITISREAD. ForgeneralinformationonourotherproductsandservicespleasecontactourCustomerCare DepartmentwithintheUnitedStatesat(800)762-2974,outsidetheUnitedStatesat(317)572-3993 orfax(317)572-4002. Trademarks:Wiley,theWileyPublishinglogo,Wrox,theWroxlogo,ProgrammertoProgrammer,and relatedtradedressaretrademarksorregisteredtrademarksofJohnWiley&Sons,Inc.and/orits affiliates.Allothertrademarksarethepropertyoftheirrespectiveowners.WileyPublishing,Inc.,is notassociatedwithanyproductorvendormentionedinthisbook. Wileyalsopublishesitsbooksinavarietyofelectronicformats.Somecontentthatappearsinprintmaynotbe availableinelectronicbooks. LibraryofCongressCataloging-in-PublicationData Excel2003VBAProgrammer’sReference/PaulKimmel...[etal.]. p. cm. Includesindex. eISBN0-7645-7898-7 1.MicrosoftExcel(Computerfile). 2.MicrosoftVisualBASIC. 3.Business—Computerprograms. 4.Electronicspreadsheets. 5.Computersoftware—Development. I.Kimmel,Paul. HF5548.4.M523E92986 2004 005.54—dc22 2004007598 PrintedintheUnitedStatesofAmerica 10 9 8 7 6 5 4 3 2 1 About the Authors Paul Kimmel PaulKimmelfoundedSoftwareConceptions,Incin1990andhasbeendesigningandbuildingsoftware andwritingcomputerbookseversince.PaulKimmelistheauthorofseveralbooksonVBA,VB,VB.NET, C#,Delphi,andC++.Paulisalsothebi-monthlycolumnistforwww.codeguru.com’sVBTodaycolumn andafrequentcontributortoperiodicalsandonlinepublications,includingwwww.InformiT.com.You maycontacthimatpkimmel@softconcepts.comforhelpdesigningandbuildingsoftware. Stephen Bullen StephenBullenisbasedinCarlow,IrelandandinLondon,England.Hehasbeenrunninghisown company,BusinessModellingSolutionsLtdsince1997,specializinginExceldevelopmentandconsulting andhasworkedforsomeoftheworldslargestcompanies.TheBMSwebsite,www.BMSLtd.co.uk, containsalargenumberofexamplesofhiswork,includingtoolsandutilitiestoextendExcel’s functionalityandmanyexamplesofExceldevelopmenttechniques. StephendevotesalotofhissparetimetohelpingotherExcelusersbyansweringquestionsinthe CompuServeExcelforumandMicrosoft’sInternetNewsgroups.Inrecognitionofhiscontributionsand knowledge,Microsofthasawardedhimthestatusof’MostValuableProfessional’everyyearsince1996. StephenwrotemostofthelaterchaptersintheExcel2000andExcel2002VBA. ProgrammersReferences,whichhavebeencarriedforwardtothisbookandupdatedbyPaulKimmel whereappropriate.Stephenhasnotdirectlycontributedtothisedition. John Green JohnGreen,SydneyAustralia,isanindependentconsultantspecializinginExcelandAccess.With 30yearsofcomputingexperience,aChemicalEngineeringdegreeandanMBA,hedrawsfromadiverse background.Hehasledtrainingcoursesforsoftwareapplicationsandoperatingsystemsbothin Australiaandoverseas.Microsofthasawardedhimthestatusof’MostValuableProfessional’everyyear since1995. JohnwastheprincipalauthoroftheExcel2000andExcel2002VBAProgrammersReferences,which havebeencarriedforwardtothisbookandupdatedbyPaulKimmelwhereappropriate.Johnhasnot directlycontributedtothisedition. Rob Bovey RobBoveyisasoftwaredeveloperspecializinginMicrosoftOffice,VisualBasic,andSQLServer applications.HeisfounderandpresidentofthecustomapplicationdevelopmentfirmApplication Professionals.RobdevelopedseveralAddinsshippedbyMicrosoftforExcel.Healsoco-authoredthe MicrosoftExcel97Developer’sKit.MicrosofthasawardedRobthestatusof’MostValuableProfessional’ everyyearsince1995.RobauthoredthechapteronDataAccesswithADOfortheExcel2002VBA Programmer’sReference.Hehasnotdirectlycontributedtothisedition. Robert Rosenberg RobertRosenbergrunshisownconsultingbusiness,whichspecializesinprovidingcustomsolutionsand advancedtraininginMicrosoftOffice.Hisclientsincludefortunefivehundredcompaniesinthe entertainment,financial,andlegalfields.AsaMicrosoftValuableProfessionalinExcel,healso continuallyoffersadvancedonlinesupportinExcelonbehalfofMicrosofttousersoftheirInternet newsgroups.RobertwasresponsibleforupdatingthecontentfortheExcelandOfficeIndexesforthe2002 versionofthisbook.ThisincludedupdatingexamplecodeandlistingsforexistingVBAobjectsaswellas listingnewobjectdescriptions,theirmethods,propertiesand/orargumentsalongwithcodeexamples. Brian Patterson (Contributor) BrianPatterson(Contributor)currentlyworksforIllinoisMutualLifeasaSoftwareDevelopment CoordinatorwhereheisgenerallyworkingwithC#inWinFormsortheCorporateInternetsite.Brianhas beenwritingforvariouspublicationssince1994andhasco-writtenseveral.NETrelatedbooksincluding “MigratingtoVisualBasic.nET”and“.NETEnterpriseDevelopmentwithVB.NET.”Youcangenerally findhimpostingintheMSDNNewsgroupsorhangingoutwithhislovelywifeand3children.Youcan [email protected]. Credits Vice President and Executive Group Publisher Senior Production Editor RichardSwadley FredBernardi Vice President and Executive Publisher Development Editor BobIpsen AdaobiObiTulton Vice President and Publisher Production Editor JosephB.Wikert PamelaHanley Executive Editorial Director Media Development Specialist MaryBednarek Permissions Editor Acquisitions Editor KatieMohr Text Design & Composition WileyCompositionServices Editorial Manager KathrynA.Malm Acknowledgments Paul Kimmel IwouldliketoacknowledgemygoodfriendandeditorSharonCox,KatieMohr,andAdaobi ObiTultonatWiley.Withoutthem(andDavidFugate,myagentatWaterside)andthebook’s originalauthorsIwouldn’thavehadachancetoworkonthisproject.Ithasbeenapleasure workingwiththeprofessionalsatWiley. WhileworkingonthisprojectIhadthegoodfortuneofworkingonaC#projectatPitneyBowes withsomeintelligentfolks.EdwardRongaisagreatmanager;hecanworkwonderswith engineersthatcansometimesbeabitfussy.ItwasmypleasuretoworkatPitneywithLeonard Bertelli,JayFusco,EnzoMaini,PeterGomis,KipStroble,CarlDalzell,DebraAlberti,Sanjay Gulati,andmycellmateCharlesHaley(IplayedNorahJonesforfourmonthsandCharlesnever complained.)AsalwaysImakeeveryefforttoleavethingsbetterthanIfoundthembutalways seemtobethebeneficiaryofthesenew,greatrelationships. HappyNewYeartonewandoldfriendsandfamily.EricCotterhasasharpmindandmore enthusiasmthananyoneIknow,andRobertGoliebissteadfastandsomeoneIamluckytocall friend.GreetingstomyhostsatPorky’sinShelton,Connecticutforthemostexcellentchicken wingsandadultbeverages. Mygreatestblessingismyfamily.MywifeLoriisarockandmygreatestcheerleader,andIam blessedtohavefourhealthyandbeautifulchildren(Trevor,Douglas,Alex,andNoah).My greatestwishisthateveryonecanknowtheblessingsofaloving,healthyfamily. Stephen Bullen IwouldliketostartbythankingallthosewhopurchasedacopyoftheExcel2000VBA Programmer’sReferenceandsente-mailsofcongratulationsandsuggestionsforthisupdate.It isyoursupportthatconvincedustocomprehensivelyupdatethebookforExcel2002.I’dliketo thankJohnGreenforagreeingtoco-authortheupdate,RobBoveyforcontributingtheADO chapter,andRobertRosenbergfortakingovertheReferencesections.Asalways,thepeopleat WroxPressandthebook’stechnicalreviewersworkedwonders,anditistheircontributionthat hasmadethistheexcellentbookthatIhopeyou’llagreeitis. Onapersonalnote,I’dliketodedicatemychapterstotheeyespecialists,doctors,andnursesat theTempleStreetChildren’sHospitalinDublinfordealingwithJane’stumoursowell,andto everyoneintheFordProPrimateamfortheirfriendshipandsupportoverthepastyear. Rob Bovey IwouldliketothankmywifeMichelleforputtingupwithmyinsatiablecomputerhabitandmy dogHarleyforkeepingmyfeetwarmwhileIwork.
The list of books you might like
