What might be the circumstances, if any, in which one might prefer to code:

Dim arr() As String
Redim arr(1 to 4)
arr(1)="ab"
arr(2)="cd"
arr(3)="ef"
arr(4)="gh"

rather than:

Dim arr() As Variant
arr=Array("ab", "cd", "ef", "gh")

TIA,
Alan Beban

Hi Alan,

Alan Beban wrote:
What might be the circumstances, if any, in which one might prefer to
code:

Dim arr() As String
Redim arr(1 to 4)
arr(1)="ab"
arr(2)="cd"
arr(3)="ef"
arr(4)="gh"

rather than:

Dim arr() As Variant
arr=Array("ab", "cd", "ef", "gh")


With either method, you can resize the array, check UBound and LBound, dump
them to Excel ranges directly, and use a For Each Next to step through each
element, so they are both functional enough. So for me, comes down to
efficiency.

Here's a test I just put together - on my machine, using an array of Strings
was over 3x faster than using a Variant to hold the array (test included
setting values, resizing, and adding another element).


'/ TEST CODE
Sub test1()
Dim arr() As String
Dim l As Long
Dim lTimer As Long

lTimer = Timer
For l = 1 To 5000000
ReDim arr(1 To 4)
arr(1) = "ab"
arr(2) = "cd"
arr(3) = "ef"
arr(4) = "gh"

ReDim Preserve arr(1 To 5)
arr(5) = "ij"
Next l

Debug.Print "test1: " & Format$(Timer - lTimer, "0.0000####")
End Sub

Sub test2()
Dim arr2() As Variant
Dim l As Long
Dim lTimer As Long

lTimer = Timer
For l = 1 To 5000000
arr2 = Array("ab", "cd", "ef", "gh")
ReDim Preserve arr2(4)
arr2(4) = "ij"
Next l

Debug.Print "test2: " & Format$(Timer - lTimer, "0.0000####")
End Sub


'/ DEBUG WINDOW RESULTS
test1: 9.46484375
test2: 32.04296875
test1: 8.79492188
test2: 33.79492188

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

"Jake Marx" wrote...
Alan Beban wrote:
What might be the circumstances, if any, in which one might prefer to
code:

Dim arr() As String
Redim arr(1 to 4)
arr(1)="ab"
arr(2)="cd"
arr(3)="ef"
arr(4)="gh"

rather than:

Dim arr() As Variant
arr=Array("ab", "cd", "ef", "gh")
...

How about

Dim rra As Variant
rra = Array("ab", "cd", "ef", "gh")

Note that rra is *NOT* declared as an array, just a variant scalar. Also note
that the arr=Array("ab", "cd", "ef", "gh") call throws an error in XL8/97, so
VBA 5.x.

'/ DEBUG WINDOW RESULTS
test1: 9.46484375
test2: 32.04296875
test1: 8.79492188
test2: 33.79492188

OK, but let's benchmark then torture test this. Also, I'd guess Alan asked this
to show that explicitly declared arrays of particular types are more efficient.
I don't dispute this. However, once these efficient data structures are exposed
to ill conceived procedure calls efficiency gains evaporate.

Here's a comparison with baseline performance of 3 different data types (array
of string, array of variant and scalar variant), then direct assignment of
Array(...) results to the scalar variant, then use of Alan's Assign procedure to
assign Array(...) results to the array of strings. Note that I had to change the
32nd line of Assign from

Dim tempArray()

to

Dim tempArray As Variant

since I'm using XL8/97 to test, and it doesn't support direct assignment to
uninitialized dynamic arrays. This may affect the relative timing results
compared to XL versions useing VBA 6.x. Note that this means Alan's array
function library isn't fully compatible with XL8/97 without modifications.


Sub testemhard()
Const MAXITER As Long = 2000000

Dim arr1() As String, arr2() As Variant, arr3 As Variant
Dim k As Long
Dim t As Long

t = Timer

For k = 1 To MAXITER

ReDim arr1(1 To 4)
arr1(1) = "ab"
arr1(2) = "cd"
arr1(3) = "ef"
arr1(4) = "gh"

ReDim Preserve arr1(1 To 5)
arr1(5) = "ij"

Next k

Debug.Print "test1: " & Format$(Timer - t, "0.0000####")


t = Timer

For k = 1 To MAXITER

ReDim arr2(1 To 4)
arr2(1) = "ab"
arr2(2) = "cd"
arr2(3) = "ef"
arr2(4) = "gh"

ReDim Preserve arr2(1 To 5)
arr2(5) = "ij"

Next k

Debug.Print "test2: " & Format$(Timer - t, "0.0000####")


t = Timer

For k = 1 To MAXITER

ReDim arr3(1 To 4)
arr3(1) = "ab"
arr3(2) = "cd"
arr3(3) = "ef"
arr3(4) = "gh"

ReDim Preserve arr3(1 To 5)
arr3(5) = "ij"

Next k

Debug.Print "test3: " & Format$(Timer - t, "0.0000####")


Erase arr3

t = Timer

For k = 1 To MAXITER

arr3 = Array("ab", "cd", "ef", "gh")
ReDim Preserve arr3(4)
arr3(4) = "ij"

Next k

Debug.Print "test4: " & Format$(Timer - t, "0.0000####")


Erase arr1

t = Timer

For k = 1 To (MAXITER / 50) '** NOTE iterating 2% as many times! **

Assign Array("ab", "cd", "ef", "gh"), arr1
ReDim Preserve arr1(4)
arr1(4) = "ij"

Next k

Debug.Print "test5: " & Format$(Timer - t, "0.0000####")

End Sub


And my Immediate window results,

test1: 7.18359375
test2: 8.515625
test3: 11.765625
test4: 18.05078125
test5: 22.26171875

Note that test5 was based on 2% of the iteration count of the other tests, so
the comparable iteration count result would be 1113.085938. So while assigning
the result of Array(...) to a scalar variant takes roughly 3 times as long as
item by item initialization of an array of strings, using Alan's Assign
procedure to initialize an array of strings takes almost 155 times as long.

Moral: if you're using arrays of particular types in order to wring as much
efficiency as possible out of your code, don't use procedure calls to do simple
things like initialization. Use inline code instead. If you find generating
inline code tedious, learn to use scripting languages or programmer's editors to
write code generators.

--
To top-post is human, to bottom-post and snip is sublime.

I suspected that XL version would have some effect on relative timing
results. It does. I reran the testemhard macro with the test5 For loop
changed to

For k = 1 To (MAXITER / 10) '** NOTE iterating 10% as many times! **

and Alan's Assign macro left as-is. The results were

test1: 9.5703125
test2: 10.9921875
test3: 15.7578125
test4: 26.78125
test5: 36.640625

Using Assign is still more than an order of magnitude slower than the
alternatives, but runs relatively much faster in VBA 6.x.

Harlan Grove wrote:
"Jake Marx" wrote...

Alan Beban wrote:

What might be the circumstances, if any, in which one might prefer to
code:

Dim arr() As String
Redim arr(1 to 4)
arr(1)="ab"
arr(2)="cd"
arr(3)="ef"
arr(4)="gh"

rather than:

Dim arr() As Variant
arr=Array("ab", "cd", "ef", "gh")

. . . I'd guess Alan asked this
to show that explicitly declared arrays of particular types are more efficient.
I don't dispute this.

No. I asked it to see whether users might suggest a more functional
reason. E.g., that if the array were to be later changed or added to,
the type control furnished by, e.g., Dim arr As Integer, which might
have been a better example, might be useful. If so, then the user could
decide for him/herself whether that use of the Assign procedure to
support that usefulness, rather than using inline coding or learning to
use scripting languages or programmer's editors, was outweighed by the
loss of speed efficiency, which might for a particular user be a matter
of fractions of a millisecond.

. . . Note that I had to change the
32nd line of Assign from

Dim tempArray()

to

Dim tempArray As Variant

since I'm using XL8/97 to test, and it doesn't support direct assignment to
uninitialized dynamic arrays. This may affect the relative timing results
compared to XL versions useing VBA 6.x. Note that this means Alan's array
function library isn't fully compatible with XL8/97 without modifications.


I'm not sure whether the broad reference to "array function library" is
intended to mean anything other than the specifically described
incompatibility in the Assign procedure. In any event, that limited
incompatibility is being corrected and will be included in the next
update of the functions at the site.

Alan Beban

"Alan Beban" wrote...
...
No. I asked it to see whether users might suggest a more functional
reason. E.g., that if the array were to be later changed or added to,
the type control furnished by, e.g., Dim arr As Integer, which might
have been a better example, might be useful. If so, then the user could
decide for him/herself whether that use of the Assign procedure to
support that usefulness, rather than using inline coding or learning to
use scripting languages or programmer's editors, was outweighed by the
loss of speed efficiency, which might for a particular user be a matter
of fractions of a millisecond.
...

Fine. Then to summarize the responses so far: the only thing that seems to be
gained by declaring and using dynamic arrays of specific types vs dynamic arrays
of variants or scalar variants holding dynamic arrays seems to be efficiency.
All are equally easy to redimension. Also, arrays of specific types do provide
stronger typing than the other alternatives. Whether that's useful or not in
practical usage is arguable. Evidence that it's not particularly useful is the
fact that very, very little widely used software is written in strongly typed
languages. As for VB[A], try

Sub foo()
Dim a() As String, b() As Double
ReDim a(1 To 5)
ReDim b(1 To 5)
a(1) = 1
b(1) = " 2.345 "
End Sub

Does this generate syntax (compile time) errors or semantic (run time) errors?
No? Then what benefits does sham strong typing provide other than storage and
execution speed efficiency?

Since you seem to dismiss efficiency, then do you really believe

Dim a() As String
Assign Array("foo", "bar"), a

is more convenient than

Dim a As Variant
a = Array("foo", "bar")

especially when one considers the necessity of keeping up with the frequent
revisions to the function library whence Assign comes?

If you're not dismissing efficiency, then how do you assess the run time
difference of using Assign vs inline array initialization? Under what
circumstances would using Assign make sense? Other than stroking your ego, I
don't see much point to it.

--
To top-post is human, to bottom-post and snip is sublime.

Harlan Grove wrote:
. . .As for VB[A], try

Sub foo()
Dim a() As String, b() As Double
ReDim a(1 To 5)
ReDim b(1 To 5)
a(1) = 1
b(1) = " 2.345 "
End Sub

Does this generate syntax (compile time) errors or semantic (run time) errors?

Another of your characteristic straw men. No; but, e.g.:

Sub foo()
Dim a() As String, b() As Double
ReDim a(1 To 5)
ReDim b(1 To 5)
a(1) = 1
b(1) = "ok"
End Sub

does.

Since you seem to dismiss efficiency . . . .

And another. I don't dismiss it; I just think users can decide for
themselves how much weight to give it in their particular applications.

Alan Beban