arrays - 哪种方式编写数组更好(CPU 成本)？

Question

我有一个为工作编写的大型复杂 VBA 脚本。我正在清理它，并注意到我可以用比我所做的更动态的方式定义我的数组。

最初我将数组定义为字符串，如下所示:

Dim header_arr(6) As String
Dim header_arr_2(4) As String

header_arr(0) = "esn"
header_arr(1) = "vin"
header_arr(2) = "last gps update time"
header_arr(3) = "status"
header_arr(4) = "account name"
header_arr(5) = "vehicle #"

header_arr_2(0) = "esn"
header_arr_2(1) = "vin"
header_arr_2(2) = "last gps update time"
header_arr_2(3) = "status"
header_arr_2(4) = "vehicle #"

然后我正在研究如何分配一个数组并设法将其减少为:

Dim header_arr

header_arr = Array("esn", "vin", "last gps update time", "status", "account name", "vehicle #")
' Function call with this first Array

header_arr = Array("esn", "vin", "last gps update time", "status", "vehicle #")
' Function call with this second Array

我的问题是:

我知道对于脚本的这个简单部分，CPU 成本并不相关，但对于更大的数组，编写数组的哪种方法是最低的 CPU 成本(如果有任何差异的话)？

我还没有发现定义数组的不同方法之间的性能差异。

Answer 1

好的，所以我使用变体数组和字符串数组进行了测试。让两者都运行几次，似乎带有非类型化数组的版本甚至稍微快一些。

但更重要的教训是:字符串处理本身(这是一个相当简单的处理，连接字符串和数字)消耗了 75% 的运行时间 - 因此数组本身的数据类型并不重要。

额外说明:通过引用将数组传递给子例程 1000 次是如此之快，以至于我无法测量它。通过 val 传递它杀死了我的 Excel。但是按值传递数组几乎肯定是您无论如何都不想做的事情。

我使用了以下代码:

Option Explicit

Private mlngStart As Long
Private Declare Function GetTickCount Lib "kernel32" () As Long

Const size = 3000000

Dim startTime As Long
Public Sub StartTimer()
    startTime = GetTickCount
End Sub

Public Function EndTimer() As Long
    EndTimer = (GetTickCount - startTime)
End Function


Sub b1()

    StartTimer
    Dim i As Long
    Dim s(size)
    For i = 1 To size
        s(i) = "ABC"
    Next i
    Debug.Print "untyped, size = " & size, EndTimer
End Sub

Sub b2()
    StartTimer
    Dim i As Long
    Dim s(size) As String
    For i = 1 To size
        s(i) = "ABC"
    Next i
    Debug.Print "as string, size = " & size, EndTimer
End Sub

结果是

untyped, size = 3000000      312 
untyped, size = 3000000      313 
untyped, size = 3000000      313 
untyped, size = 3000000      297 
untyped, size = 3000000      313 
as string, size = 3000000    328 
as string, size = 3000000    313 
as string, size = 3000000    328 
as string, size = 3000000    344 
as string, size = 3000000    313 

我将赋值更改为 s(i) = "ABC"& i 并得到以下结果:

untyped, size = 3000000      922 
untyped, size = 3000000      953 
untyped, size = 3000000      938 
untyped, size = 3000000      1015 
untyped, size = 3000000      953 
as string, size = 3000000    1140 
as string, size = 3000000    1156 
as string, size = 3000000    1157 
as string, size = 3000000    1125 
as string, size = 3000000    1125