3 Simple Ways To Speed Up Your Excel Macros

3 Simple Ways To Speed Up Your Excel Macros · Disable The Application.ScreenUpdating Property. · Disable The Status Bar Updates. · Disable The ... FormulasExcelTips&TricksExcelChartingTutorialsVBABookStoreMore  BlogDownloadAreaExcelVideoTutorialsHome»3SimpleWaysToSpeedUpYourExcelMacrosHelloExcellerstimeforsomemore#macromondayExcelfun.TodayIwanttosharewithyoumytop3waystospeedupyourExcelmacros.DependingonhowcomplexyourVBAmacrobecomes,youmayfindthatyourmacrostakeawhiletorun.So,herearemygototop3waystospeedupyourmacrosandtoincreaseperformance.YoumayfindthatwhenyourunanExcelmacro,yougetsomescreenflickering.So,hereisthefirstrecommendationtospeedthingsup. DisableTheApplication.ScreenUpdatingProperty.BydisablingtheApplication,ScreenUpdatingpropertywecanpreventthatflickeringinExcel,whichinturnsavesontheExcelmemoryresources.Onceyourmacrohasrunyouthenturnonthescreenupdatingagain.Usually,thispieceofVBAcodeisplacedbeforeyourmaincodinginstructionstoExcelbeforeanyprocedureshaverun.Ihaveanexampleofhowtowritetheseinstructionsintoyourcodebelow.SubMyMacro()Application.ScreenUpdating=False‘PlaceyourmacrocodehereApplication.ScreenUpdating=TrueEndSubYoucanseethatyourcodesitsinbetweentheVBAinstructionstoturnoffthescreenupdating(Application.ScreenUpdating=False)andturningitbackonagain(Application.ScreenUpdating=False).DisableTheStatusBarUpdates.ThestatusbarinExcelwillautomaticallydisplaytheprogressofspecifictasksitisrunning,andifyourmacroiscrunchingalotofinformationthisstatusbarcanalsotakeuppreciousExcelresources.This,therefore,bringsmetomysecondrecommendationtoimproveperformance.Evenifyouapplythefirstrecommendationofdisablingthescreen,statusbarupdatesstillareapplied.So,bydisablingthisupdatealsowecansavesomeExcelmemoryagainandhelpwithperformance.OnceyourcodehasrunthenyoucaninstructExceltoturnbackontheupdates.Ihaveanexampleofhowtowritetheseinstructionsintoyourcodebelow.SubMyMacro()Application.DisplayStatusBar=False‘PlaceyourmacrocodehereApplication.DisplayStatusBar=TrueEndSubYoucanseethatagainthiscodesitsinbetweentheVBAinstructionstoturnoffthestatusbarandturnitbackonagain. DisableTheApplication.EnableEventsPropertyAneventisfiredwhensomethinghappenswhileauserisinExcel.Eventscanrangefromapop-upmessageboxorsomesortofwarningtotheuser.Theseeventscanleadtoextralinesofcodebeinggenerated,andcansometimesinterferewithyourintendedoutcomeofcode.Turningoffeventswhilstyourcodeisrunningcanhelpittorunbetter,butagainyouneedtoenteralineofcodetoturneventsbackonagainafteryourVBAcodehasexecuted.Ihaveanexampleofhowtowritetheseinstructionsintoyourcodebelow.SubMyMacro()Application.EnableEvents=False‘PlaceyourmacrocodehereApplication.EnableEvents=TrueEndSub IfyouwanttodisablealloftheseeventsthensimplycombinethebitsofcodetogetherbeforeyourVBAmacrocodeandthenenablethealtogetheragainafteryourcode. WhynottrytheseandshareinthecommentsbelowiftheyhaveimprovedperformanceforyourExcelmacros. WhatNext?WantMoreExcelTips?So,ifyouwantmoretoptipsthensignupformyMonthlyNewsletterwhereIshare3TipsonthefirstWednesdayofthemonthandreceivemyfreeEbook,30ExcelTips.  DoYouNeedHelpWithAnExcelProblem?.IampleasedtoannounceIhaveteamedupwithExcelRescue,whereyoucangethelpwithExcelFAST. TagsBlog Cells Excel ExcelCharting ExcelExcelTips ExcelFormulas exceltip ExcelTipCategory ExcelTips ExcelTips&Tricks exceltutorials ExcelVideos ExcelVideoTutorials formatting Formula formulafriday Formulas macro MacroMondays Macros PivotTable PivotTables PowerBI PowerQuery Tutorials vb VBA WorksheetsRecommendedExcelResourcesreportthisadSHAREreportthisadAdditionalInfoWantFREEExcelTips?ClickonmyFREEeBook,itsmybonusforjoiningthousandsofotherswhoreceivemy3xFREEExceltipseverymonthinmyExcelNewsletter.JoinUs!.ExcelJobsExcelChartingSelfExpandingChartsOneClickChartsCreateQuickDynamicChartsEasyCombinationChartsprivacypolicyx