VBA中Sheets(String)和Sheets(Long)的速度

概述

Sheets(String)和Sheets(Long)这两种调用方法，都可以从Sheets集合中返回一个工作表。既然这两个功能相同，那么速度就成为了选择的关键。

测试速度的代码

Private Declare Function QueryPerformanceCounter Lib "KERNEL32" (lpPerformanceCount As Currency) As Long
Private Declare Function QueryPerformanceFrequency Lib "KERNEL32" (lpFrequency As Currency) As Long

Private m_Frequency   As Currency
Private m_Start       As Currency
Private m_Now         As Currency
Private m_Available   As Boolean

Sub TestSpeet()

m_Available = (QueryPerformanceFrequency(m_Frequency) <> 0)
If Not m_Available Then
Debug.Print "Performance Counter not available"
End If

Dim i As Long
Dim a As String

Cells.NumberFormatLocal = "@"
For i = 1 To 100000 Step 1
    Cells(i, 1) = CStr(i)
Next i

QueryPerformanceCounter m_Start

For i = 1 To 100000 Step 1
    ' 下面两句中选一句执行
    a = Sheets(1).Cells(i, 1)        ' Sheets(Long)
    a = Sheets("Sheet1").Cells(i, 1) ' Sheets(String)
Next i

QueryPerformanceCounter m_Now

Elapsed = 1000 * (m_Now - m_Start) / m_Frequency
Debug.Print Format(Elapsed, "#.0000")
End Sub

测试结果

测试结果的时间单位是毫秒。

结论

使用Sheets(String)比Sheets(Long)要慢。

所以应该优先使用Sheets(Long)。