Advice Required: 2D Array within an Array

[fat-tony]

Hi guys,

I am playing with putting an array within an array e.g. TLArray(i).SubArray(3,100)

Option Base 1

Private Type arrayType
    SubArray() As String
End Type

Sub Array_Array()
    Dim TLArray(20) As arrayType
    For i = 1 To 20
        ReDim Preserve TLArray(i).SubArray(3, 100)
    Next i
    For i = 1 To 20
       For x = 1 To 3
          For y = 1 To 100
              TLArray(i).SubArray(x, y) = "Stuff Goes Here"
          Next y
       Next x
    Next i
End Sub

the TLArray will have 20 Sub Arrays. The SubArrays will have 300 entries each in total (3 sets of 100).

Just how much of a memory hog do you guys think this would be?

I was originally thinking of doing: MyArray(20,3,100) would that be a better solution?

Sub Multi_Array()
    Dim SubArray() As String
    ReDim Preserve SubArray(20, 3, 100)
    For i = 1 To 20
       For x = 1 To 3
          For y = 1 To 100
              SubArray(i, x, y) = "Stuff Goes Here"
          Next y
       Next x
    Next i
End Sub

Thanks guys.

All thoughs greatly appreciated.

 

[Richard Schollar]

For those sizes I don't think it makes any difference - for (1) though you need it like this:

Dim myArr(1 to 20)
Dim mySubArr As Variant

mySubArr = Range("A1:C100").Value

MyArr(1) = mySubArr

'etc etc

'then access using:

myArr(1)(98,3) = "A New Value"

 

[fat-tony]

For those sizes I don't think it makes any difference - for (1) though you need it like this:

Dim myArr(1 to 20)
Dim mySubArr As Variant

mySubArr = Range("A1:C100").Value

MyArr(1) = mySubArr

'etc etc

'then access using:

myArr(1)(98,3) = "A New Value"

re: memory usage thats what I thought. Thanks.

I can't use range as I am not going to populate the arrays from ordered cell values like that.

But I do keep forgetting to use .range when it should be used. So thanks, for the reminder.

 

[Richard Schollar]

The Range wasn't important - just used it to populate a 2D array quickly. The essentials of that method is that the parent array holds a further array (2D in this case) in each element (and to access the 'sub array' you need to use the code form given).

 

[fat-tony]

The Range wasn't important - just used it to populate a 2D array quickly. The essentials of that method is that the parent array holds a further array (2D in this case) in each element (and to access the 'sub array' you need to use the code form given).

Sorry I am a bit confused, can you talk me through the differences as to what I used in the first example. What am I missing? Thanks.

 

[Richard Schollar]

Sorry Tony - there's no difference with what you've done. Ignore what I said.

 

[mikerickson]

I don't see any memory issues, but Excel's implementation of UD Types is quirky. I would probably go with a native VBA 3D array.

 

[fat-tony]

Sorry Tony - there's no difference with what you've done. Ignore what I said.

ah ok, cool. Thanks Richard.

I'm still very new VBA and havnt yet got over the nerves