Top Ten Tips To Speed Up Your VBA Code

1. Turn off Screen Updating

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.

2. 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.

3. 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.

4. 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

5. 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

6. 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 = ""

7.  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:

Sub Use_Colon_ForMultipleLine()
 Dim intFirstNumber As Integer, IntSecondNumber As Integer
 intFirstNumber = 5: intSecondNumber = 10 
End Sub

Instead of the following example:

Sub Use_Colon_ForMultipleLine1()
 Dim intFirstNumber As Integer, IntSecondNumber As Integer
 intFirstNumber = 5 intSecondNumber = 10 
End Sub

8. Declare Variables with the smallest viable data type size

Always make sure you declare your variables and utilise the smallest data type possible, especially for numbers.

Dim intRowCount as Integer

Check the data range for the capacity of values needing to be held:

Data type Storage size Range
Byte 1 byte 0 to 255
Boolean 2 bytes True or False
Integer 2 bytes -32,768 to 32,767
Long
(long integer)
4 bytes -2,147,483,648 to 2,147,483,647
Single
(single-precision floating-point)
4 bytes -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 wastefull 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$

9. 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:

Sub CopyPaste_Direct()
 Sheets("Source").Range("A1:E10").Copy Destination:=Sheets("Destination").Range("A1")
End Sub

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

10. 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

  1. From the Tools menu, choose References to display the References dialog box.
  2. 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.

Consider Late Binding instead of Early Binding as an alternative.

See https://support.microsoft.com/en-gb/kb/245115

Check our calendar for scheduled VBA training courses in Glasgow and Edinburgh or contact us to discuss your specific training requirements in more detail

Leave a Reply

Your email address will not be published. Required fields are marked *