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.
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:
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”:
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:
In this method the code related to the sheet, is placed inside the worksheet itself, therefore the cells and variable can be referenced directly:
This code will only work correctly if it is written inside sheet2:
I have run tests on the 4 methods explained above to find the fastest method. I declared a public variable in sheet 2:
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:
Completion Time: 5.882 Seconds
Completion Time: 5.319 Seconds
In this test the assignment was done 1E8 time and the final result was divided by 100:
Completion Time: 0.049 Seconds
Similar to method 2 the assignment was done 1E8 time and the final result was divided by 100:
Completion Time: 0.017 Seconds
|Method 1||5.882 s|
|Method 2||5.319 s|
|Method 3||0.049 s|
|Method 3||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:
- Excel VLOOKUP vs INDEX and MATCH Speed Comparison
- Excel Speed, IF (Formula vs VBA)
- Excel VBA Working With Sheets