Can I have an array of arrays and access an element simply?

[peter]

I have
Private aAllCMDRows(1 To MAX_CMD_ROWS) As clsCMDdefn6A
Private aAllMACRows(1 To MAX_WS_ROWS) As clsMACdefn6A
Private aAllWSRows (1 To MAX_OP_ROWS) As clsWSdefn6A
(and another two)
Can I put these in another array:
Private aAllArrays(1 To 5)
using
Const ID_CMD = 1
Const ID_MAC = 2
Const ID_WS = 3

aAllArrays(ID_CMD) = aAllCMDRows '
aAllArrays(ID_MAC) = aAllMACRows
aAllArrays(ID_WS) = aAllWSRows

I want to access them as follows:
Dim oCMD As clsCMDdefn6A
addToArray ID_CMD, oCMD

Sub addToArray( iID As Integer, _
vItem As Variant )
Dim vArray As Variant ' ref to array?
Set vArray = aAllArrays(iID) ' type mismatch
vArray(1) = vItem
End Sub

But got the runtime problem in line 2.

Thanks for your help,
Peter.

(It's for an Excel macro under XP)

 

[Tushar Mehta]

If clsCMDdefn6A et al are user types, VBA has a problem coercing them
to variants. However, if they are a distinct class, the following
works

Class module: clsCMDdefn6A:
Option Explicit

Public i As Integer

Standard module:

Option Explicit
Type clsMACdefn6A
i As Integer
End Type
Type clsWSdefn6A
i As Integer
End Type
Const MAX_CMD_ROWS = 10, MAX_WS_ROWS = 20, MAX_OP_ROWS = 30

Private aAllArrays(1 To 5)

Const ID_CMD = 1
Const ID_MAC = 2
Const ID_WS = 3

Sub testIt()
Dim aAllCMDRows(1 To MAX_CMD_ROWS) As clsCMDdefn6A
Dim aAllMACRows(1 To MAX_WS_ROWS) As clsMACdefn6A
Dim aAllWSRows(1 To MAX_OP_ROWS) As clsWSdefn6A
Set aAllCMDRows(1) = New clsCMDdefn6A
aAllCMDRows(1).i = 1
aAllArrays(ID_CMD) = aAllCMDRows
MsgBox aAllArrays(ID_CMD)(1).i
aAllCMDRows(1).i = 11
MsgBox aAllArrays(ID_CMD)(1).i
aAllCMDRows(1).i = 111
MsgBox aAllArrays(ID_CMD)(1).i
addToArray ID_CMD, aAllCMDRows
MsgBox aAllArrays(ID_CMD)(1).i
'addToArray ID_MAC, aAllMACRows
'aAllArrays(ID_MAC) = aAllMACRows
End Sub

Sub addToArray(iID As Integer, vItem As Variant)
aAllArrays(iID) = vItem
End Sub

Note that to access the nested element one must use ()() and not (,).
The first () gets the element of aAllArrays, the 2nd () accesses the
elment of the nested array. And, of course, the .i should be obvious.

Note also that you can just directly assign the array to the element of
the containing array as in:
aAllArrays(ID_CMD) = aAllCMDRows
and don't have to use a sub to do the assignment.

Finally, note that VBA creates a *referenced* link (the equivalent of a
SET rather than a LET), and doesn't make a copy of the array (as I had
expected). This IMO is a breach of VBA's rules regarding LET and SET.
In any case, the result is that changes to aAllCMDRows() show up in
aAllArrays(). By contrast, the following does create a copy of the
original array

Sub testIt2()
Dim x(1 To 10) As Integer
x(1) = 10
aAllArrays(1) = x
MsgBox aAllArrays(1)(1)
x(1) = 11
MsgBox aAllArrays(1)(1)
End Sub




--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[peter]

Thanks for your help Tushar . I still have some problems though, in
that I'm not all that familiar with VBA, coming from a C/C++/Java
background.

I don't see how adding an object to aAllCMDRows will show up in
aAllArrays

Also in the Sub addToArray(iID As Integer, vItem As Variant), I want
vItem to be a clsCMDdefn6A, clsMACdefn6A, etc

Hope you can clear this up for me,
Peter.

 

[Jamie Collins]

note that VBA creates a *referenced* link (the equivalent of a
SET rather than a LET), and doesn't make a copy of the array (as I had
expected). This IMO is a breach of VBA's rules regarding LET and SET.
In any case, the result is that changes to aAllCMDRows() show up in
aAllArrays(). By contrast, the following does create a copy of the
original array

Sub testIt2()
Dim x(1 To 10) As Integer
x(1) = 10
aAllArrays(1) = x
MsgBox aAllArrays(1)(1)
x(1) = 11
MsgBox aAllArrays(1)(1)
End Sub

Isn't this simply the difference between objects (in the original
example) and Integers (in the testIt2 example). The array in the second
will store a copy of the integer. The array in the former will store a
reference (ObjPtr i.e. 4 byte Long) to the object, rather than a *copy*
of the reference, in order for the array to be an effective container
for object types. So this would be an equivalent example which shows an
array of objects does behave the same way:

Sub testIt3()
Dim aAllArrays(1 To 10) As Variant
Dim y(1 To 10) As Worksheet
Set y(1) = ThisWorkbook.Sheets(1)
aAllArrays(1) = y
MsgBox ObjPtr(aAllArrays(1)(1))
y(1).Name = "Eleven"
MsgBox ObjPtr(aAllArrays(1)(1))
End Sub

Jamie.

--

 

[Jamie Collins]

Now I think about it, this would be a better test:

Sub testIt4()
Dim aAllArrays(1 To 10) As Variant
Dim y(1 To 10) As Worksheet
Set y(1) = ThisWorkbook.Worksheets(1)
aAllArrays(1) = y
MsgBox ObjPtr(aAllArrays(1)(1))
MsgBox aAllArrays(1)(1) Is y(1)
Set y(1) = ThisWorkbook.Worksheets(2)
MsgBox ObjPtr(aAllArrays(1)(1))
MsgBox aAllArrays(1)(1) Is y(1)
End Sub

So aAllArrays(1) is a copy of y, not a reference to y, right?

Jamie.

--