Re: ReDim Object array as parameter of Variant array

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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:%23WwREiVVFHA.628@xxxxxxxxxxxxxxxxxxxxxxx
> > 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" <phillips@xxxxxxxxxxxxx> wrote in message
> > news:On#BpGVVFHA.3544@xxxxxxxxxxxxxxxxxxxxxxx
> > > 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:udiQ8NUVFHA.2196@xxxxxxxxxxxxxxxxxxxxxxx
> > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>


.

Quantcast