VBA Class() object as property of another class

Question

I'm trying to create a class to hold a variable number of items (which are themselves another class object).

So, I have Class 2:

' Class 2 contain each individual quote elements (OTC and MRC)

Private pOTC As String
Private pMRC As String
Public Property Get OTC() As String
    OTC = pOTC
End Property
Public Property Let OTC(Value As String)
    pOTC = Value
End Property

Public Property Get MRC() As String
    MRC = pMRC
End Property
Public Property Let MRC(Value As String)
    pMRC = Value
End Property

Then Class 1 contains an array of Class 2:

Private pCurr As String
Private pQuote(20) As Class2

Public Property Get Curr() As String
    Curr = pCurr
End Property
Public Property Let Curr(Value As String)
    pCurr = Value
End Property

Public Property Set Quote(Index As Integer, cQuote As Class2)
    Set pQuote(Index) = cQuote
End Property

Public Property Get Quote(Index As Integer) As Class2
    Quote = pQuote(Index)
End Property

And what I would like to do is something like:

Dim myQuotes As Class1
Set myQuotes = New Class1

myQuotes.Curr = "GBP"
myQuotes.Quote(3).OTC = "1200"

The first line setting myQuotes.Curr is no problem, however when I try to set a value inside the array the next line errors with Run-time 91 Object variable or With block variable not set

Any pointers as to what I'm doing wrong and how can I set the values for the elements within the class array?

Thanks in advance!

besides the solution to your problem which is below thanks to Alex K., can I just ask (of curiosity) why are you doing it the way you are instead of using a collection of quotes? — user2140173

Alex K. Alex K. · Accepted Answer · 2013-07-05T11:25:04

When you myQuotes.Quote(3) you call Property Get Quote which has an issue.

Your internal array of Class2 is not instantiated so pQuote(Index) refers to an array element of Nothing, when you then myQuotes.Quote(3).OTC = you try to assign to Nothing which fails.

You need to make sure pQuote(Index) is instanced; you can do this on demand:

Public Property Get Quote(Index As Integer) As Class2
   If (pQuote(Index) Is Nothing) Then Set pQuote(Index) = New Class2
   Set Quote = pQuote(Index)
End Property

(Note the required Set)

Or by adding an intitialisation routine to Class1:

Private Sub Class_Initialize()
    Dim Index As Long
    For Index = 0 To UBound(pQuote)
         Set pQuote(Index) = New Class2
    Next
End Sub

VBA Class() object as property of another class

3 Answers