Speeding Up Slow Excel VBA Code

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

---

Don't get caught in the Loop.
Follow this link to see why Loops should (and usually can) be avoided.

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

사이트 : http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm