VBA: Improve Speed and Other Best Practices - Automate Excel

Settings to Speed Up VBA Code VBACodeExamples  InDepthTutorialsyes PowerPointVBAMacroExamples&Tutorial Sorting FindandFindandReplace CheatSheets–Commands&SyntaxLists WordMacroExamples&VBATutorial Basicsyes If,ElseIf,Else(UltimateGuidetoIfStatements) ValuePaste&PasteSpecial VBA–Cut,Copy,PastefromaMacro VBASaveFile–20EasyExamples VBACodingConceptsyes CommentinginVBA Creating(Custom)UserDefinedFunctions(UDFs) ExcelVBA–NamedRangesandProgramming HowtoRenameorDeleteaModuleorForm Opening&UsingtheVisualBasicEditor(VBE)inExcel ReturnaValuefromaSubProcedure ShortcutsfortheVisualBasicEditor CallFunctionfromaSub Function–Call,ReturnValue,&Parameters ObjectModel OnError–ErrorHandlingBestPractices PrivatevsPublicProcedures(Subs&Functions) SelectCaseStatement SubProcedure WhatisaVBAModule? WhatisaVBAProject?(AndHowtoEdit) VBA–HideaMacro VBA–LanguageReferences VBA–ModifytheRight-ClickMenutoCallaMacro VBA–RunaMacrofromaMacro VBA–RunaMacroLinebyLine VBA–Wait,Delay,CreateaTimerWhileRunningCode Debug.PrintandImmediateWindow VBADoEvents VBAEnd VBAExitSuborFunction VBAOnErrorExitSub VBAOptionExplicit VBA:ImproveSpeed&OtherBestPractices Loopsyes VBAForLoop–LoopThroughaRangeofCells Loops-UltimateGuide ForEachExamples(QuickReference) VBAExitFor VBAExitLoop WhileWendLoop RangesandCellsyes RangesandCells-UltimateGuide Formulas-UltimateGuide UnionandIntersect ResizeRange VBA–AutofillwithaMacro VBA–ChangeaCell;sBackgroundColor VBA–ClearFormats–RemoveFormattingForEachCellInRange VBA–ConditionalFormatting–HighlightDuplicatesinaRange VBA–DeleteAllNamedRanges VBA–DeleteHyperlinks VBA–DetermineStartandEndColumnofaMergedCell VBA–Excel’sColorScheme VBA–FindtheMaximumValueForEachColumninaRange VBA–FormatPainter VBA–GettheActiveCell’sColumnorRow VBA–ResizeanObjecttoaRangeSize VBA–SelectAllCells VBA–SumbyColor VBA–TestifSelectionisaRangeorObject CellValue–Get,Set,orChange ClearContents/ClearCells CopyDestination(CopyRangetoAnotherSheet) CurrentRegion DynamicRange VBAFunction–PopulatingaRangeWithRandomValues HighlightCell VBAMergeCells&UnmergeCells OffsetRangeorCell Range/CellAddress Range.End(xlDown,xlUp,xlToRight,xlToLeft) VBARoutinetoreturnColumnLetterofCell VBASelectRangeorCells VBAUnion–JoinMultipleRanges VBAWrapText ActivecellOffsetVBA Rows&Columnsyes Select(andworkwith)EntireRows&Columns DeleteorInsertRowsBasedonCellValue DeleteRowsthatMeetCertainCriteriainVBA Hide/UnhideColumns&Rows UnhideAllRows/Columns VBA–AutofitColumns VBA–CounttherowsinaSelection VBA–DeleteBlankRows VBA–SelectEvery5thRow VBA–SelectNonContiguousColumns Copy/PasteRows&Columns DeleteEntireRoworColumn GroupRows&Columns InsertRoworColumn VBASetColumnWidthorRowHeight VBAUsedRange–CountNumberofUsedRowsorColumns Sheetsyes Sheets–TheUltimateGuide VBA–CounttheSheetsinaWorkbook VBA–CreateaHyperlinkMenuofWorksheets VBA–Hide(orUnhide)aWorksheet VBA–HideWorkSheetTabs VBA–HowtoUseWorksheetFunctions(andaFullList) VBA–LoopThroughallWorksheetswithForEach VBA–MacrotoListallSheetsinaWorkbook SendWorksheetsbyEmailasSeparateWorkbooks VBA–SortSheetsAlphabetically VBA–UnhideAllWorksheets ClearEntireSheet VBACopyWorksheet VBADeleteorClearWorksheet GetSheetName/RenameSheet VBAProtect/UnprotectWorksheets VBARoutinetoAddandNameWorksheets VBASelectSheet,ActivateSheet,andGetActivesheet WorksheetRange VBA:SettheDefaultSheetWhenaWorkBookOpens HowtoSortTabs/WorksheetswithaVBAMacro Workbooksyes CombineWorkbooks CreateNewWorkbook(Workbooks.Add) ActivateWorkbook ActiveWorkbookvs.ThisWorkbook VBAOpen/CloseWorkbook WorkbookName(Get,Set,withoutExtension) WorkbookProtection(PasswordProtect/Unprotect) WorkingwithWorkbooks(TheWorkbookObject) Arraysyes Arrays VBA–ArrayExamples DictionaryObjects Collections ArrayList ArrayofObjects VariantArray DynamicArray(Redim&RedimPreserve) Declare&InitilizeStringArray Declare(Dim),Create,andInitializeArrayVariable ArrayLength/Size AssignRangetoArray ClearArray–EraseFunction LoopThroughArray/ForEachIteminArray SortArray TransposeArray Searchfor(Find)ValueinArray PopulateArraywithUniqueValuesfromColumn RemoveDuplicatesFromArray FunctionReturnArray Multi-DimensionalArray(2DArrays) Output(Print)ArraytoRange Errorsyes TryCatchErrors ObjectRequiredError SyntaxErrorVBA TypeMismatch(Run-timeError13) AutomationError CompileError Error1004–Application-DefinedorObject-DefinedError IgnoreError OnErrorResumeNextorGoto0 OutofMemoryError Throw/RaiseError–Err.Raise–CustomErrorMsg Can’tFindProjectorLibrary–VBACompileError CompileErrorinHiddenModule–VBA FixError400 FixVBAError438–ObjectDoesn’tSupportPropertyorMethod FixVBARunTimeError32809 FixVBARunTimeError57121 Variablesyes DimVBA–DeclareVariable VBA–DetermineaVariable’sUnderlyingType VBAArrayVariables BooleanDataType(DimVariable) Constant VBAConstantExpressionRequired VBADataTypes–VariablesandConstants VBADateVariable DoubleDataType(DimVariable) VBAGlobalVariable Int/IntegerDataType(DimVariable) LongDataType(DimVariable) VBAPublicVariable–UseaVariableinMultipleModulesorSubs VBARangeObjectVariables(Dim/SetRange) SetObjectVariables–Workbooks,Worksheets,&More SingleDataType(DimVariable) VBAStaticVariable StringDataType(DimVariable) VariantDataType(DimVariable) Settingsyes GetandChangeUserNamewithVBA VBA–DisplayStatusBarMessage VBA–Modifythetitlebar VBA–Preventwarningmessagesfromamacro VBA–TurnAutomaticCalculationsOff(orOn) VBA–TurnFormulaBarOff(orOn) VBA–TurnoffScreenUpdating VBATurnScrollBarsOnorOff TextandStringsyes SplitStringintoCells PreventVBACaseSensitive–OptionCompareText VBA–ExtractNumberFromString VBA–FindthenthWordinaStringofText VBA–ForceProper,Upper,orLowercaseautomatically VBA–LineBreakorLineFeedinaMessageBox VBA–LoopThroughaString VBA–RemoveCharactersfromLeftorRightSideofVariableLengthString VBA–ReverseaStringofText VBAChar/ChrFunction VBACompareStrings–StrComp VBAComparisonOperators–NotEqualto&More VBAConcatenateTextStringsTogether(&–Ampersand) ConvertInteger(Number)toString ConvertStringtoDate ConvertTextStringtoNumber VBAFunctionToCalculateNumberofWordsinaString VBAINSTR–FindTextinaString VBALikeOperator VBALogicalOperators–OR,AND,XOR,NOT,IS,&LIKE VBANewLine/CarriageReturn ParseString VBASpaceFunction–AddSpacestoaString VBAStringsandSubstringsFunctions TexttoColumns Trim,LTrim,andRTrimFunctions–RemoveSpacesFromText VBAUpper,Lower,andProperCase–CaseFunctions Viewyes VBA–ActiveWindow.WindowState VBA–Display(orExit)Fullscreen VBA–FreezePanes VBA–HideExcel(TheEntireApplication) VBA–PageBreakPreviewModeOnorOff VBA–ScrollVerticallyandScrollHorizontally VBA–Zoom–FitSelection VBA–ZoominandOutofWorksheets Filesyes FileSystemObject MoveFileswithVBAFileSystemObject(MoveFile) VBA–ConvertExceltoCSV(CommaDelimitedTextFile) CreateTextFilewithCreateTextFile VBA–Deletefilesfromtheharddrive VBA–ExportRangetoCSV(DelimitedTextFile) ImportFileNamestoClickableLinks VBA–ListofallfilescontainedwithinaDirectory ReadTextFileintoString ReadTextFileLinebyLine VBA–TestifWorkbookisOpenbyWorkbookName ValidateFilenames WritetoTextFile VBACheckifFileorFolderExists CombineMultipleExcelFilesintoOneWorkbook CopyaFolderwithCopyFolder(FSO) VBACopyFile/Workbook VBADeleteFile/Workbook DeleteFolderwithDeleteFolder(FSO) FileCopyStatement GetFileNamewithGetFileName(FSO) GetFolder&GetFile(GetFile&FolderProperties) VBAListFilesinFolder VBAOpenFileDialogBox OpenTextFilewithOpenTextFile ReadTextFile(Read,Parse,andImport) VBARenameFile Shapes,Charts,Objectsyes VBAAutoFilter VBAGuideForChartsandGraphs VBAGuidetoPivotTables VBAObjects ExcelAddVBAButton HowtouseVBAControlsinUserForms VBA–CreateaBarChart VBA–DeleteAllAutoshapes VBA–ProgrammaticallyDrawBoxeswithCode Listbox–SelectedItem VBATablesandListObjects TextBox Formattingyes ConditionalFormatting Bold CellBorders CellFont–ChangeColor,Size,Style,&More CenterText–CellAlignment(Horizontal&Vertical) DisplayPageBreaksSetting FormatCells FormatDate FormatTime Eventsyes Events VBA–ExitWithoutSavePrompt VBA–ForceaWorkbooktoSaveBeforeClose VBA–RunaMacrowhenExcelCloses–Auto_Close VBA–RunamacrowhenExcelstarts VBAWorksheetChangeEvent–RunaMacroWhenaCellChanges VBA:ShoworloadaformwhenExcelstarts UserForms,MsgBoxes&Controlsyes VBA–MakeUserformTransparent VBA–YesNoMessageBox(Msgbox) VBAInputBox–GetInputfromaUser CreatingVBAUserforms OptionButtonExcelVBA SpinbuttonExcelVBA VBACheckbox VBAComboBox VBAListbox VBAOpenorCloseUserForm Formattingyes ConditionalFormatting Bold CellBorders CellFont–ChangeColor,Size,Style,&More CenterText–CellAlignment(Horizontal&Vertical) DisplayPageBreaksSetting FormatCells FormatDate FormatTime Misc.yes ClearClipboard Pause(Break/Stop)&ResumeaMacro SaveAs(Print)toPDF ColorIndexCodesList FormatNumbers HowtoInstall(orUninstall)aVBAadd-in(.xlamfile)forMicrosoftExcel HowtouseVBAStructures RemovingDuplicateValuesinExcelVBA SpeedupyourVBACode VBA–AddorRemoveCellComments AllOperatorsExplained BuildaCustomImportInterface CheckifOfficeAppisRunning DialogBox GetToday’sDate(CurrentDate) PivotTableFilter RefreshPivotTable/AllPivotTables RefreshPivotTablewhenDataChanges UsingGoalSeekinVBA UsingtheAnalysisToolPakinVBA AdvancedFilter Application.CutCopyMode=False Calculate–Now,Workbook,Worksheet,orRange Call/RunSubFromanotherSubroutine CopytoClipboard FindValueinColumn GoToaLineLabel IIFFunction InputStatement isNothing IsOperator LineInputStatement Multiple(Nested)IfStatements ProjectPassword/Protectcode Reference Solver Wait&SleepFunctions–Pause/DelayCode Wildcards With/EndWithStatement WhatistheDifferenceBetweenVBandVBA? PersonalMacroWorkbook VBA–CheckSpelling VBA–TurnOffAutoFilter/ClearFilters VBADropDownList(DataValidation) VBAHyperlinks VBARandomNumber Functionsyes CheckifSheetand/orRangeExistsFunction EnvironFunction–ExcelVBA IsEmptyFunction UsingIsnumericandIsnumberinVBA VBA–CompareDates AbsFunction AVERAGE,AVERAGEA,AVERAGEIF CCurFunction CDblFunction–ConverttoDouble CDecFunction ChDirStatement ChDriveStatement CIntFunction–ConverttoInteger CLngFunction–ConvertExpressiontoLong CLngLngFunction CloseStatement COUNTFunction COUNTIFandCOUNTIFSFunctions CreateObject(CreateObject) CSngFunction CurDirFunction VBADateFunctions DateAddFunction DateDiffFunction DatePartFunction DateSerialFunction DateValueFunction DayFunction DirFunction EnvironFunctions EOFFunction FileDateTimeFunction FilterArrays FormatFunction FreeFileFunction VBAFunctiontoShowCellFormula FunctionsList GetAttrFunction HexFunction HourFunction InStrRevFunction–FindTextFromRight IsArrayFunction IsDateFunction IsEmptyFunction IsErrorFunction IsMissingFunction IsNullFunction IsNumericFunction JoinFunction LBoundFunction LeftFunction–ExtractTextFromLeft LenFunction–GetStringLength LOFFunction LogFunction MacIDFunction MidFunction–ExtractCharactersFromMiddleofString MinuteFunction MkDirStatement MonthFunction MonthNameFunction NowFunction–GetCurrentDate&Time OpenStatement PrintStatement RandomizeStatement ReplaceFunction–ReplaceString RightFunction–ExtractTextFromRight RndFunction RoundFunction Round,RoundUp,andRoundDown SecondFunction SplitFunction–SplitStringofTextintoArray SqrFunction SumFunction(Ranges,Columns,&More) SUMIFandSUMIFSFunctions SwitchStatement TextFunction TimeFunction TimeFunctions TimerFunction TimeSerialFunction TimeValueFunction TypeName TypeOf UBound&LBoundFunctions ValFunction VarType WeekdayFunction WeekdayNameFunction WriteStatement YearFunction Vlookup–MultipleResultswithVBA VlookupMultipleCriteriawithVBAUDF AdvancedProgrammingyes AdvancedVBAProgrammingExamples EarlyLateBinding ClassConstructor ClassModuleTutorial&Examples Matrix–Create&More Shell VBA–ConvertMatrixtoVector VBA–ProgrammingtheVBE(VisualBasicEditor) VBARegex VBASendKeys VBATimerFunction Internetyes AutomateInternetExplorer(IE)UsingVBA VBA–ConsumeSoapWebServices VBA–WinHttpRequestwithLoginandPassword–MiscBloglines SendEmailsfromExcelthroughGmail SendEmailsfromExcelthroughOutlook Accessyes AccessVBARecordsets–Open,Count,LoopandMore AccessVBA–Import/ExportExcel–Query,Report,Table,andForms AccessVBA–Open/CloseForm AccessVBA–OpenExcelFile AccessVBADatabase–Open,Connect,Login,&More AccessVBAFindfirst AccessVBAMe AccessVBAQuery AccessVBARecordsets–Open,Count,LoopandMore AccessVBARefreshForm AccessVBAReports–Print,Export,Filter AccessVBASQLExamples AccessVBATables–Update,Count,Delete,Create,Rename,Export Wordyes WordBookmarks–VBAMacrostoAdd,Delete,Goto,Modify WordVBA–MacrotoOpenWordDocument WordVBAMacros–AddNewDocument WordVBAMacros–CountWordsinSelection WordVBAMacros–Find,Find&Replace WordVBAMacros–SaveAs(PDForNewFileName) WordVBAMacros–Tables:Add,Select,Loop,InsertFromExcel WordVBAMacros–Templates(MakeNew) WordVBAMacros–TextBox:Add,Delete,Write OtherExcelGuides "HowTo"Guides "Formulas"Guides "Shortcuts"Guides VBAGuides ReturntoVBACodeExamples InthisArticleSettingstoSpeedUpVBACodeDisableScreenUpdatingSetCalculationstoManualDisableEventsDisablePageBreaksBestPracticestoImproveVBASpeedAvoidActivatingandSelectingAvoidCopyingandPastingUsetheForEachloopsinsteadofForLoopsDeclareVariables/UseOptionExplicitUseWith–EndWithStatementsAdvancedBestPracticeTipsProtectUserInterfaceOnlyUseArraystoEditLargeRangesVBACodingMadeEasy ThistutorialwilldiscusshowtospeedupVBAMacrosandotherVBAbestpractices.   SettingstoSpeedUpVBACode BelowyouwillfindseveraltipstospeedupyourVBAcode.Thetipsarelooselyorganizedbyimportance. TheeasiestwaytoimprovethespeedofyourVBAcodeisbydisablingScreenUpdatinganddisablingAutomaticCalculations.Thesesettingsshouldbedisabledinalllargeprocedures. DisableScreenUpdating Bydefault,Excelwilldisplaychangestoworkbook(s)inreal-timeasVBAcoderuns.ThiscausesamassiveslowdowninprocessingspeedasExcelmostinterpretanddisplaychangesforeachlineofcode. ToturnoffScreenUpdating: 1 Application.ScreenUpdating=False Attheendofyourmacro,youshouldturnbackonScreenUpdating: 1 Application.ScreenUpdating=True Whileyourcodeisrunning,youmayneedto“refresh”thescreen.Thereisno“refresh”command.Instead,youwillneedtoturnScreenUpdatingbackonanddisableitagain. SetCalculationstoManual Wheneveracellvalueischanged,Excelmustfollowthe“calculationtree”torecalculatealldependentcells.Additionally,wheneveraformulaischanged,Excelwillneedtoupdatethe“calculationtree”inadditiontore-calculatingalldependentcells. Dependingonyourworkbooksize,theserecalculationscancauseyourmacrostorununreasonablyslow. TosetCalculationstoManual: 1 Application.Calculation=xlManual Tomanuallyre-calculatetheentireworkbook: 1 Calculate Noteyoucanalsocalculateonlyasheet,range,orindividualcell,ifnecessaryforimprovedspeed. TorestoreAutomaticCalculations(attheendofyourprocedure): 1 Application.Calculation=xlAutomatic Important!ThisisanExcelsetting.Ifyoudon’tre-setcalculationstoautomaticyourworkbookwillnotre-calculateuntilyoutellitto. You’llseethebiggestimprovementsfromtheabovesettings,butthereareseveralothersettingsthatcanmakeadifference: DisableEvents Eventsare“triggers”thatcausespecialeventprocedurestorun.Examplesinclude:whenanycellonaworksheetchanges,whenaworksheetisactivated,whenaworkbookisopened,beforeaworkbookissaved,etc. Disablingeventscancauseminorspeedimprovementswhenanymacrosrun,butthespeedimprovementcanbemuchgreaterifyourworkbookusesevents. Andinsomecasesdisablingeventsisnecessarytoavoidcreatingendlessloops. Todisableevents: 1 Application.EnableEvents=False Toturneventsbackon: 1 Application.EnableEvents=True DisablePageBreaks DisablingPageBreakscanhelpincertainsituations: You’vepreviouslysetaPageSetuppropertyfortherelevantworksheetandyourVBAproceduremodifiesthepropertiesofmanyrowsorcolumns ORYourVBAprocedureforcesExceltocalculatepagebreaks(displayingPrintPreviewormodifyinganypropertiesofPageSetup). TodisablePageBreaks: 1 ActiveSheet.DisplayPageBreaks=False Tore-enablePageBreaks: 1 ActiveSheet.DisplayPageBreaks=True BestPracticestoImproveVBASpeed AvoidActivatingandSelecting WhenyourecordaMacro,you’llseemanyActivateandSelectmethods: 12345678 SubSlow_Example()    Sheets("Sheet2").Select    Range("D9").Select    ActiveCell.FormulaR1C1="example"    Range("D12").Select    ActiveCell.FormulaR1C1="demo"    Range("D13").SelectEndSub Activatingandselectingobjectsisusuallyunnecessary,theyaddcluttertoyourcode,andtheyareverytime-consuming. Youshouldavoidthismethodswhenpossible. ImprovedExample: 1234 SubFast_Example()    Sheets("Sheet2").Range("D9").FormulaR1C1="example"    Sheets("Sheet2").Range("D12").FormulaR1C1="demo"EndSub AvoidCopyingandPasting Copyingrequiressignificantmemory.Unfortunately,youcan’ttellVBAtoclearinternalmemory.InsteadExcelwillclearit’sinternalmemoryat(seemingly)specificintervals.Soifyouperformmanycopyandpasteoperationsyouruntheriskofhoggingtoomuchmemory,whichcandrasticallyslowdownyourcodeorevencrashExcel. Insteadofcopyingandpasting,considersettingthevaluepropertiesofcells. 123456789 SubCopyPaste()     'Slower    Range("a1:a1000").CopyRange("b1:b1000")     'Faster    Range("b1:b1000").Value=Range("a1:a1000").Value EndSub UsetheForEachloopsinsteadofForLoops Whenloopingthroughobjects,theForEachloopisfasterthantheForLoop.Example: ThisForLoop: 123456 SubLoop1()    dimiasRange    Fori=1To100        Cells(i,1).Value=1    NextiEndSub IsslowerthanthisForEachLoop: 123456 SubLoop2()    DimcellAsRange    ForEachcellInRange("a1:a100")        cell.Value=1    NextcellEndSub DeclareVariables/UseOptionExplicit VBAdoesnotrequirethatyoudeclareyourvariables,unlessyouaddOptionExplicittothetopofyourmodule: 1 OptionExplicit AddingOptionExplicitisacodingbestpracticeasitdecreasestheprobabilityoferrors.Italsoforcesyoutodeclareyourvariables,whichslightlyincreasesthespeedofyourcode(benefitsaremorenoticeablethemoreavariableisused). HowdoesOptionExplicitpreventerrors? ThebiggestbenefittoOptionExplicitisitwillhelpyoucatchvariablenamespellingerrors.Forexample,inthefollowingexamplewe’vesetavariablenamed‘var1’,butlaterwereferencevariablenamed‘varl’.Variable‘varl’hasn’tbeendefinedsoit’sblank,causingunexpectedresults. 1234 SubOptionExplicit()    var1=10    MsgBoxvarlEndSub UseWith–EndWithStatements Ifyoureferencethesameobjectsmultipletimes(ex.Ranges,Worksheets,Workbooks),considerusingtheWithStatement. Itisfastertoprocess,canmakeyourcodeeasiertoread,andsimplifiesyourcode. Withstatementexample: 12345678 SubFaster_Example()    WithSheets("Sheet2")        .Range("D9").FormulaR1C1="example"        .Range("D12").FormulaR1C1="demo"        .Range("D9").Font.Bold=True        .Range("D12").Font.Bold=True    EndWithEndSub Isfasterthan: 123456 SubSlow_Example()    Sheets("Sheet2").Range("D9").FormulaR1C1="example"    Sheets("Sheet2").Range("D12").FormulaR1C1="demo"    Sheets("Sheet2").Range("D9").Font.Bold=True    Sheets("Sheet2").Range("D12").Font.Bold=TrueEndSub AdvancedBestPracticeTips ProtectUserInterfaceOnly It’sgoodpracticetoprotectyourworksheetsfromeditingunprotectedcellstopreventtheend-user(oryou!)fromaccidentallycorruptingtheworkbook. However,thiswillalsoprotecttheworksheet(s)fromallowingVBAtomakechanges.Soyoumustunprotectandre-protectworksheets,whichisverytimeconsumingwhendoneonmanysheets. 12345 SubUnProtectSheet()    Sheets(“sheet1”).Unprotect”password”    'EditSheet1    Sheets(“sheet1”).Protect”password”EndSub Instead,youcanprotectsheetswithsettingUserInterfaceOnly:=True.ThisallowsVBAtomakechangestosheets,whilestillprotectingthemfromtheuser. 1 Sheets(“sheet1”).ProtectPassword:="password",UserInterFaceOnly:=True Important!UserInterFaceOnlyresetstoFalseeverytimetheworkbookopens.Sotousethisawesomefeature,youwillneedtousetheWorkbook_OpenorAuto_Openeventstosetthesettingeachtimetheworkbookisopened. PlacethiscodeintheThisworkbookmodule: 123456 PrivateSubWorkbook_Open()    DimwsAsWorksheet    ForEachwsInWorksheets        ws.ProtectPassword:="password",UserInterFaceOnly:=True    NextwsEndSub orthiscodeinanyregularmodule: 123456 PrivateSubAuto_Open()    DimwsAsWorksheet    ForEachwsInWorksheets        ws.ProtectPassword:="password",UserInterFaceOnly:=True    NextwsEndSub UseArraystoEditLargeRanges Itcanbeverytimeconsumingtomanipulatelargerangesofcells(Ex.100,000+). Insteadofloopingthroughrangesofcells,manipulatingeachcell,youcanloadthecellsintoanarray,processeachiteminthearray,andthenoutputthearraybackintotheiroriginalcells. Loadingthecellsintoarraysformanipulationcanbemuchfaster. 1234567891011121314151617181920212223242526272829303132 SubLoopRange()     DimcellAsRange    DimtStartAsDouble     tStart=Timer     ForEachcellInRange("A1:A100000")        cell.Value=cell.Value*100    Nextcell     Debug.Print(Timer-tStart)&"seconds" EndSub SubLoopArray()     DimarrAsVariant    DimitemAsVariant    DimtStartAsDouble     tStart=Timer     arr=Range("A1:A100000").Value    ForEachitemInarr        item=item*100    Nextitem    Range("A1:A100000").Value=arr     Debug.Print(Timer-tStart)&"seconds" EndSub VBACodingMadeEasy StopsearchingforVBAcodeonline.LearnmoreaboutAutoMacro–AVBACodeBuilderthatallowsbeginnerstocodeproceduresfromscratchwithminimalcodingknowledgeandwithmanytime-savingfeaturesforallusers!   LearnMore! <