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