need some array help

[Gary Keramidas]

i want to store an unknown number variables in a 2 dimensional array and can't
figure out how to use redim preserve

here's what i tried

dim arr() as string

redim arr(1 to 1, 1 to 6)

for n = 1 to itemnum.count
ReDim Preserve arr(1 To n, 1 To 6)
then code to add a values
next

on the 2nd loop i always get an error
can someone enlighten me please?

 

[Rick Rothstein \(MVP - VB\)]

When you use the Preserve keyword, you can only change the size of the last
dimension. This is discussed in the help files for the ReDim statement.

Rick

 

[Gary Keramidas]

ok, i see now. then how would i do this:

if i set arr = range("A1:B4") for example and then use the watch window to view
the array, i get this:
this is just an example, i'm not doing this
arr(1,1) = "item"
arr(1,2) = "desc"
arr(1,3) = "cases"
arr(1,4) = "weight"


in code i need to populate the array with values, but i don't know how many
elements i need because i'm in the process of building it in the array:
arr(2,1) = "item"
arr(2,2) = "desc"
arr(2,3) = "cases"
arr(2,4) = "weight

arr(3,1) = "item"
arr(3,2) = "desc"
arr(3,3) = "cases"
arr(4,4) = "weight

and so on
arr(n,1) = "item"
arr(n,2) = "desc"
arr(n,3) = "cases"
arr(n,4) = "weight

 

[Gary Keramidas]

got it worked out by redimming after i know how many elements i need.

thanks

 

[Rick Rothstein \(MVP - VB\)]

You may have another problem. How is arr declared? Because you used the Set
keyword in your example, I'm guessing as a Variant like this...

Dim arr As Variant

because if you had declared it as a dynamic array (that is, with an empty
set of parentheses) like this...

Dim arr() As Variant

your Set statement would have generated an error. I know it is a popular
belief that doing this...

Set arr = Range("A1:B4")

initializes arr as an array. I don't think that is actually what is
happening. What I believe happens is the Range Collection is assigned to arr
and you just end up using its name as an alias for the Range Collection
itself. The Item method is the default method for a Collection Object which
means it does not have to be explicitly specified. Consider this with the
original Range Collection...

Debug.Print Range("A1:B4").Item(1,2)

it will print out whatever is in B1 (first row, second column). Now consider
this where we do not explicitly specify the default Item method...

Debug.Print Range("A1:B4")(1,2)

it will print out the exact same cell value. Now consider what happens when
you do this...

Set arr = Range("A1:B4")

I think arr ends up assigning a reference the Range Collection (an object)
to the Variant arr; but, a Variant can store practically anything... in this
case, it ends up storing a Collection Object, specifically, the Range
Collection. Because of that, you can reference its elements using either
this syntax...

Debug.Print arr.Item(1,2)

or this syntax where the default Item method is not specifically
specified....

Debug.Print arr(1,2)

It is this latter syntax which gets exclusively used and is almost always
(incorrectly I believe) spoken of as an array. Now, that was a long winded
way of saying I don't think you can assign the Range to your Variant
variable and then ReDim Preserve it later on because I don't think it ever
was an array to begin with. What I think you will have to do is Dim an
actual array, initially ReDim it with the elements reverse (if you think
row, column, then make it column, row)... that way you will be able to ReDim
Preserve and do so by changing the last element (the only one you are
allowed to change).

Rick

 

[Rick Rothstein \(MVP - VB\)]

In light of the mini-essay I just posted, I'd be interested in seeing your
code. I'm going to sleep for the night now, so if you post it, I'll look at
it when I get up.

Rick

 

[Bob Phillips]

Gary,

As mentioned, the variable part of an array should be the last dimension,
not the first.

But you should also be aware that Redim Preserve is a very process hungry
action and should not be overdone. It is better to Redim it before loading

dim arr() as string

redim arr(1 to itemnum.count, 1 to 6)

for n = 1 to itemnum.count
then code to add a values
next

If you don't know how many you will be loading, make a high guess, and then
Redim down to the actual size at the end.

--
HTH

Bob

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

 

[Gary Keramidas]

that's what i ended up doing,

thanks for the explanation

 

[Gary Keramidas]

rick, thanks for the explanation. i'm probably not even going to use the code, i
had already done it another way. i was simply trying alternative methods to gain
the same result. i may need to use something like this in the future so i wanted
to see the difference in the 2 methods and see if either was more efficient.

 

[Gary Keramidas]

tanks for the info, dana.

--


Gary

If interested, one workaround is to change the thinking for m(record, field)
to m(field, record)

Sub Demo()
Dim m()
Dim Rec '# of Records

Rec = 1
ReDim m(1 To 3, 1 To Rec)
m(1, Rec) = "a"
m(2, Rec) = "b"
m(3, Rec) = "c"

' Add a new record
Rec = Rec + 1
ReDim Preserve m(1 To 3, 1 To Rec)
m(1, Rec) = "d"
m(2, Rec) = "e"
m(3, Rec) = "f"

Rec = Rec + 1
ReDim Preserve m(1 To 3, 1 To Rec)
m(1, Rec) = "g"
m(2, Rec) = "h"
m(3, Rec) = "i"

'When finished adding...
m = WorksheetFunction.Transpose(m)
[A1].Resize(3, 3) = m
End Sub

But as was mentioned, it's not efficient when the array gets large.
Another option is the Dictionary Object.

--
Dana DeLouis

that's what i ended up doing,

thanks for the explanation