Simple Tips on Speeding up your VB Code

This will stop the screen flickering and updating while executing or running macros, and that will greatly speed up your code.

Sub Stop_ScreenUpdating ()

Application.ScreenUpdating = False

‘… (Your Code)

Application.ScreenUpdating = True

End Sub

Tip: Use Application.ScreenUpdating = False at the beginning of your code and Application.ScreenUpdating = True before ending of your code to control when to stop and start screen updates.

Turn off ‘Automatic Calculations’

This prevents calculations while executing or running macro, so you can wait until a set of actions have been completed and instruct calculations to update at the end once, rather than after every update.

Sub Stop_Calculation()

Application.Calculation = xlCalculationManual

‘… (Your Code)

Application.Calculation = xlCalculationAutomatic

End Sub

Tip: Use Application. Calculation = xlCalculationManual at the beginning of your code and Application. Calculation = xlCalculationAutomatic before ending of your code.

Disable Events

Disabling Excel Events will help you to prevent or stop endless loops while executing or running macros, especially if you have worksheet or workbook events.

Sub Stop_Events()

Application.EnableEvents = False

‘… (Your Code)

Application.EnableEvents = True

End Sub

Tip: Use Application. EnableEvents = False at the beginning of your code and Application. EnableEvents = True before ending of your code.

Use ‘WITH’ Statement

Use the ‘WITH’ statement when working with Objects in macro. If you are using several statements with same object, use ‘WITH’ rather than referencing them all individually.

without a with statement:

Sub Without_WITH()

Worksheets(“Sheet1”).Range(“A1”).Value = 100

Worksheets(“Sheet1”).Range(“A1”).Font.Bold = True

End Sub

using a with statement:

Sub Use_WITH()

With Worksheet(“Sheet1”).Range(“A1”)

.Value = 100

.Font.Bold = True

End With

End Sub

Edit Recorded Macros

While a recorded macro can provide useful insights into Excel’s VBA syntax and references, it is always better avoid using all the code from a recorded macro. It is likely to have a detrimental effect on performance, so always review the macro and edit down the code to ensure only essential executable lines remain.Example: Change cell (“C2”) colour to yellow and font is bold.

Recorded Macro
If you record macro, the code could look like this:

Sub Macro1()

‘

‘ Macro1 Recorded Macro

‘

Range(“C2”).Select

Selection.Font.Bold = True

With Selection.Interior

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

.Color = 65535

.TintAndShade = 0

.PatternTintAndShade = 0

End With

End Sub

Written Macro:
The recorded macro can also be written like this:

Sub Change_Cell_Font()

With Range(“C2”)

.Font.Bold = True

.Interior.Color = 65535

End With

End Sub

Use vbNullString instead of “”

The ‘vbNullString’ is a Constant. It denotes a null String. It occupies less memory than a zero length string (denoted by “”) and is faster to process and to assign.

Label1.Caption = vbNullString

is slightly more efficient than

Label1.Caption = “”

Reduce the number of lines using comma (,) or colon (:)

There are some VBA statements which may be written in a single executable line of code instead of multiple lines.

Example: We can declare variables on the same line, separating each one with a comma:

Sub Declare_Variables()

Dim intFirstNumber As Integer, IntSecondNumber As Integer

End Sub

Instead of the following:

Sub Declare_Variables1()

Dim intFirstNumber As Integer

Dim IntSecondNumber As Integer

End Sub

Example: Use colon (:) to write multiple statements in a single line, for example to assign values to variable:

-3.402823E38 to -1.401298E-45 for negative values;
1.401298E-45 to 3.402823E38 for positive values

Double
(double-precision floating-point)

8 bytes

-1.79769313486231E308 to
-4.94065645841247E-324 for negative values;
4.94065645841247E-324 to 1.79769313486232E308 for positive values

Currency
(scaled integer)

8 bytes

-922,337,203,685,477.5808 to 922,337,203,685,477.5807

Decimal

14 bytes

+/-79,228,162,514,264,337,593,543,950,335 with no decimal point;
+/-7.9228162514264337593543950335 with 28 places to the right of the decimal

Date

8 bytes

January 1, 100 to December 31, 9999

Object

4 bytes

Any Object reference

String
(variable-length)

10 bytes + string length

0 to approximately 2 billion

String
(fixed-length)

Length of string

1 to approximately 65,400

Variant
(with numbers)

16 bytes

Any numeric value up to the range of a Double

Variant
(with characters)

22 bytes + string length
(24 bytes on 64-bit systems)

Same range as for variable-length String

User-defined
(using Type)

Number required by elements

The range of each element is the same as the range of its data type.

Avoid Variants – It’s a simple thing but often overlooked. All variables, parameters and functions should have a defined data type. If the data is a string, then the data type should be defined as string. If you don’t give a data type, you’re using a variant. The variant data type has its uses but not in string processing. A variant means performance loss in most cases.

So add Option Explicit statements to each module and Dim all variables with a decent data type. Review your functions and ensure that they define a return data type.

The following functions are less than ideal if you’re using them on strings as they apply to variants and they return variants. These functions are OK to use if you’re processing variants, but wasteful if working with strings.

Left(), Mid(), Right(), Chr(), ChrW(),

UCase(), LCase(), LTrim(), RTrim(), Trim(),

Space(), String(), Format(), Hex(), Oct(),

Str(), Error

If you’re dealing with strings of text, forget about the variants. Use the string versions instead:

Left$(), Mid$(), Right$(), Chr$(), ChrW$(),

UCase$(), LCase$(), LTrim$(), RTrim$(), Trim$(),

Space$(), String$(), Format$(), Hex$(), Oct$(),

Str$(), Error$

Use the best approach to Copy and Paste

There are different approaches to copying data in VBA. The most efficient is the direct copy action, missing out the Windows Clipboard.Example:

This example above is far more efficient than the “clipboard” method below which sends the copied items to the clipboard to then be pasted:

Sub CopyPaste_Clipboard()

Sheets(“Source”).Range(“A1:E10”).Copy

Sheets(“Destination”).Range(“A1”).PasteSpecial

Application.CutCopyMode = False

End Sub

Only add additional Reference Libraries when necessary

If you use objects in other applications as part of your Visual Basic application, you may want to establish a reference to the object libraries of those applications. Before you can do that, you should check if that the application provides an object library.

To see if an application provides an object library

From the Tools menu, choose References to display the References dialog box.

The References dialog box shows all object libraries registered with the operating system. Scroll through the list for the application whose object library you want to reference.
If the application isn’t listed, you can use the Browse button to search for object libraries (*.olb and *.tlb) or executable files (*.exe and *.dll on Windows).
References whose check boxes are checked are used by your project; those that aren’t checked are not used, but can be added.