6 Ways to Speed Up Excel Macros | Computergaga

Limiting the number of trips you make to the cells of your worksheets will greatly speed up Excel macros. This can be done by storing data in memory using ... SkiptomaincontentSkiptoprimarysidebarSkiptofooterHome/VBA/6WaystoSpeedUpExcelMacrosCreatingmacrosinExcelisafantasticwaytoautomateExcelprocessesandsaveloadsoftime.Howeverasyourmacrosgetbigger,youmayneedtospeedupExcelmacrosexecution.Thisblogpostlooksat6waysthatyoucanspeedupyourmacros.YoureallyneedtoknowthisstufftocreateefficientmacrosinExcel.IfyouareauserofExcelVBAalready,thenyoumayknowall,ormostofthese.Pleasefeelfreetocommentwiththetechniquesthatyouuse.WatchtheVideo–SpeedUpExcelMacrosSwitchOffAutomaticCalculationsWheneveracellbeingusedbyaformulaischanged,Excelrecalculatestheentireworksheet.Obviouslysuchactionscanslowdownyourmacros,especiallyifyouhavemanyformulas.Thisbehaviourisnormallyunnecessary,asweonlyneedtoseetheeffectthemacrohashadwhenithasfinishedexecuting.Soitdoesnotneedtorecalculateuntilthen.Bytypingthelinebelowsomewherenearthestartofthemacrocode,thiswillswitchExcelcalculationstomanual.Meaningtheywillnotrecalculateasvaluesarechanged.Application.Calculation=xlCalculationManualThenatorneartheendofthemacro,theywouldbeswitchedbacktoautomatic.Excelwillimmediatelyrecalculatetheentiresheetatthispoint.Application.Calculation=xlCalculationAutomaticStoptheScreenUpdatingAnotherverycommonsettingtoswitchoffwhilstthemacrorunsisscreenupdating.AsthemacrorunsExcelisconstantlyupdatingthescreentoshowwhatishappening.Howeverduringthemacrowedonotneedthis.Wecanturnthisoffinasimilarwaytotheprevioustechnique.Application.ScreenUpdating=FalseMakesureyouswitchitbackonattheendofthemacro.Application.ScreenUpdating=TrueDisableStatusBarUpdatesUpdatestotheStatusBarissomethingmostuserswillnotthinkabout,orevennotice.ButExcelisconstantlyupdatingthistoprovidetotals,counts,filteredrecords,zoomlevelandotherusefulinformation.Duringamacrothough,wedonotneeditupdatedanditistakingupresources.Thecodebelowwilldisabletheupdates.Application.DisplayStatusBar=FalseAndthenenablethemagainattheendofthemacro.Application.DisplayStatusBar=TruePreventExcelEventsfromRunningThissettingisoneyoumayneedtoconsideralittlemore,anditistodisableEvents.AsyouuseExcel,variouseventsaretriggered.Forexample,whenyoucloseaworkbook,theBeforeCloseeventistriggered.Andwhenavalueonaworksheetischanged,theWorksheetChangeeventistriggered.Nowthesealsotakeupresources,socanbedisabledwiththelinebelow.Application.EnableEvents=FalseBeawarethatifyoucreatedmacrosthatrunontheseevents,andyoudisableExceleventstheywillnotexecute.Ifyoudodisablethem.Makesureyouenablethemagainattheend.Application.EnableEvents=TrueThesefourdifferentsettingsmentionedsofartoimprovemacroperformance,areallfoundontheApplicationobject.InsteadofreferringtotheApplicationobject4differenttimes,theycouldhavebeenwrappedinaWithconstructlikebelow.WithApplication .Calculation=xlCalculationManual .ScreenUpdating=False .DisplayStatusBar=False .EnableEvents=False EndWithAndthenagainattheendofthemacrotoswitchthemback.StopSelectingObjectswhenPerformingOperationsOnethingyoudefinitelywanttostopdoing,isselectingobjectstoperformoperationsonthem.Yourarelyneedtodothis.Youcanchangevalues,testvaluesandformatobjectswithoutselectingthemfirst.ThiswillgreatlyspeedupExcelmacros.Takeanexampleofcopyandpastingdatafromoneworksheettoanother.IfIrecordmyselfdoingthis.Thefollowingcodeiscreated.Range("A1:B5").Select Selection.Copy Sheets("ToSheet").Select Range("B2").Select ActiveSheet.PasteIthasgenerated5linesofcode.Totallyunnecessarytoselecttherangesandtheworksheetwhenperformingtheseactions.Thatprocedurecouldbesimplifiedtothecodebelow.Range("A1:B5").CopyDestination:=Worksheets("ToSheet").Range("B2")Nowthisisoneexample,andtheoptionsavailabletoyouwilldependwhatyouaredoing.Bydoingsomeresearchyoucanprobablyoptimiseyourcodealittlebetter.AndselectingobjectsunnecessarilyiscommonamongbeginnerstomacrosinExcel.UseDatainMemoryandAvoidTripstotheWorksheetsLimitingthenumberoftripsyoumaketothecellsofyourworksheetswillgreatlyspeedupExcelmacros.Thiscanbedonebystoringdatainmemoryusingvariablesinstead.Referringto,andtestingthevaluesinvariablesisfarmoreefficientthanaccessingthevaluesonworksheets.Takethecodebelowforexample;SubUseVariables() Range("A2").Select DoUntilActiveCell.Value="" IfActiveCell.Value=Worksheets("Sheet5").Range("B3").ValueThen ActiveCell.Offset(0,1).Value=ActiveCell.Offset(0,1).Value*1.1 ExitDo EndIf ActiveCell.Offset(1,0).Select Loop MsgBox"Complete" EndSubInthiscodetherearemanyinteractionswiththeworksheets.ThereistheselectionofcellA2.TheIFstatementcomparesthevalueinB3ofthesheetcalledSheet5.AndthentheconstantreferencestotheActiveCellandthecellsaroundit.Thecodebelowisanimprovementonthis;SubUseVariables() DimRowNumAsLong DimSalesNameAsString RowNum=2 SalesName=Worksheets("Sheet5").Range("B3").Value DoUntilCells(RowNum,1).Value="" IfCells(RowNum,1).Value=SalesNameThen Cells(RowNum,2).Value=Cells(RowNum,2).Value*1.1 ExitDo EndIf RowNum=RowNum+1 Loop MsgBox"Complete" EndSubItstoresthevalueinB3ofSheet5inavariableandthentestthateachtime,insteadofkeepgoingbacktolookatthatsheet.Italsousesavariabletoincrementtherownumbersothatwedonotneedtophysicallymovetoeachcell.These6techniqueswillstopmanyoftheunnecessaryactionsthatmacrosperform,andwillthereforegreatlyincreasemacrospeed.MoreExcelVBATutorialsLoopthroughallthefilesinafolderAutomaticallyrefreshPivotTableswhendatachangesPicknamesatrandomfromalistEmailaworkbookasanattachmentRelatedPosts:EnteringFractionsinExcelCreateaPictureLookupinExcelCreateaCaseSensitiveLookupFormulainExcelCompareTwoListsinExceltoHighlightMatchedItemsReaderInteractionsLeaveaReplyCancelreplyYouremailaddresswillnotbepublished.Requiredfieldsaremarked*Comment*Name*Email*WebsiteSavemyname,email,andwebsiteinthisbrowserforthenexttimeIcomment. ΔPrimarySidebarPopularPostsExcelFixturesandLeagueTableGeneratorExcelIFFunctionContainsText–APartialMatchinaCellExcelFormulatoDisplaytheSheetNameinaCell5ReasonsWhyyourExcelFormulaisNotCalculating4AlternativestoNestedIFFormulasHowtoCreateaReverseFINDFormulaCheckifaValueisanEvenNumberIFFunctioninPowerQueryIncludingNestedIFSHowtoHyperlinktoaHiddenWorksheetinExcelConditionalFormattingMultipleColumns–3ExamplesreportthisadRecentPostsSEQUENCEFunctioninExcelSortbyDropDownListinExcelAdvancedSUMFunctionExamples–ThePowerofSUMDisplayNegativeTimeinExcelLookupMultipleValuesinExcelreportthisadreportthisadFooterContact[email protected]UsefulLinksCourseTopicsChartsCoursesDashboardsDownloadsExcelTipsFormulasMailMergeOfficePivotTablesPowerBIPowerPivotPowerQueryVBAJoinOurMailingListxx