What way of writing an array is better (CPU Cost)?

Question

I have a large complex VBA script I wrote for work. I am in the process of cleaning it up and noticed I could define my array in what seams like a more dynamic way than I was doing it.

Originally I was defining my Array As String like this:

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 #"

Then I was playing around with how to assign an Array and managed to reduce it to this:

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

My Question is this:

I know that for this simple portion of the script the CPU cost is not relaven but for much larger Array's what method of writing an Array is the lowest CPU cost if any difference at all?

I have not been able to find much on performance differences between the different methods of defining an Array.

header_arr(6) As String is an array of strings. The result returned by Array() is a Variant containing an array of Variants. Obviously the latter has more overhead. — GSerg
– GSerg, Commented Sep 24, 2018 at 14:09
Dim header_arr As Variant is exactly the same as Dim header_arr. You can't assign a Variant/Array to a strictly typed variable, you have to do it line by line. You can, however, pass a strictly typed array to a function without copying the array, provided the function parameter is declared ByRef ... As Variant, and same function would be able to accept a Variant/Array. Using ByVal ... As Variant would result in copying the array upon call. — GSerg
– GSerg, Commented Sep 24, 2018 at 14:20
While a typed array for sure is faster in theory, I doubt that you will be able to measure any difference in real world. No, I am sure you will not... — FunThomas
– FunThomas, Commented Sep 24, 2018 at 14:28
"You have two horses, race them!" - write code for readability and ease of maintenance. Micro-optimizations like this should make no noticeable difference - there is no blanket answer. And since you can't have an array of 3 million items defined inline with Array(...), what's that about then? — Mathieu Guindon
– Mathieu Guindon, Commented Sep 24, 2018 at 14:37
Arrays are limited by available memory AFAIK, but a line of code in the VBE can't exceed 1023 characters, and beyond a dozen line continuations you start hitting "too many line continuations" compile error - hence an inline array can only ever contain so many items. Depending on what you need 3 million records for, a Recordset might be a much better idea. Or the values might be on a worksheet, in which case you get them into a 2D variant array pretty much instantly with a single line of code. Depends what you need =) — Mathieu Guindon
– Mathieu Guindon, Commented Sep 24, 2018 at 14:46

FunThomas · Accepted Answer · 2018-09-24 15:01:20Z

Okay, so I did a test with a variant array and with a string array. Let both run several times, and it seems that the version with the untyped array with even slightly faster.

But the more important lesson: the string handling itself (and this is a rather easy one, concattenating a string and number) consumed 75% of the runtime - so the data type of the array itself doesn't really matter.

An extra remark: Passing the array to a subroutine by reference 1000 times was so fast that I couldn't measure it. Passing it by val killed my excel. But passing an array by value is almost sure something you don't want to do anyhow.

I used the following code:

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

Result was

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

I changed the assignment to s(i) = "ABC" & i and got the following result:

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

Thanks for the info. I need time to finish my project but I will be back to review your answer.

Collectives™ on Stack Overflow

What way of writing an array is better (CPU Cost)?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related