Optimize Slow VBA Code. Speed Up Efficient VBA Code/Macros

Optimize Excel VBA code with these tips and tricks for Speeding Up Excel Macros. ExcelVBAVideoTraining/EXCELDASHBOARDREPORTS OptimizeSlowVBACode.SpeedingUpSlowExcelVBACode   Backto:ExcelVBA .GotanyExcel/VBAQuestions?Excel Help Speedupcodeandstopscreenflickering: SubNoScreenRePainting() Application.ScreenUpdating=False 'Yourcodehere. Application.ScreenUpdating=True EndSub Preventingcalculationwhileexecutingcode: SubNoCalculations() Application.Calculation=xlCalculationManual 'Yourcodehere. Application.Calculation=xlCalculationAutomatic EndSub SpeedingupcodeifyouhaveWorksheetorWorkbookEvents.Alsostops endlessloopsinEvents SubStopAllEvents() Application.EnableEvents=False 'Yourcodehere. Application.EnableEvents=True EndSub UsetheWithStatementwhenworkingwithObjects. SubWithARange() WithRange("A1") .Value=100 .Font.Bold=True .Interior.ColorIndex=6 .CopyDestination:=Range("B1") EndWith EndSub UseVbNullStringinsteadof=""WhenneedingtodefaultaStringvariable backtoit'sdefaultof""use: SubEmptyText() DimstrWordsAsString strWords="Cats" MsgBoxstrWords strWords=vbNullString MsgBoxstrWords EndSub InsertingaRelativeformulaintoarangeofcells:FasterthanAutoFill orCopy. SubNoAutoFillOrCopy() Range("A1:A200").FormulaR1C1="=SUM(RC[1]:RC[5])" EndSub Tip:Togetaformula,typeitinanycellthenselectthecell,go Tools>Macro>RecordnewmacroandrecordamacropushingF2thenEnter. AvoidtheuseofCopyandPastewheneverPossible: SubNoCopyAndPaste() 'Insteadof: Sheet1.Range("A1:A200").Copy Sheet2.Range("B1").pasteSpecial Application.CutCopyMode=False'ClearClipboard 'Use: 'By-passestheClipboard Sheet1.Range("A1:A200").CopyDestination:=Sheet2.Range("B1") 'Or,ifonlyvaluesareneeded: Sheet2.Range("B1:B200").Value=Sheet1.Range("A1:A200").Value 'Or,ifonlyformulaeareneeded: Sheet2.Range("B1:B200").Formula=Sheet1.Range("A1:A200").Formula 'SeealsoFormulaArrayandFormulaR1C1etc 'Insteadof: Sheet1.Range("A1:A200").Copy Sheet1.Range("A1:A200").PasteSpecialxlPasteValues Application.CutCopyMode=False'ClearClipboard 'Use: Sheet1.Range("A1:A200")=Sheet1.Range("A1:A200").Value EndSub Alwaysdeclareyourvariablescorrectly! Toquicklyviewavariablesdefinition: Selectthevariablethatyouwantthedefinitionfor. GotoView>Definition(Shift+F2) Toreturntoyourpreviousposition: GotoView>LastPostition(Ctrl+Shift+F2). ReleasememoryfromObjectvariables: SubReleaseObjectMemory() 'CouldbeanyvariableoftheObjecttype DimwSheetasWorksheet 'SetObjectvariable SetwSheet=Sheet1 'Yourcodehere. 'Releasememory SetwSheet=Nothing EndSub Don'tgetcaughtintheLoop. FollowthislinktoseewhyLoopsshould(andusuallycan)beavoided. AvoidIf,Elsewheneverpossible MoreoftenthannotpeoplewoulduseanIf,ElseStatementtotestwhetheracondition isTRUEorFALSE.Thereishoweveraslightlyfaster(andlesstyping)method.The firstexampleshowsthecommonmethod,whilethesecondshowsafastermethod.Of courseinsuchasmallexamplethedifferenceisnotnoticeable. SubTrueOrFalseSlower() DimbYesNoAsBoolean DimiAsInteger Ifi=5Then bYesNo=True Else bYesNo=False EndIf MsgBoxbYesNo EndSub Here'sabetterway! SubTrueOrFalseFaster() DimbYesNoAsBoolean DimiAsInteger bYesNo=(i=5) MsgBoxbYesNo EndSub AnothercommonneedistotoggleavariablebetweenTrueandFalsedependingon acondition.Forexample: SubToggleTrueOrFalseSlower() DimbYesNoAsBoolean IfbYesNo=FalseThen bYesNo=True Else bYesNo=False EndIf MsgBoxbYesNo EndSub Here'samuchbetterway SubToggleTrueOrFalseFaster() DimbYesNoAsBoolean bYesNo=NotbYesNo MsgBoxbYesNo EndSub CheckoutthisMicrosoftTechlinkforsomeniftyVBAmacrotipstoimproveperformance. Excel DashboardReports&ExcelDashboardCharts50%Off BecomeanExcelUserAffiliate&EarnMoney Special!Free Choiceof CompleteExcelTrainingCourseOR ExcelAdd-insCollection onallpurchasestotalingover$64.00.ALL purchasestotaling over$150.00getsyouBOTH!PurchasesMUSTbemadevia thissite.Sendpaymentproofto[email protected]31daysafterpurchase date. InstantDownloadandMoneyBackGuaranteeonMostSoftware ExcelVBAVideoTraining/EXCELDASHBOARDREPORTS ExcelTraderPackage TechnicalAnalysisinExcelWith$139.00ofFREEsoftware! Microsoft®andMicrosoftExcel®areregisteredtrademarksofMicrosoftCorporation.OzGridisinnowayassociatedwithMicrosoft Someofourmorepopularproductsarebelow...ConvertExcelSpreadsheetsToWebpages|TradingInExcel|ConstructionEstimators|FinanceTemplates&Add-insBundle|Code-VBA|Smart-VBA|Print-VBA|ExcelDataManipulation&Analysis|ConvertMSOfficeApplicationsTo......|AnalyzerExcel|DownloaderExcel |MSSQLMigration Toolkit| MonteCarloAdd-in| Excel CostingTemplates