How To Speed Up VBA Code - excel - Stack Overflow

5 Answers 5 ... In general, there are two ways to speed up VBA code: ... In addition to the tweaks suggested by Storax, your code is slow because ... Resultsfromthe2022DeveloperSurveyarenowavailable Home Public Questions Tags Users Companies Collectives ExploreCollectives Teams StackOverflowforTeams –Startcollaboratingandsharingorganizationalknowledge. CreateafreeTeam WhyTeams? Teams CreatefreeTeam Collectives™onStackOverflow Findcentralized,trustedcontentandcollaboratearoundthetechnologiesyouusemost. Learnmore Teams Q&Aforwork Connectandshareknowledgewithinasinglelocationthatisstructuredandeasytosearch. Learnmore HowToSpeedUpVBACode AskQuestion Asked 4years,7monthsago Modified 1year,2monthsago Viewed 38ktimes 11 9 IhaveanexcelfilewithaVBAcode(Notwrittenbyme) Howthiscodeworksisuserentersa6digitnumberinauserform,theVBAthenchecksanothersheetandifthis6digitnumberispresentontheworksheet. Ifitdoes,itchangesthestage,butifitdoesn'titaddsthis6digitnumbertotheworksheet Itusedtoworkperfectly,butnowbecausetheexcelfilehasgrowninthenumberofrows,almost6000rows,thiscodeisbecomeveryslow,takesupto20secondstoupdatethesheet Cansomeonepleasehelpmespeedthiscodeup,orsuggestanotherwaytoacheiveit Thecodeisbelow PrivateSubcmdPSDUdate_Click() Dimx If(Me.PSDUDateRow="")+(Me.PSDStageCB.ListIndex=-1)ThenExitSub WithSheets("psdatastagecals").ListObjects("PSDataStageCals") x=Application.Match(Val(Me.PSDUDateRow),.ListColumns(1).DataBodyRange,0) IfIsNumeric(x)Then .ListRows(x).Range(2)=Me.PSDStageCB.Value Else .ListRows.Add.Range=Array(Val(Me.PSDUDateRow),Me.PSDStageCB) EndIf EndWith Me.PSDUDateRow.Value="" Me.PSDStageCB.Value="" Me.PSDUDateRow.SetFocus EndSub Thanksinadvance Rahul vbaexcel Share Follow askedNov3,2017at6:29 rahul_ferns76rahul_ferns76 11111goldbadge11silverbadge55bronzebadges 4 Ifthecodedoesn'tthrowanyerrorsbutissimplyslow,itdoesn'tbelonghere.Pleaseconsidermovingittostackexchange.comforreviewandimprovementsuggestions. – KostasK. Nov3,2017at7:14 @KostasK.IassumeyoumeantCodeReview.SE? – Vegard Nov3,2017at8:28 Correct.codereview.stackexchange.com/questions/tagged/vba – KostasK. Nov3,2017at8:29 SoPSDataStageCalshasjust6000rows?WhatisPSDUDateRow?Asinglenumber,oranentirerowcomprisingofmultiplecolumns?ArethereformulasinthefilethatreferencePSDataStageCals?(Ifso,theseformulaswillberecalculatedbecauseyouareaddinganewListRow,andiftheyarecomputationallyexpensiveorareatthestartoflongcalculationchainsthenthiscouldexplainmostofyourdelay).DoyouhavevolatilefunctionsinyourfilesuchasOFFSET,NOW,TODAY,orINDIRECT? – jeffreyweir Nov3,2017at21:18 Addacomment  |  5Answers 5 Sortedby: Resettodefault Highestscore(default) Trending(recentvotescountmore) Datemodified(newestfirst) Datecreated(oldestfirst) 14 Youcouldturnoffscreenupdating,automaticcalculationsetc Application.Calculation=xlCalculationManual Application.ScreenUpdating=False Application.DisplayStatusBar=False Application.EnableEvents=False ‘Placeyourmacrocodehere Application.Calculation=xlCalculationAutomatic Application.ScreenUpdating=True Application.DisplayStatusBar=True Application.EnableEvents=True Share Follow answeredNov3,2017at9:20 StoraxStorax 9,44433goldbadges1414silverbadges2828bronzebadges Addacomment  |  8 Ingeneral,therearetwowaystospeedupVBAcode: Writegoodcode,thatdoesnotuseSelect,Activate,ActiveCell,Selectionetc-HowtoavoidusingSelectinExcelVBA Refertotheseroutinesonthestartandontheendofthecode: PublicSubOnEnd() Application.ScreenUpdating=True Application.EnableEvents=True Application.AskToUpdateLinks=True Application.DisplayAlerts=True Application.Calculation=xlAutomatic ThisWorkbook.Date1904=False Application.StatusBar=False EndSub PublicSubOnStart() Application.ScreenUpdating=False Application.EnableEvents=False Application.AskToUpdateLinks=False Application.DisplayAlerts=False Application.Calculation=xlAutomatic ThisWorkbook.Date1904=False ActiveWindow.View=xlNormalView EndSub (Forimprovementideas,kindlymakePullRequest) IthinkthatCalculationshouldbealwayssettoxlAutomatic,asfarasifyouneedxlCalculationManualtospeedup,itisagoodideatorefactorthecode.Furthermoremanualcalculationistoorisky. ThesamegoesforDate1904-itisalwayssettoFalse. Share Follow editedApr1,2021at16:28 answeredMar27,2018at13:59 VityataVityata 41.4k77goldbadges5050silverbadges8787bronzebadges Addacomment  |  6 InadditiontothetweakssuggestedbyStorax,yourcodeisslowbecauseyouarebringingdatacell-by-cellovertheExcel/VBAdivide. Furthermore,youcanradicallyspeedupyourMATCHfunctionbyusingtheBinaryversionofit.Haveareadofhttp://dailydoseofexcel.com/archives/2015/04/23/how-much-faster-is-the-double-vlookup-trick/andalsotrytominimisetheamountofindividualtransfersyoudoacrosstheExcel/VBAdividebyeitherperformingthelookupsentirelywithintheExcelsheet(byusingVBAtowritetheformulainthesheetandexecuteitthere)orbybringingallthedataintoVBAinonegousingvariantarrays,performingyourlogic,andthenbydumpingitbackinonego.Google"EfficientwaytotransferdatabetweenExcelandVBA"orsomethingsimilar.AlsocheckoutanyarticlesfromCharlesWilliamsonthesubject. Share Follow answeredNov3,2017at10:39 jeffreyweirjeffreyweir 4,50611goldbadge1515silverbadges2626bronzebadges Addacomment  |  1 Idon'tseeanythingwrongwithyourcode.PerhapstheWorkbookitselfistheculprit.Isitbecominghugeandslowtoopen? Ifyes,trysearchingfor'cleanupexcelfile'. SomeresultsIfound: https://excelfilecleaner.codeplex.com/ https://support.microsoft.com/en-us/help/3070372/how-to-clean-up-an-excel-workbook-so-that-it-uses-less-memory Share Follow answeredNov3,2017at9:29 iDevlopiDevlop 24.1k1111goldbadges8686silverbadges142142bronzebadges Addacomment  |  1 WhencrunchinglargechunksofdatainExcelthatrequiresfrequentreferencingofcells,it’salwaysmuchmuchfastertocopythedatatoanarray(copytheentireworksheetifnecessary),processthedatawithinthearray,andthenwritebacktotheworksheetifnecessary.Copyingdatafromworksheettoarrayisaonelinecommandthatisveryveryfast.Samewitharraytoworksheet.Relativelyspeaking,referencingcellsisaverytimeconsumingprocesscomparedwithreferencingelementsofanarray. Share Follow answeredJan25,2020at22:59 TimTim 2122bronzebadges Addacomment  |  YourAnswer ThanksforcontributingananswertoStackOverflow!Pleasebesuretoanswerthequestion.Providedetailsandshareyourresearch!Butavoid…Askingforhelp,clarification,orrespondingtootheranswers.Makingstatementsbasedonopinion;backthemupwithreferencesorpersonalexperience.Tolearnmore,seeourtipsonwritinggreatanswers. Draftsaved Draftdiscarded Signuporlogin SignupusingGoogle SignupusingFacebook SignupusingEmailandPassword Submit Postasaguest Name Email Required,butnevershown PostYourAnswer Discard Byclicking“PostYourAnswer”,youagreetoourtermsofservice,privacypolicyandcookiepolicy Nottheansweryou'relookingfor?Browseotherquestionstaggedvbaexceloraskyourownquestion. TheOverflowBlog CelebratingtheStackExchangesitesthatturnedtenyearsoldinSpring2022  GitHubCopilotishere.Butwhat’stheprice?(Ep.457) FeaturedonMeta Testingnewtrafficmanagementtool Duplicatedvotesarebeingcleanedup Trending:Anewanswersortingoption AskWizardTestResultsandNextSteps Updatedbuttonstylingforvotearrows:currentlyinA/Btesting Linked 0 BeginnerVBAforExcel:howcanIspeedupmycode? 605 HowtoavoidusingSelectinExcelVBA 2 VBA-HowtomakeForloopfaster 0 Efficientwayofexportingarangeofcellsspanningtwocolumns 0 Coderunningmoreslowlythanonotherfiles/dates 1 LoopingthroughWorkbooksandCopyaDynamicrangetoMasterWorkbook 0 ConvertrowsinExceltoXML,inVBAcode,andposttoawebservice-needstobeeffecient 0 Macroistakingverylongtimetoloopovernearly700lines 1 Loopoptimization/Customization 0 Excel-slowVBAexecutionofsimpleloop Seemorelinkedquestions Related 2086 HowdoIcreateanExcel(.XLSand.XLSX)fileinC#withoutinstallingMicrosoftOffice? 551 IsthereawaytocrackthepasswordonanExcelVBAProject? 167 ExcelVBAAppstopsspontaneouslywithmessage"Codeexecutionhasbeenhalted" 605 HowtoavoidusingSelectinExcelVBA 0 Howtoimporttheentirerow(s)containingcurrent(today's)datefromaexcelfileintoanotherexcelfileautomaticallywithoutopeningwithVBA 2 Excelstopsrespondingwhileexecutingloop 0 Howtosolverun-timeerrorinVBAExcel? HotNetworkQuestions Landolocaldevsitetabslinktolive WhydidoldconsoleshavespecialRAMdedicatedforaspecifictask? Howcomeaturbopropcan'treachspeedsashighasaturbofan? Howwouldthefightingofmedievalstylewarsbealteredbythepresenceofapredatorspecies? IsthecategoryofsetsandfunctionsKleisli? ro1000annu1000era50en100o501ng Whyaresomeembeddedregisterspasswordprotected? Whocontrolsmoodlightingonacommercialairplane? Isthereanysign"theWest"isdeliberatelytryingtoprolongfightinginUkraineforthesakeoffighting? AfterthefallofRoevs.Wade,arewomenatriskofcriminalprosecution? Whatcanbedonewithsamesampleswithdifferenttarget? WhyistheUSDeputyNationalSecurityAdvisorforInternationalEconomicscalleda"sherpa"? Howdoessoapunderminethechurchin"AConnecticutYankee"? Instrumentationampgivingonlypositiveoutput(with+and-powersupplies) Whatdoes"towithinlessthanaboutthirtySchwarzschildradii"mean? Novelaboutincompetentspacepirates HowshallIproceedifmycarmaycontainillegaldrugsanddrugparaphernaliabeforeenteringCanadabycarfromtheUS? Doingalittletrollingwiththemicrowavetimer Whenwouldonecarryaswordonashoulderwithgripupwards? Howwouldyoustandardizeon-callexpectationsacrosssoftwareteamswhenthey'vedivergedatthesamesalarylevel? Ascifibookcontaininganartificially-madeworldwithweirdcentaurs Isthisproofthatmasslessobjectscannotbecharged? Cansiliconecaulkfilla1cmshowergap? TL072POpampbehaviour morehotquestions Questionfeed SubscribetoRSS Questionfeed TosubscribetothisRSSfeed,copyandpastethisURLintoyourRSSreader. lang-vb Yourprivacy Byclicking“Acceptallcookies”,youagreeStackExchangecanstorecookiesonyourdeviceanddiscloseinformationinaccordancewithourCookiePolicy. Acceptallcookies Customizesettings