Save time and work efficiently in Excel tool thanks to VBA.
A VBA macro needs to be clear, simple, and faster as much as possible using the appropriate objects.
How many times did you search to optimize code execution? How many times have you been in your work face to an unintelligible VBA module because of a developer who has not let his work easy to take over?
You are in the right place the practices below will help you optimize your macro modules and the execution time. Your code will be more professional and cleaner. It also gives VBA programmers better maintainability. Your code will be simple to understand and quick to read to automate complex processes. We need to find the fastest ways to accomplish a task.
Object Application
The deactivation of the Excel screen or any pop-up will block the screen updating during the code execution.
To save time, at the beginning of your VBA procedure, set at False the objects below, and 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. If you leave the automatic calculation option, Excel will re-calculate all formulas in your sheet during the code execution, which will be a waste of time.
Sub OptimizeTimeCode()
'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
Option Explicit
By using the Option Explicit, you will have to declare all variables (Long, Integer, Boolean, Decimal, String…) in your module. It is a good and useful habit to develop in VBA.
The reading of your code will be simpler Excel will not have to guess the type of your variables, which will improve the execution time. Without the declaration, Excel considers the variable as a Variant.
Option Explicit ' To be write at the beginning of the module
Sub UseOptionExplicit ()
'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.
Declarations
All VBA objects must be explicit, names of modules, procedures, variables, functions.
The more explicit you are, the more your code will be easier to maintain and understandable for everyone. Procedures, subroutines, modules, functions need a name that describes as best the task they perform.
Indentation
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), and it is useful to activate the Auto Indent.
When your code is too long for a line, don’t hesitate to use the line break with the following character “_”. It will avoid scrolling horizontally for reading.
Sub UseLineBreak()
'Use 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 need to continue despite this bug, you will be blocked by a debug window. It is, therefore, preferable to save time to deactivate this function in the VBA option window.
IntelliSense Menu
It 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, you need to check the box “Auto List Member” in the VBA Editor options.
Optimal Algorithm
Improving the efficiency of your code is, above, all finding the solution that best suits your problems. Ask yourself the right questions before starting.
Minimize the use of loops to browse your data.
Choose the right objects according to the amount of information to be processed like custom type objects, arrays, class modules, and dictionaries.
Avoid working directly on the cells of the sheet, prefer to work on values stored in memory.
An optimal algorithm will have a low time complexity.
Comments
Comments allow improving the understanding of code and maintenance for the next developers. Suppose that you need to modify your code one year later. You will be happy to retrieve yourself in what you wrote. It’s 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 WriteComments ()
'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
Clarity
Limit the number of lines in your VBA modules. Keep your code short and straight to the point.
Don’t hesitate to declare several variables on the same line and write an if loop on the same line when it is possible.
Sub IfLine ()
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 see the number of rows, depending on the cursor position in the VBA editor.
Time measurement
Know the time consumed by your code allows you to know if your solution is optimal. To know it, you can use the time function of your machine. Calculate the difference between the time at the beginning and the end of your module.
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 don’t 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, even if the screen update is deactivated. It is better to work directly with the object.
Set reference
If you work with several sheets or workbooks, it is better to Set the object in a defined variable. It will avoid the use of the activate and select method.
Sub SetReferences()
'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
Value2
Instead of using the Value method, it is faster to use the Value2 method in your code.
Sub UseValueTwo()
'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 vbNullString. Write 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 the Application.WorksheetFunction
As much as possible, call the functions already existing in VBA. They will be more efficient than re-creating the methods on objects. You will save reflection time as well as lines. It is essential to structure your code well.
Sub UseWorksheetFunction()
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) named the Zen of Python gives useful advice on development to provide a basic set of guidelines and standards to code in Python. Most of the advice can be applied for development in general, including the VBA language.
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
