Dynamic multi-dimensional arrays class property?

[tinkythomas]

Hi,

I'm trying to create some dynamic array class properties that are multi-dimensional. However I have been scratching my head for hours trying to get it to work.

The class module...

Option Explicit
Private m_varCellOldValue As Variant
Private m_varCellNewValue As Variant
Private m_varCellOriginalValue() As Variant
Private m_intArrayIndex As Integer
Public Property Get ArrayIndex() As Integer
    ArrayIndex = m_intArrayIndex
End Property
Public Property Let ArrayIndex(ByVal intArrayIndex As Integer)
    m_intArrayIndex = intArrayIndex
End Property
Public Property Get CellNewValue() As Variant
    CellNewValue = m_varCellNewValue
End Property
Public Property Let CellNewValue(ByVal varCellNewValue As Variant)
    m_varCellNewValue = varCellNewValue
End Property
Public Property Get CellOldValue() As Variant
    CellOldValue = m_varCellOldValue
End Property
Public Property Let CellOldValue(ByVal varCellOldValue As Variant)
    m_varCellOldValue = varCellOldValue
End Property
Public Property Get CellOriginalAddress(intIndex As Integer) As Variant
    CellOriginalAddress = m_varCellOriginalValue(intIndex, 0)
End Property
Public Property Let CellOriginalAddress(intIndex As Integer, varCellOriginalAddress As Variant)
    m_varCellOriginalValue(intIndex, 0) = varCellOriginalAddress
End Property
Public Property Get CellOriginalValue(intIndex As Integer) As Variant
    CellOriginalValue = m_varCellOriginalValue(intIndex, 1)
End Property
Public Property Let CellOriginalValue(intIndex As Integer, varCellOriginalValue As Variant)
    m_varCellOriginalValue(intIndex, 1) = varCellOriginalValue
End Property
Public Function GetOriginalValue(strCellAddress As String) As Variant
    ' Variables
    Dim i As Integer
    Dim varOriginalValue As Variant
    ' Loop through array to find cell ref and corresponding value
10  For i = LBound(Me.CellOriginalValue, 1) To UBound(Me.CellOriginalValue, 1)
20      If Me.CellOriginalValue(i, 0) = strCellAddress Then
40          varOriginalValue = Me.CellOriginalValue(i, 1)
50      End If
60  Next i
70  GetOriginalValue = varOriginalValue
End Function

I'm getting Error 9 (Subscript out of range) when trying to assign values to the properties CellOriginalAddress and CellOriginalValue from a standard code module. I have declared the variable m_varCellOriginalValue as dynamic and not static.

Where am I going wrong?

Any help would be appreciated.

Regards,

 

[shg]

You never initialize the dynamic array. You can't refer to dimensions before doing so.

EDIT: That wasn't very lucid. You're getting the same error you get here:

Sub x()
    Dim ai() As Long
 
    ai(1) = 23
End Sub

 

[tinkythomas]

Thanks for the reply. Whoops I missed that I have now added the ReDim Preserve in the property let proc...

Public Property Let CellOriginalAddress(intIndex As Integer, varCellOriginalAddress As Variant)
    ReDim Preserve m_varCellOriginalValue(0 To intIndex, 1)
    m_varCellOriginalValue(intIndex, 0) = varCellOriginalAddress
End Property

However I now have the same error on subsequent calls to add more elements.

Any ideas?

Thanks for your help so far.

 

[shg]

ReDim Preserve m_varCellOriginalValue(0 To intIndex, 1)

You should get in the habit of declaring the lower bound for all array dimensions.

I now have the same error on subsequent calls to add more elements.

You can only change the last dimension of an array when using the Preserve keyword.

 

[tinkythomas]

You should get in the habit of declaring the lower bound for all array dimensions.

Yes, I agree entirely. I have some bad habits that I need to get out of.

You can only change the last dimension of an array when using the Preserve keyword.

That hit the nail on the head. I have now changed my dimensions and all is working as expected now.

Thank you very much for your help, it's appreciated.

 

[shg]

I have some bad habits that I need to get out of.

Don't we all ...

You're welcome.

 

[shg]

Why aren't you using a collection or a dictionary instead of an array?

 

[tinkythomas]

Why aren't you using a collection or a dictionary instead of an array?

Good question, is there a performance/memory advantage to using a collection? Perhaps you could give some samples of how I would implement a collection in my class?

Thanks foor your help!

 

[shg]

Collections and dictionaries are both very fast, and you can retrieve an Item by its Key , so you don't need to loop through an array.

Collections and Dictionaries are different; here's a list I've been meaning to make, surely not complete:

<TABLE style="WIDTH: 349pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=465><COLGROUP><COL style="WIDTH: 103pt; mso-width-source: userset; mso-width-alt: 6290" width=138><COL style="WIDTH: 119pt; mso-width-source: userset; mso-width-alt: 7241" width=158><COL style="WIDTH: 127pt; mso-width-source: userset; mso-width-alt: 7716" width=169><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 103pt; HEIGHT: 12pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 height=16 width=138>Characteristic</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 119pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 width=158>Collection</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 127pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 width=169>Dictionary</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Data Type of Item</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>Anything</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>Anything</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Data type of Key</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>String</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>Anything but an array or object</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Iterate each Item</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>For Each vItem in col</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>For Each v in dic.Items</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Iterate Items by position</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>For i = 1 to col.Count</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>No</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Iterate Keys</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>No</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>For Each v in dic.Keys</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Add Item</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>Add Item:=vItem [, Key:=sKey]</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>dic.Add Key:=vKey, Item:=vItem</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Item requires Key</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>No</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>Yes</TD></TR><TR style="HEIGHT: 24pt" height=32><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 24pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=32>Retrieve Item by Key</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 119pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=158>vItem = col(sKey) or
Set vItem = col(sKey)
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 127pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=169>vItem = dic.Item(vKey) or
Set vItem = dic.Item(vKey)
</TD></TR><TR style="HEIGHT: 24pt" height=32><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 24pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=32>Retrieve Item by position</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 119pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=158>vItem = col(i) or
Set vItem = col(i)
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>No</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Retrieve all Keys</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>No</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>avKey = dic.Keys</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Retrieve all Items</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>No</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>avItem = dic.Items</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Test for existence of a Key</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>No</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>bExists = dic.Exists(vKey)</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Delete Item by position</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>.Remove i</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>No</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Retrieve Key from Item</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>No</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>No</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Clear</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>Set col = New Collection</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>dic.RemoveAll</TD></TR></TBODY></TABLE>

 

[tinkythomas]

Thanks for the comprehensive comparison between collections and dictionaries! Could you offer a code snippet of a collection in a class?

Thanks again.

Regards,