[VBA] String() 與 Collection 效能比較

最近在做 Linear Programming ，用 Excel 365 產生目標函數與限制式，由於懶得計算有幾行，所以直接用

Collection.Add

來增加字串，結果資料跑多一點，就花了 02:3x:xx 產生資料，在 VB6 年代我是知道 String() 效能比 Collection 高，但在 VBNET 其實差不多，所以我本來以為微軟會把新技術放到 VB7.x 的 VBA 內，結果沒有，改用 String() 產生後，大概時間縮短為 00:21:34 ，大概誤差 7 倍左右，所以寫點小巨集來測試。

Function TestString(ByVal vCount)
   ReDim arrTest(vCount - 1)
   nbc = -1
   For ibc = 1 To vCount
      nbc = nbc + 1
      arrTest(nbc) = CStr(ibc)
   Next
End Function

Function TestCollection(ByVal vCount)
   Set colTest = New Collection
   For ibc = 1 To vCount
      colTest.Add CStr(ibc)
   Next
   Set colTest = Nothing
End Function

Sub Test()
   Set xsTest = Sheets("Test")
   arrCount = Array(1, 5, 10, 50, 100, 500, 1000, 5000, 10000, 50000, 100000, 500000, 1000000, 5000000, 10000000, 50000000)
   
   With xsTest
      For ibc = 0 To UBound(arrCount)
         .Cells(ibc + 2, 1) = arrCount(ibc)
         .Cells(ibc + 2, 2) = PerformanceCounterSecond()
         
         TestString arrCount(ibc)
         .Cells(ibc + 2, 3) = PerformanceCounterSecond()
         
         TestCollection arrCount(ibc)
         .Cells(ibc + 2, 4) = PerformanceCounterSecond()
         
      Next
   End With
End Sub

測試程式碼如上，很簡單，其中因為 Timer 的精度差，改呼叫 API ，取得的時間達百萬分之一秒。

計算結果如下：

計算時間

左邊四格可以從程式中看到。

右邊三格是依據左邊四格計算時間差，再算比例。

當次數小於 100 次以內，太多干擾導致兩種方案誤差不大。

當次數大於 10000 次以上後，可以看到 Collection 明顯花費時間為 String() 的 7 倍，與我先前的值差不多，應該算是穩定的結果。

我本來是沒打算寫這篇，因為我印象中以前很容易找到 Collection 跟 String() 之間效能比較的文章，但可能時代落差超過 22 年 (VBNET 2002 上市) ，文章都找不到了，基本上都是 VBNET 文章，所以只好自己測一下，記錄下來。