Last Updated on February 9, 2022 – 7 min read
Save time and work efficiently in Excel with VBA.
A VBA macro needs to be clear, simple, and faster by using the appropriate developing objects.
How optimize a VBA macro? How can you improve your code? How do you speed up your VBA execution?
In this article, you will discover best practices to optimize the execution time of your VBA macro. Your code will be more professional and cleaner. It also gives VBA programmers better maintainability.
Your code needs to be simple to understand and read to automate complex processes.
How do you make your Excel VBA code run 1000 times faster?
The deactivation of the Excel screen or any pop-up blocks the screen from updating during the code execution.
To save time, at the beginning of your VBA procedure, set at False the objects below. Remove the automatic calculation of your workbook.
Don’t forget to set these objects at True at the end of the program and re-activate the automatic calculation of formulas if necessary.
Sub Optimize_Time_Code() 'Remove the update of Excel screen Application.ScreenUpdating = False Application.EnableEvents = False 'Remove pop-up Application.DisplayAlerts = False Application.AskToUpdateLinks = False 'Remove the automatic calculation Application.Calculation = xlCalculationManual '... your code 'To be re-activated at the end of the process Application.ScreenUpdating = True Application.EnableEvents = True Application.DisplayAlerts = True Application.AskToUpdateLinks = True Application.Calculation = xlCalculationAutomatic End Sub
By using the Option Explicit, you have to declare all variable types such as Long, Integer, Boolean, Decimal, String in your module. It is a good practice to develop in VBA.
The reading of your code is simpler when Excel does not need to guess the type of your variables. Without the declaration, Excel considers the variable as a Variant.
Option Explicit ' write at the beginning of the module Sub Use_Option_Explicit () 'Procedure to show how works the Option Explicit 'This variable is declare using Dim Dim i As Integer i = 10 MyResult = i*10 'code error as MyResult is not declared End Sub
The memory allocated for each object is different.
All VBA objects must be explicit, including the module’s name, procedures, variables, functions.
The more explicit you are, the more your code is easier to maintain. Procedures, subroutines, modules, functions need a name that describes as best the task perform.
In VBA code, unlike Python, the code indentation is not mandatory, however, it gives better readability and better maintenance. It is essential to think about the future developers that will have the opportunity to revisit your work.
Sub Indentation() 'Name of the module 'Declaration Dim i As Long 'indentation in the module 'Loop from 1 to 10 Step 1 For i = 1 To 10 If i = 2 Then 'indentation in the loop i = i + 1 'indentation in the if MsgBox "Hello World !" 'Display End If Next i End Sub
You can define the length of your tab indentation in the options. By default, it is set to 4 spaces. You can activate the Auto Indent.
When your code is too long on a line, use the line break with the underscore character _. It avoids scrolling horizontally for reading.
Sub Use_Line_Break() 'Example of a line break ' The character "&" is used to concatenate many strings MsgBox "Education is the most powerful weapon " & _ " you can use to change the world. " & _ "Nelson Mandela" End Sub
Auto Syntax Check
VBA checks the consistency after each word writes in the code. In case of any issue, the anomaly is highlighted in red. If you continue to write code despite this bug, you will be blocked by a debug window. It is, therefore, preferable to save time by deactivating this function in the VBA option window.
The IntelliSense is a dropdown menu that appears after typing a point. The list contains the members of the active references, including objects, properties, methods, constants.
To activate the IntelliSense menu, check the box Auto List Member in the VBA Editor options.
Finding the solution that best suits your problems will improve the efficiency of your code. Ask yourself the right questions before starting.
Minimize the use of loops to browse your data.
Avoid working directly on the cells of your sheet. It is better to work on values stored in memory.
An optimal algorithm will have a low time complexity.
Comments improve the understanding of your code and the maintenance for developers. Suppose that you need to update your code in one year. It is a good practice to comment code for later use.
Be explicit and concise as possible in your comments to explain what your code is trying to accomplish. In VBA, explanations are written in green using apostrophes.
Sub Write_Comments () 'Write a comment to explain the purpose 'of the Sub 'Declarations Dim i As Integer 'i will be a counter Dim a As Long Dim sh As Sheet 'Initialisation a = 2 For i = 1 To 10 ... 'Explain the purpose of loop Next i End Sub
Limit the number of lines in your VBA modules. Keep your code short and straight to the point.
You can declare several variables on the same line and write an if loop on the same line when possible.
Sub If_Line () Dim i, a, b As Integer i = 2 If i = 2 Then a = 2 'If with one action 'If with several actions to perform If i = 3 Then a = 3 b = 3 End If End Sub
You can check the number of code lines, depending on your cursor position in the VBA editor.
Knowing the time consumed by your code allows you to know if your solution is optimal.
You can use the time of your machine to calculate the difference between the time at the beginning and the end of your macro.
Sub TimeMeasurement() 'Declaration Dim x, y, z As Double x = Timer '... code to estimate ... y = Timer z = y - x MsgBox "Time elapsed : " & z & " sec." End Sub
Activate & Select
Try to not use the select and activate method for sheet, workbook, range, cell, column, row as much as possible. Your code will be faster without these methods. It is better to work directly with the object.
If you work with several sheets or workbooks, it is better to Set the object in a defined variable. It avoids the use of activating and selecting methods.
Sub Set_References() 'Declaration Dim ShData As Worksheet Dim ShResults As Worksheet Dim WbMacro As Workbook Dim WbResults As Workbook Dim RngData As Range 'Initialisation Set WbMacro = Workbooks("Macro.xlsx") Set WbResults = Workbooks("Results.xlsx") Set ShData = WbMacro.Sheets("Data") Set ShResults = WbResults.Sheets("Results") Set RngData = WbMacro.Sheets("Data").Range("A1:Z26") End Sub
Instead of using the Value method, it is faster to use Value2 in your code.
Sub Use_Value_Two() 'Declaration Dim a, b As Double a = Range("A1").Value 'Value2 method is faster than Value b = Range("A1").Value2 End Sub
Empty or blank
If you want to set a variable as empty, it is better to use the double quotes “” instead of the vbNullString method. Writing the double quotes is faster than vbNullString.
Sub UseDouble Quotes() 'Declaration Dim a, b As String a = "weather" b = "" c = vbNullString If b = "" Then c = "" 'if condition on one row End Sub
Use Existing Functions
As much as possible, call the functions already existing in VBA. It is more efficient than re-creating the methods on objects. It is essential to structure your code well.
Sub Use_Worksheet_Function() Dim TotalAmount As Long With Application.WorksheetFunction TotalAmount = .Sum(Sheets("Calculator").Range("D1:D500")) End With End Sub
The Zen of Python
The PEP 20 (Python Enhancement Proposals) called the Zen of Python gives pieces of advice on development to provide a basic set of guidelines and standards to code in Python.
Beautiful is better than ugly
Explicit is better than implicit
Simple is better than complex
Flat is better than nested
Sparse is better than dense
Special cases aren’t special enough to break the rules
Although practicality beats purity
Errors should never pass silently
Unless explicitly silenced
In the face of ambiguity, refuse the temptation to guess
There should be one — and preferably only one — obvious way to do it
Although that way may not be obvious at first unless you’re Dutch
Now is better than never
Although never is often better than right now
If the implementation is hard to explain, it’s a bad idea
If the implementation is easy to explain, it may be a good idea;
Namespaces are one honking great idea — let’s do more of those
It can be applied for development in general, including the VBA language.