目錄

• 概述
• 測試速度的代碼
• 測試結果

概述

Range和Cells這兩個函數，都可以獲取單元格內容和將內容寫入單元格。既然這兩個功能相同，那麼速度就成為了選擇的關鍵。

註：Range有著比Cells更強大的功能，大家千萬不要把這兩個函數完全等同。當然了，這也從側面反應了速度上必然Range更慢。

測試速度的代碼

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 test()
Dim i As Long
Dim a As String

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

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 = Range("A" & CStr(i)) 'Range read
a = Cells(i, 1) 'Cells read
Cells(i, 1) = a 'Cells write
Range("A" & CStr(i)) = a 'Range write
Next i

QueryPerformanceCounter m_Now

Elapsed = 1000 * (m_Now - m_Start) / m_Frequency
Debug.Print Elapsed
End Sub

測試結果

測試結果的時間單位是毫秒。