Optimize Slow VBA Code. Speeding Up Slow Excel VBA Code

Got any Excel/VBA Questions? Excel Help

Speed up code and stop screen flickering:

Sub NoScreenRePainting()



Application.ScreenUpdating=False



	'Your code here.



	Application.ScreenUpdating=True



End Sub

Preventing calculation while executing code:

Sub NoCalculations()



Application.Calculation = xlCalculationManual



	'Your code here.



	Application.Calculation = xlCalculationAutomatic



End Sub

Speeding up code if you have Worksheet or Workbook Events. Also stops endless loops in Events

Sub StopAllEvents()



Application.EnableEvents = False



	'Your code here.



	Application.EnableEvents = True



End Sub

Use the With Statement when working with Objects.

Sub WithARange()



With Range("A1")



		.Value = 100



		.Font.Bold = True



		.Interior.ColorIndex = 6



		.Copy Destination:=Range("B1")



	End With







End Sub

Use VbNullString instead of = "" When needing to default a String variable back to it's default of "" use:

Sub EmptyText()



Dim strWords As String







	strWords = "Cats"



	MsgBox strWords







	strWords = vbNullString



	MsgBox strWords



End Sub

Inserting a Relative formula into a range of cells: Faster than AutoFill or Copy.

Sub NoAutoFillOrCopy()



Range("A1:A200").FormulaR1C1 = "=SUM(RC[1]:RC[5])"



End Sub

Tip: To get a formula, type it in any cell then select the cell, go Tools>Macro>Record new macro and record a macro pushing F2 then Enter.

Avoid the use of Copy and Paste whenever Possible:

Sub NoCopyAndPaste()



	'Instead of:



Sheet1.Range("A1:A200").Copy



	Sheet2.Range("B1").pasteSpecial



	Application.CutCopyMode=False'Clear Clipboard



'Use:



'By-passes the Clipboard



	Sheet1.Range("A1:A200").Copy Destination:=Sheet2.Range("B1")



'Or, if only values are needed:



Sheet2.Range("B1:B200").Value= Sheet1.Range("A1:A200").Value



'Or, if only formulae are needed:



Sheet2.Range("B1:B200").Formula = Sheet1.Range("A1:A200").Formula



	'See also FormulaArray and FormulaR1C1 etc



'Instead of:



Sheet1.Range("A1:A200").Copy



	Sheet1.Range("A1:A200").PasteSpecial xlPasteValues



	Application.CutCopyMode=False'Clear Clipboard



'Use:



Sheet1.Range("A1:A200") = Sheet1.Range("A1:A200").Value



End Sub

Always declare your variables correctly!

To quickly view a variables definition:
Select the variable that you want the definition for.
Go to View>Definition (Shift+F2)

To return to your previous position:
Go to View>Last Postition (Ctrl+Shift+F2).

Release memory from Object variables:

Sub ReleaseObjectMemory()



'Could be any variable of the Object type



	Dim wSheet as Worksheet







	'Set Object variable



	Set wSheet = Sheet1



	'Your code here.







	'Release memory



	Set wSheet = Nothing



End Sub

Avoid If, Else whenever possible

More often than not people would use an If, Else Statement to test whether a condition is TRUE or FALSE. There is however a slightly faster (and less typing) method. The first example shows the common method, while the second shows a faster method. Of course in such a small example the difference is not noticeable.

Sub TrueOrFalseSlower()



Dim bYesNo As Boolean



	Dim i As Integer







	If i = 5 Then



		bYesNo = True



	Else



		bYesNo = False



	End If







MsgBox bYesNo



End Sub

Here's a better way!

Sub TrueOrFalseFaster()



Dim bYesNo As Boolean



	Dim i As Integer







	bYesNo = (i = 5)



	MsgBox bYesNo







End Sub

Another common need is to toggle a variable between True and False depending on a condition. For example:

Sub ToggleTrueOrFalseSlower()



Dim bYesNo As Boolean







	If bYesNo = False Then



		bYesNo = True



	Else



		bYesNo = False



	End If







	MsgBox bYesNo



End Sub

Here's a much better way

Sub ToggleTrueOrFalseFaster()



Dim bYesNo As Boolean







	bYesNo = Not bYesNo



	MsgBox bYesNo



End Sub

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

Gallery

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
OzGrid is in no way associated with Microsoft.

Website designed by Astrava Solutions Ltd.

OzGrid