Speed of fixed array versus dynamic array

[Sing]

Dear Excel Gurus,

I am thinking of ways to optimize a slow Excel VBA program.

Will using fixed array size instead of dynamic array significantly speed up
the operation?

Also, I have been using dynamic arrays but did not use Redim. Is this wrong?

 

[Bob Phillips]

That would depend on factors, such as
- how much of the fixed array is populated/empty
- how will you populate the arrays, etc.

Time both options, and see which is best, but my guess is that the time
spent is elsewhere, unless you do a lot of array processing.

How did you build a dynamic array without using Redim?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

How did you build a dynamic array without using Redim?

You can use an assignment from the Split and Array functions, as well the
directly assigning another (already dimensioned) array, to do that.

Rick

 

[Bob Phillips]

I wouldn't call that building a dynamic array in either case.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

I'm not sure, then, that I understand your use of the word "building". Why
would something like this....

Dim Items() As String
Items = Split("One,Two,Three,Four", ",")

or this...

Dim Things() As Variant
Things = Array(1, 2, 3, 4)

not be considered building a dynamic array? As a side point, you introduced
the word "building"... the OP's original statement was

"Also, I have been using dynamic arrays but did not use Redim."

Rick

 

[Bob Phillips]

Because the array is fixed, not pre-determined perhaps, but fixed, it cannot
flex as the code proceeds. That is what I understand by dynamic.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

Okay, I think I see what you are saying; however I see it a little
differently than you. To me, this...

Dim Items() As String
Items = Split("One,Two,Three,Four", ",")

and this...

Dim Items() As String
ReDim Items(3)
Items(0) = "One"
Items(1) = "Two"
Items(2) = "Three"
Items(3) = "Four"

are equivalent (and similarly for the Array function). When finished, the
Items array contains the same 4 elements for either assignment method. And
in both cases, Items can be ReDim'med later on, if needed. Anyway, that is
basically the thinking behind my previous reply to you.

Rick

 

[RB Smissaert]

Not sure I get this. Look at this:

Sub test()

Dim i As Long
Dim arr() As String

arr = Split("a b c d", Chr(32))

For i = LBound(arr) To UBound(arr)
MsgBox arr(i), , i
Next i

ReDim Preserve arr(0 To 10)

For i = LBound(arr) To UBound(arr)
MsgBox arr(i), , i
Next i

End Sub


RBS

 

[Peter T]

For the sake of contrivance, another way to dynamically size

Sub test2()
Dim r&, c&
Dim arr()
r = 10: c = 2

arr = Range(Cells(1, 1), Cells(r, c))

MsgBox UBound(arr) & " : " & UBound(arr, 2)

End Sub