Excel VBA, Sheet Reference Speed
In the article Excel VBA Working With Sheets, I’ve explained that there are 3 different methods for referencing sheets. I have run tests on these methods to determine the fastest way of referencing sheets. Before explaining the tests, I have briefly explained the different methods of referencing sheets.
Contents
Referencing Sheets:
Method 1:
Using the sheet name. By default excel starts with 3 sheets with the names “Sheet1”, “Sheet2”, “Sheet3”. The names can be seen in the bottom tab:
Sheets("Sheet2").cells(1, 1) = "Something"
or
Worksheets("Sheet2").cells(1, 1) = "Something"
Method 2:
Using the sheet index. The sheet index indicates the position of the sheet in the bottom tab. By default “Sheet1” has index 1, “Sheet2” has index “2” and “Sheet3” has index 3. By moving the sheets around their index also changes. For example in the figure below, “Sheet1” has indbex “2” and “Sheet2” has index “1”:
Worksheets(2).Cells(1, 1) = "Something"
or
Sheets(2).Cells(1, 1) = "Something"
Method 3:
Using the sheet object name. Each sheet object has a name. The sheet object name can only be changed through the property window in the VBA editor:
Example:
Sheet2.Cells(1, 1) = "Something"
Method 4:
In this method the code related to the sheet, is placed inside the worksheet itself, therefore the cells and variable can be referenced directly:
Example:
This code will only work correctly if it is written inside sheet2:
Cells(1, 1) = "Something"
Speed Test:
I have run tests on the 4 methods explained above to find the fastest method. I declared a public variable in sheet 2:
Public intVar As Long
This variable is assigned a value 1E6 times. The start and end times are recorded and thus the total amount of time to accomplish the test is obtained.
Below you can see the code used to test each method:
Method1 :
'Method 1
Sub Test1()
Dim i As Long
Dim intTime1 As Long
Dim intTime2 As Long
intTime1 = GetTickCount
'''
'Start
For i = 1 To 100000
Sheets("sheet2").intVar = 1
Next i
'End
'''
intTime2 = GetTickCount
MsgBox (intTime2 - intTime1)
End Sub
Completion Time: 5.882 Seconds
Method 2:
'Method 2
Sub Test2()
Dim i As Long
Dim intTime1 As Long
Dim intTime2 As Long
intTime1 = GetTickCount
'''
'Start
For i = 1 To 1000000
Sheets(2).intVar = 1
Next i
'End
'''
intTime2 = GetTickCount
MsgBox (intTime2 - intTime1)
End Sub
Completion Time: 5.319 Seconds
Method 3:
In this test the assignment was done 1E8 time and the final result was divided by 100:
'Method 3
Sub Test3()
Dim i As Long
Dim intTime1 As Long
Dim intTime2 As Long
intTime1 = GetTickCount
'''
'Start
For i = 1 To 100000000
Sheet2.intVar = 1
Next i
'End
'''
intTime2 = GetTickCount
'The calculation was done 1E8 times and the time was
'divided by 100
MsgBox (intTime2 - intTime1) / 100
Sheet2.Cells(1, 1) = "Something"
Sheet2.Cells(1, 1) = "Something"
End Sub
Completion Time: 0.049 Seconds
Method 4:
Similar to method 2 the assignment was done 1E8 time and the final result was divided by 100:
'Method 4
Sub Test4()
Dim i As Long
Dim intTime1 As Long
Dim intTime2 As Long
intTime1 = GetTickCount
'''
'Start
For i = 1 To 100000000
intVar = 1
Next i
'End
'''
intTime2 = GetTickCount
'The calculation was done 1E8 times and the time was
'divided by 100
MsgBox (intTime2 - intTime1) / 100
Sheet2.Cells(1, 1) = "Something"
Sheet2.Cells(1, 1) = "Something"
End Sub
Completion Time: 0.017 Seconds
Results:
Method | Example | Completion Time |
Method 1 | Sheets("Sheet2").cells(1, 1) = |
5.882 s |
Method 2 | Sheets(2).cells(1, 1) = |
5.319 s |
Method 3 | Sheet2.cells(1, 1) = |
0.049 s |
Method 3 | cells(1, 1) = |
0.017 s |
The following results can be obtained from this test:
- Referencing sheets using the sheet index and name is very inefficient. (Sheets(“Sheet2). and Sheets(2).
- The best method for referencing sheets is using the sheet object variable. (Sheet2.)
- If speed is essential, try to place the code for every sheet inside the sheet. This way there won’t be a need to use sheet references.
You can download the file and code for this article from the link below:
See also:
- Excel VLOOKUP vs INDEX and MATCH Speed Comparison
- Excel Speed, IF (Formula vs VBA)
- Excel VBA Working With Sheets
If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com
One thought on “Excel VBA, Sheet Reference Speed”