9 quick tips to improve your VBA macro performance

As part of our work to help enterprises upgrade to Office 365 ProPlus, we have found that some users have been experiencing slow running VBA, which can be. Microsoft TechCommunity HomeCommunityHubsCommunityHubsCommunityHubsHomeProductsSpecialTopicsVideoHubCloseMostActiveHubsMicrosoftTeamsMicrosoftExcelWindowsSecurity,ComplianceandIdentityOffice365SharePointWindowsServerAzureExchangeMicrosoft365MicrosoftEdgeInsider.NETSharingbestpracticesforbuildinganyappwith.NET.MicrosoftFastTrackBestpracticesandthelatestnewsonMicrosoftFastTrackMicrosoftVivaTheemployeeexperienceplatformtohelppeoplethriveatworkMostActiveHubsITOpsTalkCoreInfrastructureandSecurityMicrosoftLearnEducationSectorMicrosoft365PnPAIandMachineLearningMicrosoftMechanicsHealthcareandLifeSciencesSmallandMediumBusinessPublicSectorInternetofThings(IoT)AzurePartnerCommunityExpandyourAzurepartner-to-partnernetworkMicrosoftTechTalksBringingITProstogetherthroughIn-Person&VirtualeventsMVPAwardProgramFindoutmoreabouttheMicrosoftMVPAwardProgram.VideoHubAzureExchangeMicrosoft365Microsoft365BusinessMicrosoft365EnterpriseMicrosoftEdgeMicrosoftOutlookMicrosoftTeamsSecuritySharePointWindowsBrowseAllCommunityHubsBlogsBlogsEventsEventsEventsHomeMicrosoftIgniteMicrosoftBuildCommunityEventsMicrosoftLearnMicrosoftLearnHomeCommunityBlogAzureDynamics365Microsoft365Security,Compliance&IdentityPowerPlatformGithubTeams.NETLoungeLounge861KMembers5,466Online246KDiscussions Search cancel Turnonsuggestions Auto-suggesthelpsyouquicklynarrowdownyoursearchresultsbysuggestingpossiblematchesasyoutype. Showingresultsfor  Show  only  | Searchinsteadfor  Didyoumean:  SignIn SignIn cancel Turnonsuggestions Auto-suggesthelpsyouquicklynarrowdownyoursearchresultsbysuggestingpossiblematchesasyoutype. Showingresultsfor  Show  only  | Searchinsteadfor  Didyoumean:  Home Home MicrosoftExcel Excel 9quicktipstoimproveyourVBAmacroperformance 9quicktipstoimproveyourVBAmacroperformance %3CLINGO-SUB%20id%3D%22lingo-sub-173687%22%20slang%3D%22en-US%22%3E9%20quick%20tips%20to%20improve%20your%20VBA%20macro%20performance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-173687%22%20slang%3D%22en-US%22%3E%3CP%3EAs%20part%20of%20our%20work%20to%20help%20enterprises%20upgrade%20to%20Office%20365%20ProPlus%2C%20we%20have%20found%20that%20some%20users%20have%20been%20experiencing%20slow%20running%20VBA%2C%20which%20can%20be%20frustrating.%20The%20good%20news%20is%20that%20there%20are%20steps%20which%20can%20be%20taken%20to%20improve%20performance.%3C%2FP%3E%0A%3CP%3EThis%20post%20aims%20to%20raise%20awareness%20of%20the%20steps%20that%20you%20can%20take%20to%20improve%20the%20performance%20of%20your%20macros%2C%20whether%20you%20are%20an%20end%20user%2C%20IT%20admin%2C%20or%20developer.%20We%E2%80%99ve%20collected%20the%20following%20steps%20from%20blog%20posts%2C%20Microsoft%20field%20engineers%2C%20and%20Microsoft%20MVPs%20like%20Charles%20Williams%20and%20Jan%20Karel%20Pieterse.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E1.%20Turn%20off%20everything%20but%20the%20essentials%20in%20VBA%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EOne%20of%20the%20first%20things%20to%20do%20when%20speeding%20up%20VBA%20code%20is%20to%20turn%20off%20unnecessary%20features%20such%20as%20animations%2C%20screen%20updating%2C%20automatic%20calculations%20and%20events%20while%20your%20macro%20is%20running.%20These%20features%20can%20add%20extra%20overhead%20and%20slow%20down%20the%20macro%2C%20particularly%20if%20the%20macro%20is%20modifying%20many%20cells%20and%20triggering%20lots%20of%20screen%20updates%20and%20recalculations.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20below%20code%20sample%20shows%20you%20how%20to%20enable%2Fdisable%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EManual%20calculations%3C%2FLI%3E%0A%3CLI%3EScreen%20updates%3C%2FLI%3E%0A%3CLI%3EAnimations%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CPRE%3EOption%20Explicit%0ADim%20lCalcSave%20As%20Long%0ADim%20bScreenUpdate%20As%20Boolean%0ASub%20SwitchOff(bSwitchOff%20As%20Boolean)%0A%20%20Dim%20ws%20As%20Worksheet%0A%20%20%20%20%0A%20%20With%20Application%0A%20%20%20%20If%20bSwitchOff%20Then%20%0A%0A%20%20%20%20%20%20'%20OFF%20%0A%20%20%20%20%20%20lCalcSave%20%3D%20.Calculation%0A%20bScreenUpdate%20%3D%20.ScreenUpdating%0A%20%20%20%20%20%20.Calculation%20%3D%20xlCalculationManual%0A%20%20%20%20%20%20.ScreenUpdating%20%3D%20False%0A%20%20%20%20%20%20.EnableAnimations%20%3D%20False%0A%20%20%20%20%20%20%0A%20%20%20%20%20%20'%0A%20%20%20%20%20%20'%20switch%20off%20display%20pagebreaks%20for%20all%20worksheets%0A%20%20%20%20%20%20'%0A%20%20%20%20%20%20For%20Each%20ws%20In%20ActiveWorkbook.Worksheets%0A%20%20%20%20%20%20%20%20ws.DisplayPageBreaks%20%3D%20False%0A%20%20%20%20%20%20Next%20ws%0A%20%20%20%20Else%0A%20%0A%20%20%20%20%20%20'%20ON%0A%20%20%20%20%20%20If%20.Calculation%20%26lt%3B%26gt%3B%20lCalcSave%20And%20lCalcSave%20%26lt%3B%26gt%3B%200%20Then%20.Calculation%20%3D%20lCalcSave%0A%20%20%20%20%20%20.ScreenUpdating%20%3D%20bScreenUpdate%0A%20%20%20%20%20%20.EnableAnimations%20%3D%20True%0A%20%20%20%20%20%20%0A%20%20%20%20End%20If%0A%20%20End%20With%0AEnd%20Sub%0A%0ASub%20Main()%0A%20%20SwitchOff(True)%20%E2%80%98%20turn%20off%20these%20features%0A%20%20MyFunction()%20%E2%80%98%20do%20your%20processing%20here%0A%20%20SwitchOff(False)%20%E2%80%98%20turn%20these%20features%20back%20on%0AEnd%20Sub%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E2.%20Disabling%20Office%20animations%20through%20system%20settings%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnimations%20can%20be%20disabled%20across%20Windows%20by%20accessing%20the%26nbsp%3B%3CSTRONG%3EEase%20of%20Access%20Center%3C%2FSTRONG%3E.%3C%2FP%3E%0A%3CP%3EAnimations%20can%20be%20disabled%20in%20Excel%20specifically%2C%20under%20the%26nbsp%3B%3CSTRONG%3EAdvanced%3C%2FSTRONG%3E%20or%26nbsp%3B%3CSTRONG%3EEase%20of%20Access%3C%2FSTRONG%3E%20tab%2C%20within%20the%26nbsp%3B%3CSTRONG%3EFile%20%26gt%3B%20Options%20%3C%2FSTRONG%3Emenu.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20see%20the%20following%20link%20for%20more%20information%3A%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fturn-off-office-animations-9ee5c4d2-d144-4fd2-b670-22cef9fa025a%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fturn-off-office-animations-9ee5c4d2-d144-4fd2-b670-22cef9fa025a%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E3.%20Disabling%20Office%20animations%20through%20registry%20settings%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EOffice%20animations%20can%20be%20disabled%20across%20multiple%20computers%20by%20setting%20the%20appropriate%20registry%20key%20via%20a%20group%20policy%20setting.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3EHIVE%3A%20HKEY_CURRENT_USER%0AKey%20Path%3A%20Software%5CMicrosoft%5COffice%5C16.0%5CCommon%5CGraphics%0AKey%20Name%3A%20DisableAnimations%0AValue%20type%3A%20REG_DWORD%0AValue%20data%3A%26nbsp%3B0x00000001%20(1)%3C%2FPRE%3E%0A%3CP%3E%3CEM%3EWarning%3A%20Using%20Registry%20Editor%20incorrectly%20can%20cause%20serious%2C%20system-wide%20problems%20that%20may%20require%20you%20to%20re-install%20Windows%20to%20correct%20them.%20Microsoft%20cannot%20guarantee%20that%20any%20problems%20resulting%20from%20the%20use%20of%20Registry%20Editor%20can%20be%20solved.%20Use%20this%20tool%20at%20your%20own%20risk.%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E4.%20Removing%20unnecessary%20selects%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EThe%20select%20method%20is%20common%20to%20see%20in%20VBA%20code%2C%20however%20it%20is%20often%20added%20to%20the%20macro%20where%20it%20isn%E2%80%99t%20needed.%20Select%20can%20trigger%20cell%20events%20such%20as%20animations%20and%20conditional%20formatting%20which%20slow%20the%20macro%20down%2C%20so%20removing%20unnecessary%20selects%20can%20significantly%20speed%20up%20your%20macro.%3C%2FP%3E%0A%3CP%3EThe%20following%20example%20shows%20the%20code%20before%20and%20after%20making%20the%20change%20to%20remove%20unnecessary%20selects.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CEM%3EBefore%3C%2FEM%3E%3C%2FP%3E%0A%3CPRE%3ESheets(%22Order%20Details%22).Select%0AColumns(%22AC%3AAH%22).Select%0ASelection.ClearContents%3C%2FPRE%3E%0A%3CP%3E%3CEM%3EAfter%3C%2FEM%3E%3C%2FP%3E%0A%3CPRE%3ESheets(%22Order%20Details%22).Columns(%22AC%3AAH%22).ClearContents%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E5.%20Using%20the%20With%20statement%20to%20read%20object%20properties%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EWhen%20working%20with%20objects%2C%20use%20the%20With%20statement%20to%20reduce%20the%20number%20of%20times%20object%20properties%20are%20read.%20The%20following%20example%20shows%20the%20code%20before%20and%20after%20making%20the%20change%20to%20use%20the%20With%20statement.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CEM%3EBefore%3C%2FEM%3E%3C%2FP%3E%0A%3CPRE%3ERange(%22A1%22).Value%20%3D%20%E2%80%9CHello%E2%80%9D%0ARange(%22A1%22).Font.Name%20%3D%20%E2%80%9CCalibri%E2%80%9D%0ARange(%22A1%22).Font.Bold%20%3D%20True%0ARange(%22A1%22).HorizontalAlignment%20%3D%20xlCenter%3C%2FPRE%3E%0A%3CP%3E%3CEM%3EAfter%3C%2FEM%3E%3C%2FP%3E%0A%3CPRE%3EWith%20Range(%22A1%22)%0A%20%20.Value2%20%3D%20%E2%80%9CHello%E2%80%9D%20%0A%20%20.HorizontalAlignment%20%3D%20xlCenter%0A%20%20%20%20With%20.Font%0A%20%20%20%20%20%20.Name%20%3D%20%E2%80%9CCalibri%E2%80%9D%0A%20%20%20%20%20%20.Bold%20%3D%20True%0A%20%20%20%20End%20With%0AEnd%20With%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E6.%20Using%20ranges%20and%20arrays%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EReading%20and%20writing%20to%20cells%20in%20Excel%20from%20VBA%20is%20expensive.%20There%20is%20an%20overhead%20that%20is%20incurred%20every%20time%20data%20moves%20between%20VBA%20and%20Excel.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F30612i88DD1220B0068F13%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%222018-03-20%2014_39_10-Make%20your%20VBA%20run%20100X%20faster%20%20-%20%20Protected%20View%20-%20PowerPoint.png%22%20title%3D%222018-03-20%2014_39_10-Make%20your%20VBA%20run%20100X%20faster%20%20-%20%20Protected%20View%20-%20PowerPoint.png%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EThe%20mountain%20between%20Excel%20and%20VBA%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EThis%20means%20that%20you%20should%20try%20to%20reduce%20the%20number%20of%20times%20you%20pass%20data%20between%20VBA%20and%20Excel.%20This%20is%20where%20ranges%20are%20useful.%20Instead%20of%20reading%20and%20writing%20to%20each%20cell%20individually%20in%20a%20loop%2C%20read%20the%20entire%20range%20into%20an%20array%20at%20the%20start%2C%20loop%20through%20the%20array%2C%20and%20then%20write%20the%20entire%20array%20back%20at%20the%20end.%20The%20following%20example%20code%20shows%20how%20a%20range%20can%20be%20used%20to%20read%20and%20write%20the%20values%20once%2C%20instead%20of%20reading%20each%20cell%20individually.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3EDim%20vArray%20As%20Variant%0ADim%20iRow%20As%20Integer%0ADim%20iCol%20As%20Integer%0ADim%20dValue%20As%20Double%0AvArray%20%3D%20Range(%22A1%3AC10000%22).Value2%20%E2%80%98%20read%20all%20the%20values%20at%20once%20from%20the%20Excel%20cells%2C%20put%20into%20an%20array%20%0A%0AFor%20iRow%20%3D%20LBound(vArray%2C%201)%20To%20UBound(vArray%2C%201)%0A%20%20For%20iCol%20%3D%20LBound(vArray%2C%202)%20To%20UBound(vArray%2C%202)%0A%20%20%20%20dValue%20%3D%20vArray%20(iRow%2C%20iCol)%0A%20%20%20%20If%20dValue%20%26gt%3B%200%20Then%20%0A%20%20%20%20%20%20dValue%3DdValue*dValue%20%E2%80%98%20Change%20the%20values%20in%20the%20array%2C%20not%20the%20cells%20%0A%20%20%20%20vArray(iRow%2C%20iCol)%20%3D%20dValue%0A%20%20End%20If%0ANext%20iCol%0ANext%20iRow%0ARange(%22A1%3AC10000%22).Value2%20%3D%20vArray%20%E2%80%98%20writes%20all%20the%20results%20back%20to%20the%20range%20at%20once%3C%2FPRE%3E%0A%3CP%3E%3CSTRONG%3E%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E7.%20Use%20.Value2%20instead%20of%20.Text%20or%20.Value%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EThere%20are%20different%20ways%20that%20you%20can%20retrieve%20values%20from%20a%20cell%2C%20and%20which%20property%20you%20use%20can%20make%20a%20different%20in%20the%20performance%20of%20your%20code.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E.Text%3C%2FSTRONG%3E%20is%20commonly%20used%20to%20retrieve%20the%20value%20of%20a%20cell%20%E2%80%93%20it%20returns%20the%20formatted%20value%20of%20a%20cell.%20Getting%20the%20formatting%20of%20a%20cell%20is%20more%20complex%20than%20just%20retrieving%20a%20value%2C%20and%20makes%20.Text%20quite%20slow.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E.Value%3C%2FSTRONG%3E%20is%20an%20improvement%20over%20.Text%2C%20as%20this%20mostly%20gets%20the%20value%20from%20the%20cell%2C%20without%20formatting.%20However%20for%20cells%20formatted%20as%20a%20date%20or%20currency%2C%20.Value%20will%20return%20a%20VBA%20date%20or%20VBA%20currency%20(which%20may%20truncate%20decimal%20places).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E.Value2%20%3C%2FSTRONG%3Egives%20the%20underlying%20value%20of%20the%20cell.%20As%20it%20involves%20no%20formatting%2C%20.Value2%20is%20faster%20than%20.Value.%20.Value2%20is%20faster%20than%20.Value%20when%20processing%20numbers%20(there%20is%20no%20significant%20difference%20with%20text)%2C%20and%20is%20much%20faster%20using%20a%20variant%20array.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20a%20more%20detailed%20explanation%2C%20please%20see%20Charles%20William%E2%80%99s%20blog%20post%2C%20%E2%80%9C%3CSTRONG%3ETEXT%20vs%20VALUE%20vs%20VALUE2%3C%2FSTRONG%3E%E2%80%9D%3A%20%3CA%20href%3D%22https%3A%2F%2Ffastexcel.wordpress.com%2F2011%2F11%2F30%2Ftext-vs-value-vs-value2-slow-text-and-how-to-avoid-it%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Ffastexcel.wordpress.com%2F2011%2F11%2F30%2Ftext-vs-value-vs-value2-slow-text-and-how-to-avoid-it%2F%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E8.%20Bypass%20the%20clipboard%20(copy%20and%20paste)%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EWhen%20you%20use%20the%20Macro%20Recorder%20to%20record%20operations%20that%20use%20copy%20and%20paste%2C%20the%20code%20will%20use%20the%20copy%20and%20paste%20methods%20by%20default.%20However%2C%20within%20VBA%20code%2C%20it%20is%20much%20faster%20to%20bypass%20the%20clipboard%20and%20use%20internal%20operations%20instead.%20By%20default%2C%20copying%20will%20copy%20everything%2C%20including%20formulas%2C%20values%20and%20formatting.%20You%20can%20make%20copying%20faster%20by%20only%20copying%20values%20or%20formulas%2C%20without%20the%20formatting.%20The%20following%20example%20shows%20the%20code%20before%20and%20after%20making%20the%20change%20to%20bypass%20the%20clipboard.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CEM%3EBefore%3C%2FEM%3E%3C%2FP%3E%0A%3CPRE%3ERange(%22A1%22).Select%0ASelection.Copy%0ARange(%22A2%22).Select%0AActiveSheet.Paste%3C%2FPRE%3E%0A%3CP%3E%3CEM%3EAfter%3C%2FEM%3E%3C%2FP%3E%0A%3CPRE%3E%E2%80%98%20Approach%201%3A%20copy%20everything%20(formulas%2C%20values%20and%20formatting%0ARange(%22A1%22).Copy%20Destination%3A%3DRange(%22A2%22)%0A%0A%E2%80%98%20Approach%202%3A%20copy%20values%20only%0ARange(%22A2%22).Value2%20%3D%20Range(%22A1%22).Value2%0A%0A%E2%80%98%20Approach%203%3A%20copy%20formulas%20only%0ARange(%22A2%22).Formula%20%3D%20Range(%22A1%22).Formula%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20still%20find%20that%20a%20macro%20takes%20longer%20than%20expected%20to%20execute%20many%20individual%20copy%20and%20paste%20operations%2C%20you%20may%20want%20to%20apply%20the%20following%20hot%20fix%3A%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-in%2Fhelp%2F2817672%2Fmacro-takes-longer-than-expected-to-execute-many-individual-copy-and-p%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-in%2Fhelp%2F2817672%2Fmacro-takes-longer-than-expected-to-execute-many-individual-copy-and-p%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E9.%20Use%20Option%20Explicit%20to%26nbsp%3Bcatch%20undeclared%20variables%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EOption%20Explicit%20is%20one%20of%20the%20available%20Module%20directives%26nbsp%3Bin%20VBA%20that%20instructs%20VBA%20how%20to%20treat%20the%20code%20within%20the%20code%20module.%26nbsp%3B%3C%2FSPAN%3ESetting%20Option%20Explicit%20requires%20all%20variables%20to%20be%20declared%20and%20will%20give%20compile%20errors%20if%20an%20undeclared%20variable%20is%20used.%20This%20helps%20catch%20incorrectly%20typed%20variable%20names%20and%20improves%20performance%20with%20all%20variable%20types%20being%20defined%20at%20compile%20time%2C%20instead%20of%20being%20inferred%20at%20runtime.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EThis%20can%20be%20set%20by%20typing%3A%20Option%20Explicit%20at%20the%20top%20of%20each%20module%20in%20your%20project%20or%20by%20checking%20the%20%22Require%20Variable%20Declaration%22%20option%20under%20Tools%20-%26gt%3B%20Options%20in%20the%20VBA%20editor.%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EAdditional%20Details%20on%20Module%20directives%20can%20be%20found%20here%3A%20%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fdotnet%2Fvisual-basic%2Flanguage-reference%2Fstatements%2Foption-explicit-statement%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%3CSPAN%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fdotnet%2Fvisual-basic%2Flanguage-reference%2Fstatements%2Foption-explicit-statement%3C%2FSPAN%3E%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EConclusion%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EWe%20hope%20that%20this%20has%20helped%20highlight%20some%20of%20the%20ways%20that%20you%20can%20make%20your%20macros%20run%20faster.%20We%E2%80%99re%20sure%20that%20we%20haven%E2%80%99t%20covered%20everything%2C%20so%20please%20comment%20below%20with%20any%20other%20tips%20or%20tricks%20to%20improve%20the%20performance%20of%20your%20macros%20in%20Excel.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EFurther%20reading%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fblogs.office.com%2Fen-us%2F2009%2F03%2F12%2Fexcel-vba-performance-coding-best-practices%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fblogs.office.com%2Fen-us%2F2009%2F03%2F12%2Fexcel-vba-performance-coding-best-practices%2F%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fdatapigtechnologies.com%2Fblog%2Findex.php%2Ften-things-you-can-do-to-speed-up-your-excel-vba-code%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fdatapigtechnologies.com%2Fblog%2Findex.php%2Ften-things-you-can-do-to-speed-up-your-excel-vba-code%2F%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.ozgrid.com%2FVBA%2FSpeedingUpVBACode.htm%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.ozgrid.com%2FVBA%2FSpeedingUpVBACode.htm%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EFAQs%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EAfter%20using%20the%20new%20macro%20animations%20stopped%20working%2Fit%E2%80%99s%20stuck%20on%20manual%20calculation.%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EIt%20is%20possible%20the%20code%20disables%20the%20various%20settings%2C%20but%20the%20macro%20crashes%20before%20re-enabling%20these%20settings.%20To%20fix%20this%2C%20you%20will%20need%20to%20run%20the%20code%20to%20enable%20these%20settings%20again.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EOther%20resources%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CA%20id%3D%22ember2650%22%20class%3D%22app-aware-link%20ember-view%22%20href%3D%22https%3A%2F%2Fwww.thespreadsheetguru.com%2Fblog%2F2015%2F2%2F25%2Fbest-way-to-improve-vba-macro-performance-and-prevent-slow-code-execution%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fwww.thespreadsheetguru.com%2Fblog%2F2015%2F2%2F25%2Fbest-way-to-improve-vba-macro-performance-and-prevent-slow-code-execution%3C%2FA%3E%26nbsp%3B-%26nbsp%3BThe%20Best%20Way%20To%20Improve%20VBA%20Macro%20Performance%20And%20Prevent%20Slow%20Code%20Execution%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.ozgrid.com%2FVBA%2FSpeedingUpVBACode.htm%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.ozgrid.com%2FVBA%2FSpeedingUpVBACode.htm%3C%2FA%3E%20-%26nbsp%3BOptimize%20Slow%20VBA%20Code.%20Speeding%20Up%20Slow%20Excel%20VBA%20Code%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EAbout%20the%20co-authors%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ECharles%20Williams%3C%2FSTRONG%3E%20founded%20Decision%20Models%20in%201996%20to%20provide%20advanced%20consultancy%2C%20decision%20support%20solutions%2C%20and%20tools%20based%20on%20Microsoft%20Excel%20and%20relational%20databases.%20Charles%20is%20the%20author%20of%20FastExcel%2C%20the%20widely%20used%20Excel%20performance%20profiler%20and%20performance%20toolset%2C%20and%20co-author%20of%20Name%20Manager%2C%20the%20popular%20utility%20for%20managing%20defined%20names.%20For%20more%20information%20about%20Excel%20calculation%20performance%20and%20methods%2C%20memory%20usage%2C%20and%20VBA%20user-defined%20functions%2C%20visit%20the%26nbsp%3B%3CA%20href%3D%22http%3A%2F%2Fwww.decisionmodels.com%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EDecision%20Models%20Web%20site%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EJan%20Karel%20Pieterse%3C%2FSTRONG%3E%20is%20a%20long%20time%20Excel%20MVP%20who%20develops%20custom%20solutions%20focused%20on%20Microsoft%20Office%2C%20with%20deep%20expertise%20in%20Excel%20and%20VBA.%20He%20runs%20the%20website%20%3CA%20href%3D%22http%3A%2F%2Fwww.jkp-ads.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EJKP%20Application%20Development%20Services%20site%3C%2FA%3E%2C%20where%20you%20can%20find%20an%20interesting%20collection%20of%20articles%2C%20training%20events%2C%20and%20utilities.%20For%20a%20good%20overview%20of%20topics%2C%20see%20this%20list%20of%20in-depth%20articles.%20Jan%20develops%20some%20cool%20and%20useful%20utilities%20for%20Excel%2C%20including%20NameManager%2C%20RefTreeAnalyser%2C%20and%20Flexfind.%20You%20can%20find%20a%20full%20list%20on%20the%20downloads%20page.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-173687%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20%26amp%3B%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETips%20and%20Tricks%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-175935%22%20slang%3D%22en-US%22%3ERe%3A%208%20quick%20tips%20to%20improve%20your%20VBA%20macro%20performance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-175935%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20your%20feedback%2C%20Haytham%2C%20it's%20great%20to%20hear%20more%20tips%20that%20can%20improve%20performance.%20I've%20updated%20the%20post%20to%20include%20information%20on%20using%20Option%20Explicit%20to%20catch%20issues%20with%20undeclared%20variables.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-175902%22%20slang%3D%22en-US%22%3ERe%3A%209%20quick%20tips%20to%20improve%20your%20VBA%20macro%20performance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-175902%22%20slang%3D%22en-US%22%3EIIRC%20the%20Application.EnableAnimations%20property%20does%20not%20work%20(but%20we'd%20like%20it%20to!!!)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-174369%22%20slang%3D%22en-US%22%3ERe%3A%208%20quick%20tips%20to%20improve%20your%20VBA%20macro%20performance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-174369%22%20slang%3D%22en-US%22%3E%3CP%3EMark%2C%3CBR%20%2F%3Ere%3A%20use%20of%20With%20statement%3CBR%20%2F%3E%3CBR%20%2F%3EKen%20Getz%20(author%20of%20the%20VBA%20Developers%20Handbook%20-%20many%20years%20ago)%20pointed%20out%2C%20that%20when%20using%20the%20With%20statement%2C%20you%20must%20reduce%20the%20number%20of%20dots%20used%20in%20order%20to%20improve%20efficiency.%3CBR%20%2F%3EOtherwise%20you%20are%20just%20saving%20typing%20time.%3CBR%20%2F%3E'---%3CBR%20%2F%3EJim%20Cone%3CBR%20%2F%3E%3CA%20title%3D%22free%20and%20commercial%20excel%20programs%20at%20dropbox%22%20href%3D%22https%3A%2F%2Fgoo.gl%2FIUQUN2%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgoo.gl%2FIUQUN2%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-174162%22%20slang%3D%22en-US%22%3ERe%3A%208%20quick%20tips%20to%20improve%20your%20VBA%20macro%20performance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-174162%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Mark%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20you%20very%20much%20for%20these%20valuable%20tips!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt's%20noteworthy%20to%20mention%20that%20the%20explicitly%20declare%20all%20variables%20with%20the%20appropriate%20data%20type%20increases%20performance.%3C%2FP%3E%0A%3CP%3EYou%20can%20force%20yourself%20to%26nbsp%3Bdeclare%20all%20variables%20by%20using%20%3CA%20href%3D%22https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Fvba%2Flanguage-reference-vba%2Farticles%2Foption-explicit-statement%3Ff%3D255%26amp%3BMSPPError%3D-2147217396%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EOption%20Explicit%3C%2FA%3E%20statement.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EIn%20addition%2C%20avoid%20using%20the%20%3CSTRONG%3EVariant%3C%2FSTRONG%3E%20data%20type%20as%20much%20as%20possible%20b%3C%2FSPAN%3E%3CSPAN%3Eecause%20it%20will%20cost%20the%20VBA%20interpreter%2Fcompiler%20some%20extra%20steps%20to%20determine%20what%20the%20most%20appropriate%20data%20type%20should%20be%20used!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2545911%22%20slang%3D%22en-US%22%3ERe%3A%209%20quick%20tips%20to%20improve%20your%20VBA%20macro%20performance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2545911%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F109076%22%20target%3D%22_blank%22%3E%40Mark%20Johnson%3C%2FA%3E%2C%20AMAZING!!!%26nbsp%3B%20THANK%20YOU!!!%3C%2FP%3E%3CP%3EThis%20took%20me%20from%20more%20than%2011%20minutes%20to%20less%20than%2010%20seconds%20to%20iterate%20over%2021%20million%20records%20to%20selectively%20update%20them!%3C%2FP%3E%3CP%3E%236%20was%20incredibly%20helpful!!%3C%2FP%3E%3C%2FLINGO-BODY%3E DiscussionOptions SubscribetoRSSFeed MarkDiscussionasNew MarkDiscussionasRead PinthisDiscussionforCurrentUser Bookmark Subscribe PrinterFriendlyPage MarkJohnson Microsoft ‎Mar202018 08:59AM -edited ‎Nov302018 07:35AM MarkasNew Bookmark Subscribe Mute SubscribetoRSSFeed Permalink Print EmailtoaFriend ReportInappropriateContent ‎Mar202018 08:59AM -edited ‎Nov302018 07:35AM 9quicktipstoimproveyourVBAmacroperformance AspartofourworktohelpenterprisesupgradetoOffice365ProPlus,wehavefoundthatsomeusershavebeenexperiencingslowrunningVBA,whichcanbefrustrating.Thegoodnewsisthattherearestepswhichcanbetakentoimproveperformance. Thispostaimstoraiseawarenessofthestepsthatyoucantaketoimprovetheperformanceofyourmacros,whetheryouareanenduser,ITadmin,ordeveloper.We’vecollectedthefollowingstepsfromblogposts,Microsoftfieldengineers,andMicrosoftMVPslikeCharlesWilliamsandJanKarelPieterse.   1.TurnoffeverythingbuttheessentialsinVBA OneofthefirstthingstodowhenspeedingupVBAcodeistoturnoffunnecessaryfeaturessuchasanimations,screenupdating,automaticcalculationsandeventswhileyourmacroisrunning.Thesefeaturescanaddextraoverheadandslowdownthemacro,particularlyifthemacroismodifyingmanycellsandtriggeringlotsofscreenupdatesandrecalculations.   Thebelowcodesampleshowsyouhowtoenable/disable: Manualcalculations Screenupdates Animations OptionExplicit DimlCalcSaveAsLong DimbScreenUpdateAsBoolean SubSwitchOff(bSwitchOffAsBoolean) DimwsAsWorksheet WithApplication IfbSwitchOffThen 'OFF lCalcSave=.Calculation bScreenUpdate=.ScreenUpdating .Calculation=xlCalculationManual .ScreenUpdating=False .EnableAnimations=False ' 'switchoffdisplaypagebreaksforallworksheets ' ForEachwsInActiveWorkbook.Worksheets ws.DisplayPageBreaks=False Nextws Else 'ON If.Calculation<>lCalcSaveAndlCalcSave<>0Then.Calculation=lCalcSave .ScreenUpdating=bScreenUpdate .EnableAnimations=True EndIf EndWith EndSub SubMain() SwitchOff(True)‘turnoffthesefeatures MyFunction()‘doyourprocessinghere SwitchOff(False)‘turnthesefeaturesbackon EndSub   2.DisablingOfficeanimationsthroughsystemsettings   AnimationscanbedisabledacrossWindowsbyaccessingthe EaseofAccessCenter. AnimationscanbedisabledinExcelspecifically,underthe Advancedor EaseofAccesstab,withinthe File>Optionsmenu.    Pleaseseethefollowinglinkformoreinformation:https://support.office.com/en-us/article/turn-off-office-animations-9ee5c4d2-d144-4fd2-b670-22cef9fa...   3.DisablingOfficeanimationsthroughregistrysettings Officeanimationscanbedisabledacrossmultiplecomputersbysettingtheappropriateregistrykeyviaagrouppolicysetting.   HIVE:HKEY_CURRENT_USER KeyPath:Software\Microsoft\Office\16.0\Common\Graphics KeyName:DisableAnimations Valuetype:REG_DWORD Valuedata: 0x00000001(1) Warning:UsingRegistryEditorincorrectlycancauseserious,system-wideproblemsthatmayrequireyoutore-installWindowstocorrectthem.MicrosoftcannotguaranteethatanyproblemsresultingfromtheuseofRegistryEditorcanbesolved.Usethistoolatyourownrisk.     4.Removingunnecessaryselects TheselectmethodiscommontoseeinVBAcode,howeveritisoftenaddedtothemacrowhereitisn’tneeded.Selectcantriggercelleventssuchasanimationsandconditionalformattingwhichslowthemacrodown,soremovingunnecessaryselectscansignificantlyspeedupyourmacro. Thefollowingexampleshowsthecodebeforeandaftermakingthechangetoremoveunnecessaryselects.   Before Sheets("OrderDetails").Select Columns("AC:AH").Select Selection.ClearContents After Sheets("OrderDetails").Columns("AC:AH").ClearContents   5.UsingtheWithstatementtoreadobjectproperties Whenworkingwithobjects,usetheWithstatementtoreducethenumberoftimesobjectpropertiesareread.ThefollowingexampleshowsthecodebeforeandaftermakingthechangetousetheWithstatement.   Before Range("A1").Value=“Hello” Range("A1").Font.Name=“Calibri” Range("A1").Font.Bold=True Range("A1").HorizontalAlignment=xlCenter After WithRange("A1") .Value2=“Hello” .HorizontalAlignment=xlCenter With.Font .Name=“Calibri” .Bold=True EndWith EndWith   6.Usingrangesandarrays ReadingandwritingtocellsinExcelfromVBAisexpensive.ThereisanoverheadthatisincurredeverytimedatamovesbetweenVBAandExcel.   ThemountainbetweenExcelandVBA ThismeansthatyoushouldtrytoreducethenumberoftimesyoupassdatabetweenVBAandExcel.Thisiswhererangesareuseful.Insteadofreadingandwritingtoeachcellindividuallyinaloop,readtheentirerangeintoanarrayatthestart,loopthroughthearray,andthenwritetheentirearraybackattheend.Thefollowingexamplecodeshowshowarangecanbeusedtoreadandwritethevaluesonce,insteadofreadingeachcellindividually.   DimvArrayAsVariant DimiRowAsInteger DimiColAsInteger DimdValueAsDouble vArray=Range("A1:C10000").Value2‘readallthevaluesatoncefromtheExcelcells,putintoanarray ForiRow=LBound(vArray,1)ToUBound(vArray,1) ForiCol=LBound(vArray,2)ToUBound(vArray,2) dValue=vArray(iRow,iCol) IfdValue>0Then dValue=dValue*dValue‘Changethevaluesinthearray,notthecells vArray(iRow,iCol)=dValue EndIf NextiCol NextiRow Range("A1:C10000").Value2=vArray‘writesalltheresultsbacktotherangeatonce   7.Use.Value2insteadof.Textor.Value Therearedifferentwaysthatyoucanretrievevaluesfromacell,andwhichpropertyyouusecanmakeadifferentintheperformanceofyourcode.   .Textiscommonlyusedtoretrievethevalueofacell–itreturnstheformattedvalueofacell.Gettingtheformattingofacellismorecomplexthanjustretrievingavalue,andmakes.Textquiteslow.   .Valueisanimprovementover.Text,asthismostlygetsthevaluefromthecell,withoutformatting.Howeverforcellsformattedasadateorcurrency,.ValuewillreturnaVBAdateorVBAcurrency(whichmaytruncatedecimalplaces).   .Value2givestheunderlyingvalueofthecell.Asitinvolvesnoformatting,.Value2isfasterthan.Value..Value2isfasterthan.Valuewhenprocessingnumbers(thereisnosignificantdifferencewithtext),andismuchfasterusingavariantarray.   Foramoredetailedexplanation,pleaseseeCharlesWilliam’sblogpost,“TEXTvsVALUEvsVALUE2”:https://fastexcel.wordpress.com/2011/11/30/text-vs-value-vs-value2-slow-text-and-how-to-avoid-it/   8.Bypasstheclipboard(copyandpaste) WhenyouusetheMacroRecordertorecordoperationsthatusecopyandpaste,thecodewillusethecopyandpastemethodsbydefault.However,withinVBAcode,itismuchfastertobypasstheclipboardanduseinternaloperationsinstead.Bydefault,copyingwillcopyeverything,includingformulas,valuesandformatting.Youcanmakecopyingfasterbyonlycopyingvaluesorformulas,withouttheformatting.Thefollowingexampleshowsthecodebeforeandaftermakingthechangetobypasstheclipboard.   Before Range("A1").Select Selection.Copy Range("A2").Select ActiveSheet.Paste After ‘Approach1:copyeverything(formulas,valuesandformatting Range("A1").CopyDestination:=Range("A2") ‘Approach2:copyvaluesonly Range("A2").Value2=Range("A1").Value2 ‘Approach3:copyformulasonly Range("A2").Formula=Range("A1").Formula   Ifyoustillfindthatamacrotakeslongerthanexpectedtoexecutemanyindividualcopyandpasteoperations,youmaywanttoapplythefollowinghotfix:https://support.microsoft.com/en-in/help/2817672/macro-takes-longer-than-expected-to-execute-many-in...   9.UseOptionExplicitto catchundeclaredvariables OptionExplicitisoneoftheavailableModuledirectives inVBAthatinstructsVBAhowtotreatthecodewithinthecodemodule. SettingOptionExplicitrequiresallvariablestobedeclaredandwillgivecompileerrorsifanundeclaredvariableisused.Thishelpscatchincorrectlytypedvariablenamesandimprovesperformancewithallvariabletypesbeingdefinedatcompiletime,insteadofbeinginferredatruntime.   Thiscanbesetbytyping:OptionExplicitatthetopofeachmoduleinyourprojectorbycheckingthe"RequireVariableDeclaration"optionunderTools->OptionsintheVBAeditor.    AdditionalDetailsonModuledirectivescanbefoundhere:https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/option-explicit-s...   Conclusion Wehopethatthishashelpedhighlightsomeofthewaysthatyoucanmakeyourmacrosrunfaster.We’resurethatwehaven’tcoveredeverything,sopleasecommentbelowwithanyothertipsortrickstoimprovetheperformanceofyourmacrosinExcel.   Furtherreading https://blogs.office.com/en-us/2009/03/12/excel-vba-performance-coding-best-practices/ http://datapigtechnologies.com/blog/index.php/ten-things-you-can-do-to-speed-up-your-excel-vba-code/ https://www.ozgrid.com/VBA/SpeedingUpVBACode.htm   FAQs: Afterusingthenewmacroanimationsstoppedworking/it’sstuckonmanualcalculation. Itispossiblethecodedisablesthevarioussettings,butthemacrocrashesbeforere-enablingthesesettings.Tofixthis,youwillneedtorunthecodetoenablethesesettingsagain.   Otherresources https://www.thespreadsheetguru.com/blog/2015/2/25/best-way-to-improve-vba-macro-performance-and-prev... - TheBestWayToImproveVBAMacroPerformanceAndPreventSlowCodeExecution https://www.ozgrid.com/VBA/SpeedingUpVBACode.htm- OptimizeSlowVBACode.SpeedingUpSlowExcelVBACode     Abouttheco-authors   CharlesWilliamsfoundedDecisionModelsin1996toprovideadvancedconsultancy,decisionsupportsolutions,andtoolsbasedonMicrosoftExcelandrelationaldatabases.CharlesistheauthorofFastExcel,thewidelyusedExcelperformanceprofilerandperformancetoolset,andco-authorofNameManager,thepopularutilityformanagingdefinednames.FormoreinformationaboutExcelcalculationperformanceandmethods,memoryusage,andVBAuser-definedfunctions,visitthe DecisionModelsWebsite.   JanKarelPieterseisalongtimeExcelMVPwhodevelopscustomsolutionsfocusedonMicrosoftOffice,withdeepexpertiseinExcelandVBA.HerunsthewebsiteJKPApplicationDevelopmentServicessite,whereyoucanfindaninterestingcollectionofarticles,trainingevents,andutilities.Foragoodoverviewoftopics,seethislistofin-deptharticles.JandevelopssomecoolandusefulutilitiesforExcel,includingNameManager,RefTreeAnalyser,andFlexfind.Youcanfindafulllistonthedownloadspage.   Labels: Labels: Macros&VBA TipsandTricks Tags:FasterOffice2010office2016SlowSpeed 133KViews5Likes5Replies Reply AllDiscussions PreviousDiscussion NextDiscussion 5Replies HaythamAmairah  repliedto  MarkJohnson ‎Mar212018 09:41AM MarkasNew Bookmark Subscribe Mute SubscribetoRSSFeed Permalink Print EmailtoaFriend ReportInappropriateContent ‎Mar212018 09:41AM Re:8quicktipstoimproveyourVBAmacroperformance HiMark,   Thankyouverymuchforthesevaluabletips!   It'snoteworthytomentionthattheexplicitlydeclareallvariableswiththeappropriatedatatypeincreasesperformance. Youcanforceyourselfto declareallvariablesbyusingOptionExplicitstatement.   Inaddition,avoidusingtheVariantdatatypeasmuchaspossiblebecauseitwillcosttheVBAinterpreter/compilersomeextrastepstodeterminewhatthemostappropriatedatatypeshouldbeused! 0Likes Reply Anonymous  repliedto  MarkJohnson ‎Mar212018 09:41PM -edited ‎Mar212018 09:43PM MarkasNew Bookmark Subscribe Mute SubscribetoRSSFeed Permalink Print EmailtoaFriend ReportInappropriateContent ‎Mar212018 09:41PM -edited ‎Mar212018 09:43PM Re:8quicktipstoimproveyourVBAmacroperformance Mark,re:useofWithstatementKenGetz(authoroftheVBADevelopersHandbook-manyyearsago)pointedout,thatwhenusingtheWithstatement,youmustreducethenumberofdotsusedinordertoimproveefficiency.Otherwiseyouarejustsavingtypingtime.'---JimConehttps://goo.gl/IUQUN2 0Likes Reply JanKarelPieterse  repliedto  MarkJohnson ‎Mar262018 08:02AM MarkasNew Bookmark Subscribe Mute SubscribetoRSSFeed Permalink Print EmailtoaFriend ReportInappropriateContent ‎Mar262018 08:02AM Re:9quicktipstoimproveyourVBAmacroperformance IIRCtheApplication.EnableAnimationspropertydoesnotwork(butwe'dlikeitto!!!) 0Likes Reply MarkJohnson  repliedto  HaythamAmairah ‎Mar262018 08:52AM -edited ‎Mar262018 09:23AM MarkasNew Bookmark Subscribe Mute SubscribetoRSSFeed Permalink Print EmailtoaFriend ReportInappropriateContent ‎Mar262018 08:52AM -edited ‎Mar262018 09:23AM Re:8quicktipstoimproveyourVBAmacroperformance Thanksforyourfeedback,Haytham,it'sgreattohearmoretipsthatcanimproveperformance.I'veupdatedtheposttoincludeinformationonusingOptionExplicittocatchissueswithundeclaredvariables. 0Likes Reply the1abel  repliedto  MarkJohnson ‎Jul142021 01:38AM MarkasNew Bookmark Subscribe Mute SubscribetoRSSFeed Permalink Print EmailtoaFriend ReportInappropriateContent ‎Jul142021 01:38AM Re:9quicktipstoimproveyourVBAmacroperformance @MarkJohnson,AMAZING!!! THANKYOU!!!Thistookmefrommorethan11minutestolessthan10secondstoiterateover21millionrecordstoselectivelyupdatethem!#6wasincrediblyhelpful!! 1Like Reply Share SharetoLinkedIn SharetoFacebook SharetoTwitter SharetoEmail Browse What'snew SurfaceProX SurfaceLaptop3 SurfacePro7 Windows10Apps Officeapps MicrosoftStore Accountprofile DownloadCenter MicrosoftStoresupport Returns Ordertracking Storelocations Buyonline,pickupinstore In-storeevents Education Microsoftineducation Officeforstudents Officeforschools Dealsforstudentsandparents MicrosoftAzureineducation Enterprise Azure AppSource Automotive Government Healthcare Manufacturing FinancialServices Retail Developer MicrosoftVisualStudio WindowDevCenter DeveloperNetwork TechNet Microsoftdeveloperprogram Channel9 OfficeDevCenter MicrosoftGarage Company Careers AboutMicrosoft CompanyNews PrivacyatMicrosoft Investors Diversityandinclusion Accessibility Security Sitemap ContactMicrosoft Privacy Managecookies Termsofuse Trademarks Safetyandeco Aboutourads © Microsoft