Speed up your VBA Code - Automate Excel

Switch off Automatic Calculation. As your macro runs and changes cell values, Excel recalulates any cells that are dependent on the cell whose value changed. In ... 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 InthisArticleDisableScreenUpdatingSwitchoffAutomaticCalculationRisksInvolvedwhenChangingtheseDefaultSettings ThisarticlewilldemontratehowtospeedupyourVBACode. Youmighthaveaprocedureinyourcodethattakesalongtimetorun–itmayreferbacktoasheetcontinuouslyordocalculationsthatseemtoputyourcursorintoastateofaperpetualhourglass.Theare2mainsettingswecanaddtoourcodetospeedupthecode,bothofwhicharepartofVBAbestpracticeswhenwritingcode. DisableScreenUpdating Bydefault,Excelwilldisplaychangestoworkbook(s)inreal-timeasVBAcoderuns. Thismeansthatthescreenwillupdateasthemacroruns. Thiscanslowdownyourcodehugely,especiallyifalongprocedureisrunning. Topreventthis,turnofftheScreenUpdating.Addthislineofcodeatthetopoftheprocedure: 1 Application.ScreenUpdating = False Attheendofyourmacro,youshouldturnbackonScreenUpdating: 1 Application.ScreenUpdating = False Thereforeyourmacroshouldlookthisthisexamplebelow:   SwitchoffAutomaticCalculation Asyourmacrorunsandchangescellvalues,Excelrecalulatesanycellsthataredependentonthecellwhosevaluechanged. Inaddition,wheneveraformulaischanged,calculationsareagainperformed.Thiscanslowdownthespeedofyourmacrohugely. Toswitchoffautomaticcalculation,addthislineofcodetothetopofyourmacro. 1 Application.Calculation=xlManual andthentoswitchthembacktoautomatic,makesureyouputthislineofcodeatthebottomofyourmacro. 12 Application.Calculation=xlAutomatic RisksInvolvedwhenChangingtheseDefaultSettings ExcelbydefaulthastheScreenUpdatingsettoTRUEandtheCalculationssettoAutomatic. UsingthecodetoswitchoffthesesettingsintheApplicationwillspeedupyourcodeconsiderably. However,ifweforgettoswitchthesesettingBACKON,orifthecodestopsrunningbeforeitgetstothelinesofcodethatresetthesesettings,youmightendupwithanunresponsiveExcelScreen(astheScreenwillnotupdate)orcalculationsthatwillnotcalculateunlessyoumanuallydoso. Tosolvethisproblem,alwaysmakesureyouhaveanerrortrapinyourcodesothatifanerrordoesoccurtostopthemacro,makesurethemacrojumpsdowntothelinesofcodethatresetthesesettings. Intheaboveexample,wehavecreatedanErrorHandlercalledEH.  Ifsomethinggoeswronginthecode,themacrowilljumpdowntotheEHandrunthelast2linesofcode.AswehaveNOTputanExitSubinthemacroabovetheerrorhandler,ifthemacrorunsperfectly,itwillgettothe2linesofcodeandresetthesesettingsbacktothedefault. VBACodingMadeEasy StopsearchingforVBAcodeonline.LearnmoreaboutAutoMacro-AVBACodeBuilderthatallowsbeginnerstocodeproceduresfromscratchwithminimalcodingknowledgeandwithmanytime-savingfeaturesforallusers! LearnMore! VBACodeGenerator LearnMore VBACodeExamplesAdd-in Easilyaccessallofthecodeexamplesfoundonoursite. Simplynavigatetothemenu,click,andthecodewillbeinserteddirectlyintoyourmodule..xlamadd-in. (Noinstallationrequired!) FreeDownload ReturntoVBACodeExamples AutoMacro:VBAAdd-inwithHundredsofReady-To-UseVBACodeExamples&muchmore! LearnMore WhatisAutoMacro? AutoMacroisanadd-inforVBAthatinstallsdirectlyintotheVisualBasicEditor.Itcomesloadedwithcodegenerators,anextensivecodelibrary,theabilitytocreateyourowncodelibrary,andmanyotherhelpfultime-savingtoolsandutilities. LearnMore UltimateVBAAdd-in LearnMore UltimateVBAAdd-in off-Sale! Offerexpiressoon! SaleEndsFridayatMidnight 0   0 days 0   0 hours 0   0 minutes 0   0 seconds VBA(Macros)CodeExamplesAdd-in TryforFree 1004Reviews AutoMacro:TheUltimateAdd-inforVBA LearnMore AutoMacro:TheUltimateAdd-inforVBA off-Sale! DownloadourVBAMacroCodeExamplesAdd-in Download 100%Free