Re: How do I Create 'ragged' arrays in Excel VBA?

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

I disagree with John's description of references in this instance.
When you run the
statement A(i) = B you are establishing a reference to the current
array B - a reference which won't be removed until *A(i)* is
reassigned (or destroyed).

I don't believe this is correct. This should convince you that A(i) is
different from B. In otherwords, the array B is copied to A(i), not
referenced: (since B has not been reassigned, if it were only referenced,
A(i)(j) = B(j). But it doesn't. )

Sub RaggedArray()

Dim A() As Variant, B() As Long, i As Long, j As Long
Dim l As Long
ReDim A(1 To 10)
For i = 1 To 10
ReDim B(1 To i)
For j = 1 To i
B(j) = Int(Rnd() * 100 + 1)
Next
A(i) = B
For j = 1 To i
A(i)(j) = 10 * i + j
Next j
If i = 5 Then
For j = 1 To i
Debug.Print i, j, A(i)(j), B(j)
Next
End If

Next i

End Sub

So you should have no concerns using this approach.

--
Regards,
Tom Ogilvy


"David Empey" <dempey@xxxxxxxxxx> wrote in message
news:Xns98EFDF25967FDdempeycruziocom@xxxxxxxxxxxxxx
The following code seems to work, but is it safe?

Sub RaggedArray

Dim A() as Variant, B() as Long, i as Long, j as Long

ReDim A(1 to 10)
For i = 1 to 10
ReDim B(1 to I)
A(i) = B
For j = 1 to i
A(i)(j) = 10 * i + j
Next j
Next i

End Sub

This seems to create an array A whose elements are arrays
of varying lengths, which is what I want, but can I be
sure the elements of A won't be overwritten by some other
piece of code that needs to use memory? Does Visual
Basic know the elements of A exist?

Am I even asking a sensible question?

--
Dave Empey

Remember, if you're doing any major experiments in stellar
dynamics, always mount a scratch star first! --Richard Todd


.

Relevant Pages

  • Re: ReDim not working as expected. Array expert needed.
    ... I next told you not to redim it. ... examples of how array dimensions work in VBA. ... Sub ArrayStudies14() ... Dim MyArray As Variant ' Declare nonarray variant. ...
    (microsoft.public.excel.programming)
  • Re: subroutines, prototyping, and pass by reference question
    ... > There are three main reasons to use references: ... > sub process_string { ... > #we have now copied our 8 million character string ... > There is no way to create, for example, a two-dimensional array without ...
    (comp.lang.perl.misc)
  • Re: Range Object Misunderstanding
    ... So you would need to qualify your references ... The listbox is loaded from that array. ... > End Sub ... > Private Sub CommandButton2_Click ...
    (microsoft.public.excel.programming)
  • Re: a question in VB6
    ... Public Sub Bagels() ... Check online Help for ReDim statement. ... >> data as you grow your array. ...
    (comp.programming)
  • Re: ReDim not working as expected. Array expert needed.
    ... Dim MyArray As Variant ... Don't redim it prior to populating it, because VBA does that anyway, using ... array comes out dimensioned as ... In Sub ArrayStudies1, MyArray is dimensioned as an array of type ...
    (microsoft.public.excel.programming)