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:

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

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:

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:

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:

• Sheet Reference Speed Test.xlsm

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