ReDim Object array as parameter of Variant array

[Peter T]

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

[Bob Phillips]

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
news:[email protected]...
> 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 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" <[email protected]> wrote in message
news:On#[email protected]...
> 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
> news:[email protected]...
> > 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
> >
> >
>
>

[Tom Ogilvy]

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
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:On#[email protected]...
> > 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
> > news:[email protected]...
> > > 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 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
> news:%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:On#[email protected]...
> > > 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
> > > news:[email protected]...
> > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>