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)

"Sing" wrote in message
...
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?

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)



"Rick Rothstein (MVP - VB)" wrote in
message ...
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'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" wrote in message
...
I wouldn't call that building a dynamic array in either case.

"Rick Rothstein (MVP - VB)" wrote in
message ...
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.

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)" wrote in
message ...
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" wrote in message
...
I wouldn't call that building a dynamic array in either case.

"Rick Rothstein (MVP - VB)" wrote in
message ...
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.