Excel VBA Speed And Efficiency - SOA.org

This article lists my top rules for speeding up VBA. It is easy to lapse into bad programming habits when working with small macros, but with large macros and ... InThisIssue LetterFromTheChair LetterFromTheEditors SolvencyII–HowSASCanEaseYourImplementationWorries WhyIsHealthCareDataSoChallenging? AppsForActuaries ExcelVBASpeedAndEfficiency RCorner:TheCUTFunction TheMysteryOfTheSQLCursor AniPadInYourFuture? ArticlesNeededForCompActNewsletter QuickLinks TechnologyLearningCenter TechnologySectionWebsite DataAnalyticsResources SectionLeadership BethBernardi,SOAStaffPartner JaneLesch,SOASectionSpecialist SamPhillips,StaffEditor ExcelVBASpeedAndEfficiency byKevinRoper AstheproudownerofseverallargeVBAmacros,Ihavespentaconsiderableamountoftimelookingforwaystomakemacrosrunfaster.ThisarticlelistsmytoprulesforspeedingupVBA.Itiseasytolapseintobadprogramminghabitswhenworkingwithsmallmacros,butwithlargemacrosandmacrosthatrunalongtimeitiscriticaltouseefficientcoding.ThisarticleisprimarilyfocusedonExcelVBAmacros,howevermanyoftheserulesapplytoMicrosoftAccessVBAmacrosaswell. Thefirstfiverulesgenerallyhavethelargestimpactonmacroperformance.Rulessixthrough11haveamarginalimpact.Pleasenotethatmyestimatesoftimesavingsbelowmayvarysignificantlyforyourspecificapplication.TheanalysisusedExcel2007. Rule#1.Turnoffautomaticspreadsheetcalculation Thisruleiswellknown,butitisthemostimportantrule.Whenanewvalueisenteredintoaworksheetcell,Excelwillrecalculateallthecellsthatrefertoit.Ifthemacroiswritingvaluesintotheworksheet,VBAwillneedtowaituntiltheworksheetisdonerecalculatingeachentrybeforeitcanresume.Theimpactofleavingautomaticcalculationturnedoncanbedramatic.Ihighlyrecommendturningoffautomaticcalculationusingthefollowingcommandatthebeginningofthemacro. Application.Calculation=xlCalculationManual Ifyouneedtorecalculatespreadsheetvalueswhilethemacroisrunningyoucanuseanyofthefollowingcommands.Thefirstcommandrecalculatestheentireworkbook.Thesecondcommandonlyrecalculatesaspecificsheet.ThethirdcommandonlyrecalculatesaspecificRange. CalculateWorksheets("sheet1").CalculateRange("A1:C5").Calculate Whenthemacroisdone,automaticcalculationneedstobeturnedbackonusingthefollowingcommand.Ifthemacroendsprematurelybeforethiscommandisprocessed,youwillneedtomanuallyresetcalculationtoautomaticinEXCEL. Application.Calculation=xlCalculationAutomatic. Rule#2.Turnoffscreenupdates EverytimeVBAwritesdatatotheworksheetitrefreshesthescreenimagethatyousee.Refreshingtheimageisaconsiderabledragonperformance.Thefollowingcommandturnsoffscreenupdates. Application.ScreenUpdating=FALSE Attheendofthemacrousethefollowingcommandtoturnscreenupdatesbackon. Application.ScreenUpdating=TRUE Rule#3.MinimizetrafficbetweenVBAandtheworksheet Oncethemacrobeginsitisimportanttoavoidunnecessaryreferencestotheworksheet.Grabbingdatafromthespreadsheetisadragonperformance.Avoidreadingorwritingworksheetdatawithinloopswheneverpossible.Itismuchfastertoreadthedataonceandsaveitintomemorythantorereaditeachtime. Inthisexample,themacrowillneedtograbthenamedRange"issue_age"fromtheworksheetrepeatedly.Thisisacommonmistake.VBAismuchfasterwhenitdoesn'tneedtostopandinteractwiththeworksheet. ForDuration=1To100 Attained_Age=Range("Issue_Age")+DurationNextDuration InthefollowingcodethevariableIssue_AgeisreadinonlyoncefromtheworksheetandtrafficbetweenVBAandExcelisminimized.Thecodebelowismorethan100timesfasterthanthecodeabove! Issue_Age=Range("Issue_Age")ForDuration=1to100 Attained_Age=Issue_Age+DurationNextDuration ItisalsomoreefficienttoperformallnumericalcalculationsinVBA.Itisfrequentlytemptingtoleaveformulasinthespreadsheetandcallthemfromthemacro.But,ifspeedisimportant,putalltheformulasinthemacro.Thisminimizestrafficanddoesn'trequirespreadsheetrecalculation. AsageneralruleusecommandsWorkSheets,Range,CellsandApplicationasefficientlyaspossibleoutsideofloops. Rule#4.Readandwriteblocksofdatainasingleoperation ThisruleisacontinuationofRule#3.ThisisanotherwaytominimizetrafficbetweenVBAandExcel.Wheneverpossiblereadandwritedatainchunks.Thereareseveralmethodstoaccomplishthis.Hereisanexampleofreadinginalargeblockofdata(2,600cells)intoanarray.Thisexampleisroughly50timesfasterthanreadingineachcellindividuallyinaloop. DimmyArray()AsVariant´notethatthismustbeavariantmyArray=Worksheets("Sheet1").Range("A1:Z100").value Likewise,hereareexamplesofwritingthearraybackintotheworksheet.Allareroughly40timesfasterthanwritingeachofthe2,600cellsindividuallywithinaloop. Method#1 Worksheets("Sheet1").Range("A1:Z100").value=myArray Method#2 WithWorksheets("Sheet1").Range("A1:Z100").Value=myArrayEndWith Method#3 DimtheRangeAsRangeSettheRange=Range("A1:Z100")theRange.value=myArray Rule#5.AvoidusingcertainExcelworksheetfunctions Thisrulewassurprisingtome.IhadnaivelyassumedthatcommonworksheetfunctionswouldbeefficientlyprocessedbyVBA.Thisisclearlynotthecase.Forexample,mostVBAusersareprobablyawarethatVBAdoesnothaveaMax()orMin()function.Exceldoeshavethesefunctions.ItiscommontousethefollowingcodethatusestheExcelversionofMax(): variable1=Application.Max(Value1,Value2) IfoundanopensourceversionofaVBAMax()functionontheInternet.Itwas10timesfasterthantheExcelbasedcounterpartabove.However,thecodebelowisover80timesfaster!Iconcedethatthefunctionbelowonlyworkswithtwoargumentsanddoesnotsupportarrays,buttheimprovementinspeedissubstantial. FunctionMax2(Value1,Value2)IfValue1>Value2ThenMax2=Value1ElseMax2=Value2EndIfEndFunction Isuggestcautionwhenusingworksheetfunctionsinlarge,timeconsumingmacros.Youshouldevaluatetheimpactofrewritingthefunction.Notethatanycommandthatstartswith"Application."or"WorksheetFunction."isreferringtoanExcelfunction.Ican'tsaythatall"Application."functionsareslow.But,IhavewrittenordownloadedversionsofMin(),Max(),Average(),Match(),NormSInv()andStDev()thataremuchfasterthantheExcelversions. Rule#6.AvoidusingVariantsinformulas DonotdeclareanumericalvariableasVariantunlessnecessary.Notethatifyouchoosenottouse"OptionExplicit"atthebeginningofthemacroanyundefinedvariablewillbeaVariant.Variantsareveryflexiblebecausetheycanbenumericalortext,buttheyareslowtoprocessinaformula.Theimpactonefficiencyisnotlarge,buteverylittlebithelps.Notethatthisrulealsoappliestoanyfunctionsyouwrite.Basedonmyteststhevariabletypesfromfastesttoslowestinmathematicalequationsare:Constant,Single,Double,Long,Integer,Variant. Rule#7.AvoidevaluatingStrings Strings(text)areslowtoevaluate.AvoidevaluatingStringsincodelikethis: SelectCaseGenderCase"Male"(insertcodehere)...Case"Female"(insertcodehere)...Case"Unisex"(insertcodehere)...EndSelect Enumerationassignsaconstantnumericalvaluetoavariable.VBAcanprocessenumeratedvaluesquicklywhilemaintainingreadablecode.Enumerationcanassigndefaultnumericalvaluesorspecificvaluescanbeassigned. PublicEnumenumGenderMale=0Female=1Unisex=2EndEnumDimGenderasenumGenderSelectCaseGenderCaseMale(insertcodehere)...CaseFemale(insertcodehere)...CaseUnisex(insertcodehere)...EndSelect BooleanoperatorsaresimplyTRUEorFALSEswitchesthatprocessreallyquickly.IntheexamplebelowbMale,bFemaleandbUnisexareBooleanvariables.TheBooleancodeisroughly10timesfasterthanusingStrings. IfbMaleThen(insertcodehere)...ElseIfbFemaleThen(insertcodehere)...ElseIfbUnisexThen(insertcodehere)...EndIf Rule#8.Don'tselectspecificworksheetsunlessnecessary YougenerallydonotneedtousetheSelectcommandtoreadorwritetoaworksheet.Itisabout30timesfasternottoSelectaworksheet. Avoidthis:Worksheets("sheet1").SelectAmount1=Cells(1,1)Dothisinstead:Amount1=Worksheets("sheet1").Cells(1,1) Rule#9.AvoidoveruseofStatusBarupdates VBAcanprocessmathfasterthanitcandisplaytheStatusBar.WritingtotheStatusBarisanotherexampleoftrafficbetweenVBAandExcel.Thefollowingexamplewritesoneoutofevery100scenariostotheStatusBar.Itisabout90timesfasterthanwritingeveryscenariototheStatusBar. Forscenario=1To10000 (insertcodehere)... IfScenariomod100=0ThenApplication.StatusBar=ScenarioNextScenario Rule#10.Avoidunnecessarymath Asactuarieswelovemacrosfullofformulas.Frequentlytheformulasarenotasefficientastheyshouldbe.Intheexamplebelow,themacrocalculatesthemonthlyvalueofafundgrowingat5percentannualeffectiveinterestfor50years.Ifrequentlyseecodelikethis: Interest_rate=.05Fori=1To600 fund(i)=fund(i-1)*(1+interest_rate)^(1/12)Nexti Itismoreefficienttoconverttheannualinterestratetomonthlyrateonceasshownbelow.Withintheloop,VBAonlyusesasinglenumericaloperation(multiplication).Theexampleaboveusesfournumericaloperations(onemultiplication,oneaddition,onedivisionandoneexponentiation)withintheloopandisthereforeaboutfourtimesslower. Interestrate=.05Interest_factor=(1+interest_rate)^(1/12)Fori=1to600 fund(i)=fund(i-1)*interest_factorNexti Alsonotethatexponentiationisslowerthanaddition,subtraction,multiplicationordivision. Rule#11.Don'tCopyandPaste TheCopyandPaste(orPasteSpecial)functionsareslow.Itisabout25timesfastertousethefollowingtocopyandpastevalues. Range("A1:Z100").value=Range("A101:Z200").value FinalThoughts Ihavefounditusefultowriteasmallmacrotoevaluatethetimesavingsassociatedwithvariousmethods.Themacrosimplyperformsamethodamilliontimesorsoandrecordsthetimespentperformingthatmethod.ThesimplemacrobelowcomparestheExcelMax()functiontotheMax2functionshowninRule#5. '**EvaluatethefirstfunctionStart_time=NowFori=1To1000000value1=Application.Max(amt1,amt2)NextiEnd_time=NowWorksheets("sheet1").Cells(1,2)=End_Time—Start_Time'**EvaluatethesecondfunctionStart_time=NowFori=1To1000000value1=Max2(amt1,amt2)NextiEnd_time=NowWorksheets("sheet1").Cells(2,2)=End_Time—Start_Time Pleasecontactmeifyouhaveothertime-savingtips. Ialsowanttopointoutandthankseveralexcellentarticlesontheinternetthataddressthistopic: http://www.avdf.com/apr98/art_ot003.htmlbyDermotBalsonhttp://www.cpearson.com/excel/optimize.htmbyPearsonSoftwareConsultinghttp://blogs.office.com/b/microsoft-excel/archive/2009/03/12/excel-vba-performance-coding-best-practices.aspxbyDiegoM.Oppenheimerhttp://www.ozgrid.com/VBA/SpeedingUpVBACode.htmbyOzgrid KevinRoper,FSA,MAAAisactuarywithAEGONUSAInc.Hecanbecontactedatkroper@aegonusa.com