Excel 2003 VBA programmer's reference

1177 Pages·2004·11.818 MB·English

by  Paul Kimmel, et al

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,[phone],
fax[phone].RequeststothePublisherforpermissionshouldbeaddressedtotheLegal
Department,WileyPublishing,Inc.,10475CrosspointBlvd.,Indianapolis,IN46256,[phone],
fax[phone],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
[e-mail]ildingsoftware.
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.