Application.ScreenUpdating can dramatically speed up your ...

Application.ScreenUpdating is a setting within Excel that - when turned on - will visibly update the Excel worksheet on your screen any time ... Wheneveryouhaveatasktoautomate,youcanusuallygodownthe VBAroute. Andusuallywhenyouwriteyourcode,you’rejusttryingtogetthingstowork. Youknowhowitgoes.Writesomecode,test,thenfixthebugs.Maybelateryou addsomefeatureshere-and-there,makingyourVBAcodemorepowerful.Butafter awhileyounoticeyourcodeis beginningtogetveryslowwhenitruns.It’s notasfastasitusedtobeanditcanbegintogetprettyfrustratingwaiting alongtimeforyourcodetofinish.Ifthissoundslike you, then Application.ScreenUpdatingmightbeyour answer. SettingtheStage Let’sstartwithaneasyexample.We’ll taketherangeA1:P30andloopthrough theentirerange atotalof30times.Therewillbeacountertotellus how manytimeswe’ve“touched”acell. Everytimewemovetothenextcell,wewill: Putthevalueofthecounterinthecell Checkthecountertoseeifitiseven Ifitiseven,we’llhighlightthecell whiteandmakethetextblack Ifnot,we’llhighlightthecellredandmakethetextwhite OncewereachthelastcellinA1:P30 wewilloffsetthecounterby1to alternatewhichcellshaveevendigitsandwhichhaveoddones.Wedothis becausewewanttocauseasmanyscreenupdatesaswecantoshowcasethe difference turningoffApplication.ScreenUpdatingcanmake. We’llalsowanttokeeptrackofhowlongthistakes,sowe’llneedtoaddsome codeinforthataswell. PublicSubAddContentToSheet() DimstartTimeAsDouble startTime=Timer DimrAsExcel.Range Setr=ActiveSheet.Range("A1:P30") DimiAsLong DimrepeatAsLong DimcellAsExcel.Range Forrepeat=1To30 ForEachcellInr cell.Value=i If(cell.ValueMod2=0)Then 'ifiiseven,colorthecellwhite cell.Interior.Color=vbWhite cell.Font.Color=vbBlack Else 'else,colorthecelllightgray cell.Interior.Color=RGB(240,240,240) cell.Font.Color=vbWhite EndIf i=i+1 Nextcell 'offsetitoalternatecolumncoloring i=i+1 Nextrepeat MsgBox"Totaltimewas:"&(Timer-startTime) EndSub Themorewemesswiththescreenupdates(likechangingfontsize,boldand italics,etc.)themoreworkwedoforthescreentoprocessandshowwhatit’s doing. Goaheadandrunthiscode.FormyPC,ittookabout7 secondstorun. Itfeltlikeforever. Beforemovingon,let’sgetabetterunderstandingofwhythisistakingsolong. Imentionedthattheissueisthatthescreenisconstantlyupdating,whichis causingthe codetorunslowly.Ifyouthinkaboutit,iftherewere less of thescreentoprocess,itshouldrunfaster,right? Trythisout:shrinkyourExcelworkbookwindowtoasmallersizeandrun thecodeagain.Thengoevensmallerandrunitagain.Dothisafewmoretimes. Noticeadifference? Thecoderunsmuchfasterwhenthere’slessrealestateto updateonyourscreen.Nowitshouldmakesensewhyit’sagoodideatoturnoff Application.ScreenUpdating.Solet’sseewhathappenswhenwedothat. Doesthisarticlehelpyou?Ifso,pleaseconsidersupportingmewithacoffee☕️ TurnoffApplication.ScreenUpdating Application.ScreenUpdatingisasettingwithinExcelthat-whenturnedon- willvisiblyupdatetheExcelworksheetonyourscreenanytimethereisa changethathappenswithintheworksheet.Application.ScreenUpdating=True (meaning,it’sturnedon)isthe default. Let’strythecodeagain,exceptthistimewewillturnoff Application.ScreenUpdatingsowewon’trefreshthescreenconstantlyuntilthe workiscompleted: PublicSubAddContentToSheet() Application.ScreenUpdating=False DimstartTimeAsDouble startTime=Timer DimrAsExcel.Range Setr=ActiveSheet.Range("A1:P30") DimiAsLong DimrepeatAsLong DimcellAsExcel.Range Forrepeat=1To30 ForEachcellInr cell.Value=i If(cell.ValueMod2=0)Then 'ifiiseven,colorthecellwhite cell.Interior.Color=vbWhite cell.Font.Color=vbBlack Else 'else,colorthecelllightgray cell.Interior.Color=RGB(240,240,240) cell.Font.Color=vbWhite EndIf i=i+1 Nextcell 'offsetitoalternatecolumncoloring i=i+1 Nextrepeat MsgBox"Totaltimewas:"&(Timer-startTime) Application.ScreenUpdating=True EndSub Nowthecoderunsat1.4secondsforme,whichisahugeimprovement. Irealizethisisasomewhatsillyexample,butallI’mtryingtoillustrateis thatwhenyouhaveamacrothatisconstantlyupdatingthescreenwhenit’snot necessary,youcouldsaveyourself alotoftimebyturningoff Application.ScreenUpdating. Also,don’tforgettosetApplication.ScreenUpdating=Trueattheendofyour macro.IfyouleaveitsettoFalse,thenyourusersmightthinkthatthe worksheetisbroken.Thisisbecausewhenyouhaveauto-calculatedvalues(like formulas),theywon’tbeupdatedunless youperforma“CalculateSheet”byusing F9(orintheFormulasgroupintheribbon). Onelastnote,ifyouhaveasubprocedure thatcallsmoresubprocedures and youhaveadeepchainofthese calls,makesuretouse Application.ScreenUpdatingintheouter-most subprocedurethat’srunning. OtherwiseyoumayinadvertentlyturnApplication.ScreenUpdatingbackona littletooearly.←HowtoCapitalizeNamesinExcelHowtoJoinCellsInExcelwithaComma(orotherdelimiter)→