Hi All,

I want to Redim Preserve an object array to be stored in a variant array. I
can't figure how to do this, if indeed it's possible

Public vArr(1 To 4, 0 To 1)

Sub test()
Dim a1(1 To 10) As Range
Dim a2(1 To 12) As Range
Dim a4(1 To 14) As Range

For i = 1 To 10
Set a1(i) = Cells(i * 2, 1)
Next

vArr(1, 0) = a1
vArr(2, 1) = a2
vArr(4, 0) = a4

Set vArr(2, 1)(5) = [d7]

For i = 1 To 4
For j = 0 To 1
If Not IsEmpty(vArr(i, j)) Then
Debug.Print i; j, UBound(vArr(i, j))
Else: Debug.Print i; j, "Empty"
End If
Next
Next

Debug.Print vArr(1, 0)(10).Address
Debug.Print vArr(2, 1)(5).Address

End Sub


The first two dimensions of the variant array are known in advance and can
be fixed. In the above example the object arrays, a1, a2, & a4 are pre -
dimensioned. That's NOT want I want to do. Instead I want to be able to do
something like this:

x = 20
ReDim Preserve vArr(3, 0)(1 to x) as Range

Obviously this isn't possible - but is there a way?

As an alternative I can do the following

Public rArr() As Range

x = 20
ReDim Preserve rArr(1 to 4, 0 to 1, 1 to x)

Where Ubound of the last dimension, (the "x" in above) must always be the
largest qty of objects assigned to any of the preceding dimensions. This
seems inefficient as the number of objects in the last dimension varies from
none to hundreds. Yet I need an indexing method to set & get my object
arrays as provided by the first two "fixed" dimensions. Maybe there's an
alternative approach?

TIA for suggestions,
Peter T

Peter,

I am not really with you here.

By embedding an array in array, you can get at elements of the former with

Debug.Print vArr(1,0)(8) say

(but I think you know that?).

--
HTH

Bob Phillips

"Peter T" <peter_t@discussions wrote in message
...
Hi All,

I want to Redim Preserve an object array to be stored in a variant array.
I
can't figure how to do this, if indeed it's possible

Public vArr(1 To 4, 0 To 1)

Sub test()
Dim a1(1 To 10) As Range
Dim a2(1 To 12) As Range
Dim a4(1 To 14) As Range

For i = 1 To 10
Set a1(i) = Cells(i * 2, 1)
Next

vArr(1, 0) = a1
vArr(2, 1) = a2
vArr(4, 0) = a4

Set vArr(2, 1)(5) = [d7]

For i = 1 To 4
For j = 0 To 1
If Not IsEmpty(vArr(i, j)) Then
Debug.Print i; j, UBound(vArr(i, j))
Else: Debug.Print i; j, "Empty"
End If
Next
Next

Debug.Print vArr(1, 0)(10).Address
Debug.Print vArr(2, 1)(5).Address

End Sub


The first two dimensions of the variant array are known in advance and can
be fixed. In the above example the object arrays, a1, a2, & a4 are pre -
dimensioned. That's NOT want I want to do. Instead I want to be able to do
something like this:

x = 20
ReDim Preserve vArr(3, 0)(1 to x) as Range

Obviously this isn't possible - but is there a way?

As an alternative I can do the following

Public rArr() As Range

x = 20
ReDim Preserve rArr(1 to 4, 0 to 1, 1 to x)

Where Ubound of the last dimension, (the "x" in above) must always be the
largest qty of objects assigned to any of the preceding dimensions. This
seems inefficient as the number of objects in the last dimension varies
from
none to hundreds. Yet I need an indexing method to set & get my object
arrays as provided by the first two "fixed" dimensions. Maybe there's an
alternative approach?

TIA for suggestions,
Peter T

Hi Bob,

I must admit I had some difficulty in expressing what I'm trying to do (-:

I start with an array of fixed dimensions, say vArr(1 to 4, 0 to 1), though
in real life much larger than this.

Later in my prog' I want to add object arrays to particular locations in
vArr. But I don't know the eventual required size of these object arrays
when I start. I will want to set the size of "some" of them as I go,
starting with UBound (0) and increasing incrementally in various loops as
needs. And of course Preserve'ing what I already have.

vArr(1,0)(8)

Say Ubound of the object array in vArr(1,0) is currently 8. But now I want
to increase it to 9. How would I do that, without assigning to a temporary
array, re-dimensioning and re-assigning back to vArr(1,0).

The way I had been doing this was to place each object array in an array of
Class's, which makes it easy to reference and redimension each object array
individually. But now, for other reasons that would take to long to go into,
this multiple Class method is not practical.

Regards,
Peter T

"Bob Phillips" wrote in message
...
Peter,

I am not really with you here.

By embedding an array in array, you can get at elements of the former with

Debug.Print vArr(1,0)(8) say

(but I think you know that?).

--
HTH

Bob Phillips

"Peter T" <peter_t@discussions wrote in message
...
Hi All,

I want to Redim Preserve an object array to be stored in a variant
array.
I
can't figure how to do this, if indeed it's possible

Public vArr(1 To 4, 0 To 1)

Sub test()
Dim a1(1 To 10) As Range
Dim a2(1 To 12) As Range
Dim a4(1 To 14) As Range

For i = 1 To 10
Set a1(i) = Cells(i * 2, 1)
Next

vArr(1, 0) = a1
vArr(2, 1) = a2
vArr(4, 0) = a4

Set vArr(2, 1)(5) = [d7]

For i = 1 To 4
For j = 0 To 1
If Not IsEmpty(vArr(i, j)) Then
Debug.Print i; j, UBound(vArr(i, j))
Else: Debug.Print i; j, "Empty"
End If
Next
Next

Debug.Print vArr(1, 0)(10).Address
Debug.Print vArr(2, 1)(5).Address

End Sub


The first two dimensions of the variant array are known in advance and
can
be fixed. In the above example the object arrays, a1, a2, & a4 are
pre -
dimensioned. That's NOT want I want to do. Instead I want to be able to
do
something like this:

x = 20
ReDim Preserve vArr(3, 0)(1 to x) as Range

Obviously this isn't possible - but is there a way?

As an alternative I can do the following

Public rArr() As Range

x = 20
ReDim Preserve rArr(1 to 4, 0 to 1, 1 to x)

Where Ubound of the last dimension, (the "x" in above) must always be
the
largest qty of objects assigned to any of the preceding dimensions. This
seems inefficient as the number of objects in the last dimension varies
from
none to hundreds. Yet I need an indexing method to set & get my object
arrays as provided by the first two "fixed" dimensions. Maybe there's an
alternative approach?

TIA for suggestions,
Peter T

As far as I know, what you suggested about using a variant variable to hold
the array and then reassigning it after redimensioning is what works. I
haven't seen another method.

--
Regards,
Tom Ogilvy

"Peter T" <peter_t@discussions wrote in message
...
Hi Bob,

I must admit I had some difficulty in expressing what I'm trying to do (-:

I start with an array of fixed dimensions, say vArr(1 to 4, 0 to 1),
though
in real life much larger than this.

Later in my prog' I want to add object arrays to particular locations in
vArr. But I don't know the eventual required size of these object arrays
when I start. I will want to set the size of "some" of them as I go,
starting with UBound (0) and increasing incrementally in various loops as
needs. And of course Preserve'ing what I already have.

vArr(1,0)(8)

Say Ubound of the object array in vArr(1,0) is currently 8. But now I want
to increase it to 9. How would I do that, without assigning to a temporary
array, re-dimensioning and re-assigning back to vArr(1,0).

The way I had been doing this was to place each object array in an array
of
Class's, which makes it easy to reference and redimension each object
array
individually. But now, for other reasons that would take to long to go
into,
this multiple Class method is not practical.

Regards,
Peter T

"Bob Phillips" wrote in message
...
Peter,

I am not really with you here.

By embedding an array in array, you can get at elements of the former
with

Debug.Print vArr(1,0)(8) say

(but I think you know that?).

--
HTH

Bob Phillips

"Peter T" <peter_t@discussions wrote in message
...
Hi All,

I want to Redim Preserve an object array to be stored in a variant
array.
I
can't figure how to do this, if indeed it's possible

Public vArr(1 To 4, 0 To 1)

Sub test()
Dim a1(1 To 10) As Range
Dim a2(1 To 12) As Range
Dim a4(1 To 14) As Range

For i = 1 To 10
Set a1(i) = Cells(i * 2, 1)
Next

vArr(1, 0) = a1
vArr(2, 1) = a2
vArr(4, 0) = a4

Set vArr(2, 1)(5) = [d7]

For i = 1 To 4
For j = 0 To 1
If Not IsEmpty(vArr(i, j)) Then
Debug.Print i; j, UBound(vArr(i, j))
Else: Debug.Print i; j, "Empty"
End If
Next
Next

Debug.Print vArr(1, 0)(10).Address
Debug.Print vArr(2, 1)(5).Address

End Sub


The first two dimensions of the variant array are known in advance and
can
be fixed. In the above example the object arrays, a1, a2, & a4 are
pre -
dimensioned. That's NOT want I want to do. Instead I want to be able
to
do
something like this:

x = 20
ReDim Preserve vArr(3, 0)(1 to x) as Range

Obviously this isn't possible - but is there a way?

As an alternative I can do the following

Public rArr() As Range

x = 20
ReDim Preserve rArr(1 to 4, 0 to 1, 1 to x)

Where Ubound of the last dimension, (the "x" in above) must always be
the
largest qty of objects assigned to any of the preceding dimensions.
This
seems inefficient as the number of objects in the last dimension
varies
from
none to hundreds. Yet I need an indexing method to set & get my object
arrays as provided by the first two "fixed" dimensions. Maybe there's
an
alternative approach?

TIA for suggestions,
Peter T

Hi Tom,

As far as I know, what you suggested about using a variant variable to
hold
the array and then reassigning it after redimensioning is what works. I
haven't seen another method.

--
Regards,
Tom Ogilvy

So in other words the only way would be something like this:

Public vArr(1 To 4, 0 To 1)
Sub test2()
Dim rArr(1 To 8) As Range
Dim vTmp

vArr(1, 0) = rArr
Set vArr(1, 0)(8) = [a8]

vTmp = vArr(1, 0)

ReDim Preserve vTmp(1 To UBound(vTmp) + 1)

vArr(1, 0) = vTmp
Set vTmp = Nothing

Set vArr(1, 0)(9) = [a9]
Debug.Print vArr(1, 0)(8).Address, vArr(1, 0)(9).Address
End Sub

Yes it works, unfortunately though it appears somewhat slower than the
otherwise memory inefficient -
Public rArr() As Range
ReDim Preserve rArr(1 To 4, 0 To 1, 0 To 9)

and also slower than the multiple Class array method I described (which I
would continue to use except for other associated problems).

Thanks for your advice, as ever much appreciated.

Regards,
Peter T


"Peter T" <peter_t@discussions wrote in message
...
Hi Bob,

I must admit I had some difficulty in expressing what I'm trying to do
(-:

I start with an array of fixed dimensions, say vArr(1 to 4, 0 to 1),
though
in real life much larger than this.

Later in my prog' I want to add object arrays to particular locations in
vArr. But I don't know the eventual required size of these object arrays
when I start. I will want to set the size of "some" of them as I go,
starting with UBound (0) and increasing incrementally in various loops
as
needs. And of course Preserve'ing what I already have.

vArr(1,0)(8)

Say Ubound of the object array in vArr(1,0) is currently 8. But now I
want
to increase it to 9. How would I do that, without assigning to a
temporary
array, re-dimensioning and re-assigning back to vArr(1,0).

The way I had been doing this was to place each object array in an array
of
Class's, which makes it easy to reference and redimension each object
array
individually. But now, for other reasons that would take to long to go
into,
this multiple Class method is not practical.

Regards,
Peter T

"Bob Phillips" wrote in message
...
Peter,

I am not really with you here.

By embedding an array in array, you can get at elements of the former
with

Debug.Print vArr(1,0)(8) say

(but I think you know that?).

--
HTH

Bob Phillips

"Peter T" <peter_t@discussions wrote in message
...
Hi All,

I want to Redim Preserve an object array to be stored in a variant
array.
I
can't figure how to do this, if indeed it's possible

Public vArr(1 To 4, 0 To 1)

Sub test()
Dim a1(1 To 10) As Range
Dim a2(1 To 12) As Range
Dim a4(1 To 14) As Range

For i = 1 To 10
Set a1(i) = Cells(i * 2, 1)
Next

vArr(1, 0) = a1
vArr(2, 1) = a2
vArr(4, 0) = a4

Set vArr(2, 1)(5) = [d7]

For i = 1 To 4
For j = 0 To 1
If Not IsEmpty(vArr(i, j)) Then
Debug.Print i; j, UBound(vArr(i, j))
Else: Debug.Print i; j, "Empty"
End If
Next
Next

Debug.Print vArr(1, 0)(10).Address
Debug.Print vArr(2, 1)(5).Address

End Sub


The first two dimensions of the variant array are known in advance
and
can
be fixed. In the above example the object arrays, a1, a2, & a4 are
pre -
dimensioned. That's NOT want I want to do. Instead I want to be able
to
do
something like this:

x = 20
ReDim Preserve vArr(3, 0)(1 to x) as Range

Obviously this isn't possible - but is there a way?

As an alternative I can do the following

Public rArr() As Range

x = 20
ReDim Preserve rArr(1 to 4, 0 to 1, 1 to x)

Where Ubound of the last dimension, (the "x" in above) must always
be
the
largest qty of objects assigned to any of the preceding dimensions.
This
seems inefficient as the number of objects in the last dimension
varies
from
none to hundreds. Yet I need an indexing method to set & get my
object
arrays as provided by the first two "fixed" dimensions. Maybe
there's
an
alternative approach?

TIA for suggestions,
Peter T