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]