Speed of fixed array versus dynamic array

  • Thread starter Thread starter Sing
  • Start date Start date
S

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?
 
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)
 
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
 
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)
 
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
 
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)
 
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
 
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
 
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
 
Back
Top