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?).

 

[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

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

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

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

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

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 varies
from

 

[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.

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

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

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

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