Excel vba: Property let procedure not defined and property get procedure did not return an object

Question

I have a Client class. Inside that class there is an array losses. First I create and populate with clients a clientsColl array. Then for each client in that array I populate its losses array.

Then I try to print into debug a first element of losses for each client. However, it doesnt work and Property let procedure not defined and property get procedure did not return an object error appears.

And the same time if I just try to display a first element of losses for the first client, without any cycle, it works fine:

Dim clientsColl() As Client
clientsColl = getClients(dataWorkbook)

Dim clientCopy As Variant    

Debug.Print "first: " & clientsColl(1).getLosses(1) 'works fine
For Each clientCopy In clientsColl
    Debug.Print "in for each: " & clientCopy.getLosses(1) 'error here
Next

In Client class:

Public Property Get getLosses()
    getLosses = losses
End Property

Private losses() As Double

How the losses array is populated:

Public Sub calculateFinancialResult()
    ReDim losses(1 To simulationCount)
    ReDim profits(1 To simulationCount)

    Dim i As Long
    For i = 1 To simulationCount
        If outcomes(i) = 1 Then
            losses(i) = totalLoss
            ...
        Else
            ...
        End If
    Next
 End Sub

Why does this happen and how to fix it?

EDIT: more of the main sub:

For Each clientCopy In clientsColl
        clientCopy.setSimulationCount = globals("SIMULATION_COUNT")
        ...
        clientCopy.calculateFinancialResult
        ...
Next

EDIT:

At the same time a simple for cycle works fine:

Debug.Print "first: " & clientsColl(1).getLosses(1)
For tempCount = LBound(clientsColl) To UBound(clientsColl)
    Debug.Print "in for each: " & _
                clientsColl(tempCount).getLosses(1)
Next

Answer 1

To conclude what was said in comments:

Your problem (error 451) often occures when you trying to compound properties. To represent this case we can use any structure of any object with properties.

Let's emulate it with array of collections:

Option Explicit

Sub Test()
    Dim Arr As Variant
    Dim Col As Collection
    Dim i As Long
    Dim j As Long

    ReDim Arr(1 To 10)

    For i = 1 To 10
        Set Col = New Collection
        For j = 1 To 10
            Call Col.Add(j)
        Next

        Set Arr(i) = Col
    Next

    On Error Resume Next
    Debug.Print Arr(1).Item(1)
    Debug.Print Arr(1).Item()(1)
    On Error GoTo 0
End Sub

Your problem stems from the fact that you're treating your properties as attributes. On not-so-compounded (or when your array is declared explicitly as array of class instances) level it works due to early binding. But when things start to get more complex - it's fail, since your property just another function.

Hence, to achieve what you want, you should call it explicitly with another pair of parentheses.

Answer 2

Your getLosses property doesn't take an argument so your syntax is actually wrong, even though VBA can cope with it when early bound. You should be using:

Debug.Print "first: " & clientsColl(1).getLosses()(1) 'works fine
For Each clientCopy In clientsColl
    Debug.Print "in for each: " & clientCopy.getLosses()(1) 'error here
Next