More Than 10 Ways to Speed Up Macros - ExcelHowto

More Than 10 Ways to Speed Up Macros · Turn Off Automatic Calculation · Disable Screen Updating · Turn Off Status Bar Updates · Ignore Events · Hide ... Skiptomaincontent Home»ExcelMacros»Tips&Tricks»MoreThan10WaystoSpeedUpMacros MoreThan10WaystoSpeedUpMacros ExcelHowTo Mar8,2022 26minutestoread 1777 0 TableofcontentsTurnOffAutomaticCalculationDisableScreenUpdatingTurnOffStatusBarUpdatesIgnoreEventsHidePageBreaksSuspendPivotTableUpdatesAvoidCopyandPasteUsetheWithStatementRemovetheSelectmethodsAvoidExcessiveReferenceAvoidUsingVariantTypesUsingWorksheetFunctionsorMethodsUsingArrayInsteadofRangeSpeedUpYourMacrosVBACode "AllroadsleadtoRome",thesametaskcanalsobeaccomplishedindifferentwaysinVBA.Whenstartingtolearnprogramming,itisoftenenoughtoachievethegoal,andnottothinkmoreabouttherunningspeedandresourceconsumptionofthecode. Asthelevelofprogrammingincreasesandthecomplexityoftheprojectincreases,youwillincreasinglyfeeltheimportanceofcodeoptimization. Youcantakestepstoimprovetheperformanceofyourmacros.Inthisarticle,youwillfindtenwaystohelpyoukeepyourExcelmacrosrunningatoptimalperformancelevels. TurnOffAutomaticCalculation Whenautomaticcalculationisenabled,Excelrecalculatestheworkbookautomaticallyeachtimeavalueaffectingaformulachanges.Inworksheetswithalargenumberofformulas,thisbehaviorcanslowdownyourmacrosconsiderably. Ifyourworkbookisformula-intensive,youmaynotwantExceltotriggerarecalculationeverytimeamacrochangesacellvalue. CalculationPropertiestotellExceltoswitchtomanualcalculationmode.Whenaworkbookisinmanualcalculationmode,theworkbookdoesnotrecalculateuntilyouexplicitlytriggerthecalculationbypressingtheF9keyonthekeyboard. TurningoffExcel'sautomaticcalculationbehaviorcandramaticallyspeedupyourmacros.TheideaistoputExcelinManualCalculationmode,runyourcode,andthenswitchbacktoAutomaticCalculationmode. '-------------mdlCalculationMode------------- SubCalculationMode() Application.Calculation=xlCalculationManual 'Placeyourmacrocodehere Application.Calculation=xlCalculationAutomatic EndSub PleasenotethatsettingthecalculationmodebacktoxlCalculationAutomaticwillautomaticallytriggerarecalculationoftheworksheet.Therefore,youdonotneedtopressF9afteryourmacrohasrun. Dependingonyourneeds,youmayactuallyneedtoperformcalculationsduringthemacrorun,inwhichcaseyoudonotwanttoinvokethemanualcalculationmode.Besuretoconsideryourapplicationscenarioanddeterminewhathappenswhenyouturnoffautomaticcalculationwhilethemacroisrunning. DisableScreenUpdating Youmaynoticethatyourscreenflickersquiteabitwhenyourmacrosarerunning.ThisflickeringisExcel'sattempttoredrawthescreentoshowthecurrentstateoftheworksheet.Unfortunately,eachtimeExcelredrawsthescreen,ittakestime.Inmostcases,youdon'tneedExceltorunoutofresourcestoredrawthescreeneverytimeamacroperformssomeaction. ScreenUpdatingpropertytodisableanyscreenupdatesuntilyourmacrohasfinishedrunning.Thissavestimeandresources,andallowsyourmacrotorunalittlefaster.YoucanturnScreenUpdatingbackonafterthemacrocodehasfinishedrunning. '-------------mdlDisableScreenUpdating------------- SubDisableScreenUpdating() Application.ScreenUpdating=False 'Placeyourmacrocodehere Application.ScreenUpdating=True EndSub AftersettingtheScreenUpdatingpropertybacktoTrue,Excelwillnotonlyresumeredrawing,butwillalsotriggerthefirstredrawtoshowallchangesfromthemacro. TurnOffStatusBarUpdates AtthebottomoftheExcelwindow,youwillseetheExcelStatusBar.ThestatusbarusuallyshowstheprogressofcertainactionsinExcel.Forexample,ifyoucopy/pastearange,Excelwillshowtheprogressofthatactioninthestatusbar.Usually,theoperationisperformedsoquicklythatyoucan'tseetheprogressinthestatusbar.However,ifyourmacroisprocessingalotofdata,thestatusbarwilltakeupsomeresources. Notethatturningoffscreenupdatesisseparatefromturningoffthestatusbardisplay.Thismeansthatthestatusbarwillcontinuetoupdateevenifyoudisablescreenupdates.Youcanfurtherimprovethemacro'sperformancebytemporarilydisablinganystatusbarupdatesusingtheApplication. '-------------mdlStatusBarUpdates------------- SubStatusBarUpdates() Application.DisplayStatusBar=False 'Placeyourmacrocodehere Application.DisplayStatusBar=True EndSub IgnoreEvents YoucanimplementmacrosaseventproceduresthattellExceltoruncertaincodewhenaworksheetorworkbookchanges. Sometimes,thestandardmacrosmakechangesthatactuallytriggertheeventprocedure.Forexample,supposeyouimplementtheWorksheet_ChangeeventforSheet1ofaworkbook.Wheneveracellorrangeischanged,theWorksheet_Changeeventistriggered. SoifyouhaveastandardmacrotomanipulatemultiplecellsonSheet1,eachtimeyouchangeacellonthatsheet,yourmacromustpausewhiletheWorksheet_Changeeventruns.Youcanimaginehowthisbehaviorwouldslowdownyourmacro. AnotherperformanceimprovementatthispointistousetheEnableEventspropertytotellExceltoignoreeventswhilethemacroisrunning. SimplysettheEnableEventspropertytoFalsebeforerunningthemacro.Afterthemacrocodehasrun,youcansettheEnableEventspropertybacktoTrue. '-------------mdlIgnoreEvents------------- SubIgnoreEvents() Application.EnableEvents=False 'Placeyourmacrocodehere Application.EnableEvents=True EndSub Whileitistruethatdisablingeventscanspeedupyourmacros,youmayactuallyneedtotriggersomeeventswhilethemacroisrunning.Besuretoconsideryourparticularscenarioanddeterminewhathappensifyouturnoffworksheetorworkbookeventswhilethemacroisrunning. HidePageBreaks IfyoudisplayPageBreaksonaworksheet,Excelisforcedtospendtimerecalculatingwheretodisplaypagebreaksontheworksheeteverytimeyourmacromodifiesthenumberofrows,columns,orchangesthesheet'spagesettings. Youcanavoidthisbysimplyhidingthepagebreaksbeforestartingthemacro.SettheDisplayPageBreaksworksheetpropertytoFalsetohidethepagebreaks.Ifyouwanttocontinuetodisplaypagebreaksafterthemacrohasrun,youcansettheDisplayPageBreaksworksheetpropertybacktoTrue. '-------------mdlDisplayPageBreaks------------- SubDisplayPageBreaks() ActiveSheet.DisplayPageBreaks=False 'Placeyourmacrocodehere ActiveSheet.DisplayPageBreaks=True EndSub SuspendPivotTableUpdates IfyourmacrooperatesonPivotTablesthatcontainlargedatasources,youmayexperiencepoorperformancewhenperformingoperationssuchasdynamicallyaddingormovingpivotfields.ThisisbecauseeachchangeyoumaketothePivotTablestructurerequiresExceltorecalculatethevaluesinthePivotTableforeachpivotfieldthatyourmacrotouches. Youcanimprovethemacro'sperformancebypausingtherecalculationofthePivotTableuntilallpivotfieldchangesarecompleted.SimplysetthePivotTable.ManualUpdatepropertytoTruetodelaytherecalculation,runthemacrocode,andthensetthePivotTable.ManualUpdatepropertybacktoFalsetotriggertherecalculation. '-------------mdlPivotTablesManualUpdate------------- SubPivotTablesManualUpdate() ActiveSheet.PivotTables("PivotTable1").ManualUpdate=True 'Placeyourmacrocodehere ActiveSheet.PivotTables("PivotTable1").ManualUpdate=False EndSub AvoidCopyandPaste ItisimportanttorememberthatwhiletheMacroRecordersavestimebywritingVBAcodeforyou,itdoesnotalwayswritethemostefficientcode.AtypicalexampleishowtheMacroRecordercapturesanycopyandpasteoperationsthatyouperformwhilerecording. IfyouweretocopycellA1andpasteitintocellB1whilerecordingamacro,theMacroRecorderwouldcapturethefollowing. SubMacro1() ' 'Macro1Macro ' ' Range("A1").Select Selection.Copy Range("B1").Select ActiveSheet.Paste EndSub WhilethiscodedoescopyandpastefromcellA1intoB1,itforcesExceltousetheclipboard,whichaddsakindofmiddlemanwhereitisnotneeded. Youcanslightlyenhanceyourmacrobyremovingthemiddlemanandperformingadirectcopyfromonecelltothedestinationcell.ThisalternativecodeusestheDestinationparametertobypasstheclipboardandcopythecontentsofcellA1directlytocellB1. Range("A1").CopyDestination:=Range("B1") Ifyouonlyneedtocopyvalues(andnotformattingorformulas),youcanfurtherimproveperformancebyavoidingtheCopymethodaltogether.Simplysetthevalueofthetargetcelltothesamevalueasinthesourcecell.Thismethodisabout25timesfasterthanusingtheCopymethod. Range("B1").Value=Range("A1").Value Ifyouonlyneedtocopyformulasfromonecelltoanother(notvaluesorformats),youcansettheformulainthetargetcelltothesameformulacontainedinthesourcecell. Range("B1").Formula=Range("A1").Formula UsetheWithStatement Whenrecordingmacros,youwilloftenmanipulatethesameobjectmultipletimes. Forexample,yourcodemaychangetheformattingofcellA1sothatitisunderlined,italicized,andboldformatted.IfyourecordamacrowhileapplyingtheseformattingoptionstocellA1,youwillgetthefollowing. SubMacro2() Range("A1").Select Selection.Font.Bold=True Selection.Font.Italic=True Selection.Font.Underline=xlUnderlineStyleSingle EndSub Unfortunately,thiscodeisnotasefficientasitshouldbe,becauseitforcesExceltoselectandthenreferenceeachchangedobject. YoucansavetimeandimproveperformancebyusingtheWithstatementtoperformmultipleoperationsonagivenobjectwhileonlyreferencingtheobjectonce.TheWithstatementinthisexampletellsExceltochangethreeproperties,butonlyreferencestheFontobjectonce,usingfewerresources. WithRange("A1").Font .Bold=True .Italic=True .Underline=xlUnderlineStyleSingle EndWith GettingintothehabitofchunkingoperationsintoWithstatementsnotonlymakesmacrosrunfaster,butalsohelpsmakethecodeeasiertoread. RemovetheSelectmethods Ifyourecordamacrobyenteringthevalue1234incellA1ofmultipleworksheets,youwillendupwithcodesimilartothis. Sheets("Sheet1").Select Range("A1").Select ActiveCell.FormulaR1C1="1234" Sheets("Sheet2").Select Range("A1").Select ActiveCell.FormulaR1C1="1234" Sheets("Sheet3").Select Range("A1").Select ActiveCell.FormulaR1C1="1234" Asyoucansee,themacrorecorderlikestousethe"Select"methodtoexplicitlyselectobjectsbeforemanipulatingthem.Whilethiscodeworksquickly,it'snotthatefficient.ItforcesExceltospendtimeexplicitlyselectingeachobjectbeingmanipulated. Generallyspeaking,itisnotnecessarytoselectobjectsbeforeprocessingthem.Infact,youcangreatlyimprovetheperformanceofmacrosbynotusingthe"select"method. Afterrecordingyourmacro,getinthehabitofchangingthegeneratedcode,removingtheselectmethod.Inthiscase,theoptimizedcodewouldlooklikethis.Notethatnothingisselected.Thecodejustusesthehierarchyofobjectstoapplythedesiredaction. Sheets("Sheet1").Range("A1").FormulaR1C1="1234" Sheets("Sheet2").Range("A1").FormulaR1C1="1234" Sheets("Sheet3").Range("A1").FormulaR1C1="1234" AvoidExcessiveReference Whencallingamethodorpropertyofanobject,itneedstogothroughtheIDispatchinterfaceoftheOLEcomponent.ThecallstotheseOLEcomponentstaketime,soreducingthenumberofreferencestoOLEcomponentscanimprovethespeedofthemacrocode. Fortheinvocationofobjectpropertiesormethods,therepresentationmethodofObject.Methodisgenerallyused,thatis,the"."symbolisusedtoinvokepropertiesandmethods. Therefore,thenumberofmethodorpropertycallscanbejudgedaccordingtothenumberofsymbols".".Thelessthe"."symbol,thefasterthecoderuns. Forexamplethefollowingstatementincludes3symbols".". ThisWorkbook.Sheet1.Range("A1").Value=100 Thefollowingstatementhasonlyonesymbol".". Activewindow.Top=100 Herearesometrickstoreducethenumberofsymbols"."torunfaster. First,whenyouneedtorefertothesameobjectrepeatedly,youcansettheobjecttoavariabletoreducethenumberofcalls.Forexample,thefollowingcoderequirestwocallsperline. ThisWorkbook.Sheets("Sheet1").Cells(1,1)=100 ThisWorkbook.Sheets("Sheet1").Cells(2,1)=200 ThisWorkbook.Sheets("Sheet1").Cells(3,1)=300 BecausetheSheets("Sheet1")objectneedstobereferencedrepeatedly,itcanbesettoavariableshtfirst,sothateachcodeonlyneedstobecalledonce. Setsht=ThisWorkbook.Sheets("Sheet1") sht.Cells(1,1)=100 sht.Cells(2,1)=200 sht.Cells(3,1)=300 Second,ifyoudon'twanttodeclareatemporaryvariablesht,youcanalsousetheWithstatementmentionedearlier.Asshowninthefollowingexample: WithThisWorkbook.Sheets("Sheet1") .Cells(1,1)=100 .Cells(2,1)=200 .Cells(3,1)=300 EndWith Third,whentherearealotofloops,trytokeeppropertiesandmethodsoutsidetheloop.Whenreusingapropertyvalueofthesameobjectinaloop,youcanfirstassignthepropertyvaluetoaspecifiedvariableoutsidetheloop,andthenusethevariableintheloop,whichcanachievefasterspeed.Asshowninthefollowingexample: Fori=1To1000 ThisWorkbook.Sheets("Sheet1").Cells(1,1)=Cells(1,2).Value ThisWorkbook.Sheets("Sheet1").Cells(2,1)=Cells(1,2).Value ThisWorkbook.Sheets("Sheet1").Cells(3,1)=Cells(1,2).Value Nexti EachloopinthisexamplegetstheValuepropertyofthecellCells(1,2).IfyouassigntheValuepropertyofCells(1.2)toavariablebeforetheloopstarts,youwillgetafasterrun.Asshowninthefollowingexample: tmp=Cells(1,2).Value Fori=1To1000 ThisWorkbook.Sheets("Sheet1").Cells(1,1)=tmp ThisWorkbook.Sheets("Sheet1").Cells(2,1)=tmp ThisWorkbook.Sheets("Sheet1").Cells(3,1)=tmp Nexti TheabovecodecallsThisWorkbook.Sheets("Sheet1")everytimeitloops.YoucandothisfasterbyusingtheWithstatementtomovethecalltoThisWorkbook.Sheets("Sheet1")outsidetheloop.Asshowninthefollowingexample: tmp=Cells(1,2).Value WithThisWorkbook.Sheets("Sheet1") Fori=1To1000 .Cells(1,1)=tmp .Cells(2,1)=tmp .Cells(3,1)=tmp Nexti EndWith AvoidUsingVariantTypes BeginnersusuallyprefertouseVarianttypevariables,whichhastheadvantageofbeinglesscomplicatedbecauseanytypeofdatacanbeusedinwithouttheproblemofmemoryoverflowifthedataistoolargefortheIntegerorLongdatatypes.However,Varienmttypedatarequiresmoreextramemoryspacethantheotherspecifiedtypes(2bytesforIntegerdata,4bytesforLongdata,and16bytesforVariantdata),VBArequiresmoretimetoprocessVariant-typedatathanotherspecifiedtypesofdata.Asthefollowingexampleshows. SubVariantTest() DimiAsLong DimixAsInteger,iyAsInteger,izAsInteger DimvxAsVariant,vyAsVariant,vzAsVariant DimtmAsDate vx=100:vy=50 tm=Timer Fori=1To1000000 vz=vx*vy vz=vx+vy vz=vx-vy vz=vx/vy Nexti Debug.Print"Varianttypestake"&Format((Timer-tm),"0.00000")&"seconds" ix=100:iy=50 tm=Timer Fori=1To1000000 iz=ix*iy iz=ix+iy iz=ix-iy iz=ix/iy Nexti Debug.Print"Integertypestake"&Format((Timer-tm),"0.00000")&"seconds" EndSub Intheabovecode,lines8to13do1millionoperationsofaddition,subtraction,multiplicationanddivisionofVariantvariables,andlines17to22do1millionoperationsofaddition,subtraction,multiplicationanddivisionofIntegervariables.Onmycomputer,theoperationoftheVariantvariabletookabout0.09375seconds,whiletheoperationoftheIntegervariabletookabout0.03125seconds.Theresultsmayvaryfromcomputertocomputer,butVariantvariablesaresignificantlyslowerthanIntegervariables. Forthisreason,itisrecommendedtoavoidusingVariantvariableswhenyoucanexplicitlyusethespecifieddatatype. UsingWorksheetFunctionsorMethods TheExcelWorksheetFunctionsaregenerallymuchfasterthanjustusingVBAnormalcodetomanipulatingcellregions. Asthefollowingexampleshows. SubWorksheetFunctionTest() DimTotal1AsLong,Total2AsLong,iAsInteger,tmAsDate Dimcl tm=Timer Fori=1To1000 ForEachclInRange("A1:A500") Total1=Total1+cl.Value Next Nexti Debug.Print"VBA:"&Format((Timer-tm),"0.00000")&"seconds" tm=Timer Fori=1To1000 Total2=Total2+WorksheetFunction.Sum(Range("A1:A500")) Nexti Debug.Print"WorksheetFunction:"&Format((Timer-tm),"0.00000")&"seconds" EndSub TheWorksheetFunctionTestprocedurecomparesthetimetakenbythetwomethodstocalculatethesumoftheworksheetrangedata. Lines5to9useVBAtocalculatethesumofrangeA1:A500,andlines12to14usetheworksheetfunctionSUMtocalculatethesumofthesamerange,runningeachmethod1000times.Onmycomputer,theformertookabout0.78125seconds,whiletheworksheetfunctionmethodtookonly0.01953seconds. ExcelalsoincludesothermathematicalstatisticalfunctionssuchasPPODUCT,COUNTF,etc.,aswellassomelookupfunctionssuchasVLOOKLP.EffectiveuseofthesefunctionscanbeobtainedfasterthantheuseofVBAcode. UsingsomeothermethodsofmanipulatingcellareassuchasPeplace,FindandFindNextcanalsobefasterthanusingVBA. UsingArrayInsteadofRange IfyouonlyneedtoworkwiththevaluesofthecellsintheRangeobjectwithoutthepropertiesandmethodsofthecells,youcanuseArraytoworkwiththeRangeobject,becausetheArrayvariablesaremuchfasterthanthePangeobject'soperations. SubArrayTest() DimTotal1AsLong,Total2AsLong,iAsInteger,tmAsDate Dimcl Dimarr() tm=Timer arr=Range("A1:A500") Fori=1To1000 ForEachclInarr Total1=Total1+cl Next Nexti Debug.Print"Array:"&Format((Timer-tm),"0.00000")&"seconds" tm=Timer Fori=1To1000 ForEachclInRange("A1:A500") Total2=Total2+cl.Value Next Nexti Debug.Print"Range:"&Format((Timer-tm),"0.00000")&"seconds" EndSub Lines7to11useArraytocalculatethesumofrangeA1:A500,andlines14to18useRangetocalculatethesumofthesamerange,runningeachmethod1000times.Onmycomputer,theArraymethodtookabout0.01563seconds,whiletheRangemethodtookonly0.72266seconds. SpeedUpYourMacrosVBACode Insummary,youcanusuallyspeedupyourmacroswiththefollowingcode. '-------------mdlSpeedUpMacros------------- SubSpeedUpMacros() Application.Calculation=xlCalculationManual Application.ScreenUpdating=False Application.DisplayStatusBar=False Application.EnableEvents=False ActiveSheet.DisplayPageBreaks=False 'ActiveSheet.PivotTables("PivotTable1").ManualUpdate=True 'Placeyourmacrocodehere 'ActiveSheet.PivotTables("PivotTable1").ManualUpdate=False Application.Calculation=xlCalculationAutomatic Application.ScreenUpdating=True Application.DisplayStatusBar=True Application.EnableEvents=True ActiveSheet.DisplayPageBreaks=True EndSub Tips&TricksVBA 6courselikecounts Recommendedcontent SavingaWorkbookWhenaSpecificCellIsChanged DeleteAllWorksheetsExceptActiveOne SortWorksheetsbyName UnprotectaWorksheetonWorkbookOpen CreatingaNewWorkbookUsingMacro UnhideAllWorksheetsinaWorkbook MostviewedFormattingaRangeofCellsInExcelVBAHowToSetColumnWidthIncmAtOnceSelectaRangeInExcelVBAHowtoQuicklyExtractPicturesFromAnExcelFileFindandSelecttheFirstBlankCellinaColumnVBAPrintWorksheetsVBA LeaveacommentCancelYouremailaddresswillnotbepublished.Requiredfieldsaremarked*Formatyourcode:

placeyourcodehere

SavemynameandemailinthisbrowserforthenexttimeIcomment. Δ TableofcontentsTurnOffAutomaticCalculationDisableScreenUpdatingTurnOffStatusBarUpdatesIgnoreEventsHidePageBreaksSuspendPivotTableUpdatesAvoidCopyandPasteUsetheWithStatementRemovetheSelectmethodsAvoidExcessiveReferenceAvoidUsingVariantTypesUsingWorksheetFunctionsorMethodsUsingArrayInsteadofRangeSpeedUpYourMacrosVBACode