CODEWITHSUNDEEP
vbaexcel
CaptainABC
CaptainABC

Reputation: 1239

Add Collection as an item within another collection - Class - Excel VBA

I'm currently trying to build a collection of items wherein a collection might contain an another collection as an Item within.

I've set two collections and created a class module for each: col1 - (linked to Class1); and col2 - (linked to Class2)

Below are my Class Modules:

Class1:

Option Explicit
Private pTestC1A As String
Private pTestC1B As Collection

Public Property Let TestC1A(Value As String)
pTestC1A = Value
End Property

Public Property Get TestC1A() As String
TestC1A = pTestC1A
End Property

Property Set TestC1B(col2 As Collection)
Set pTestC1B = col2
End Property

Property Get TestC1BElements(v As Integer) As String
    TestC1B = pTestC1B(v)
End Property

Class2:

Option Explicit
Private pTestC2A  As String

Public Property Let TestC2A(Value As String)
pTestC2A = Value
End Property

Public Property Get TestC2A() As String
TestC2A = pTestC2A
End Property

Below is my Module code

Sub Test()

Set col1 = New Collection
Set col2 = New Collection

Set cV = New Class1
cV.TestC1A = "First Collection"

Set aV = New Class2
aV.TestC2A = "Second Collection"
sKey1 = CStr(aV.TestC2A)
col2.Add aV, sKey1

Set cV.TestC1B = col2

sKey2 = CStr(cV.TestC1A)
col1.Add cV, sKey2
    If Err.Number = 457 Then
      MsgBox "Error Occured"
    ElseIf Err.Number <> 0 Then Stop
    End If
    Err.Clear

Msgbox col1(1).TestC1A ' works fine
Msgbox col2(1).TestC2A ' works file  
MsgBox col1(1).TestC1B(1).TestC2A ' does not work - 450 run-time error

End Sub

As per the above code, I'm successfully able to get the values of the items if I reference each collection respectively, however I'm getting a "Wrong number of arguments or invalid property assignment" run-time error if I try to get the item value in a nested fashion.

It would be appreciated if someone can help point out where I'm going wrong, and perhaps shed some light on the way the class module handles the Property Set & Get parameters for a collection.

Upvotes: 2

Views: 2022

Answers (1)

CallumDA
CallumDA

Reputation: 12113

You are missing a Get TestC1B property in your Class1 class module: 

Property Get TestC1B() As Collection
    Set TestC1B = pTestC1B
End Property

Once that is present you will be able to make calls to col1(1).TestC1B(1) and access it's .TestC2A property

Background

You did the right thing by using private variables in your classes and using properties to give read/write access to your private variables. You'll get a lot more control this way.

Property Get gives read access to that property (and broadly speaking, the underlying private variable). For example you can use Range.Address to return (read) the address of a range object.

Property Let and Set give write access to the property. Use Set for objects. For example Range.Value = 1 will write the new value to the range.

Consider, Range.Address = $A$1. Since there is no Property Set for the address of a range, this will not change the address of the range. It will consider the Range.Address part a Get call and evaluate something like $A$1 = $A$1 returning TRUE in this example

Upvotes: 3

Related Questions