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.

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


"Bob Phillips" wrote in message
...
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.

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


"Bob Phillips" wrote in message
...
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.

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


"Bob Phillips" wrote in message
...
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.