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.


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:

Excel, worksheet Names
Example:

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

Excel, worksheet Index
Example:

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:

Excel, worksheet Object Names
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:

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”

Leave a Reply

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