Why does accessing a ListRow as a Property fail?-CodePudding

In a Windows Excel 365 Subscription macro, I have an object that contains a ListRow as a variable. I can access it directly as a Public variable, but not through a Property. If I use the Property, it won't run, giving a 91 RTE at the line where I assign a value to the property.

'Object1 contains a ListRow as a variable and a Property to access it.

Public myListRow as ListRow

Public Property Get Row () as ListRow
    Set Row = myListRow
End Property

Public Property Set Row (value as ListRow)
    Set myListRow = value
End Property

'Object2 tries to assign a value to Row, but fails.

Private aTable As ListObject

Private Sub Class_Initialize()
    Set aTable = Sheet1.ListObjects("MyTable")
End Sub

Private Sub DoSomething()
    Dim AnObject As Object1
    
    Set AnObject.myListRow = aTable.ListRows(1) 'works just fine

    Set AnObject.Row = aTable.ListRows(1) 'fails with a 91 error - Object variable or 
                                          'With block variable not set
End Sub

Why is it doing this?

CodePudding user response：

First, since DoSomething() is declared as Private, you should have received a compile error when trying to access this property. It should be declared as Public.

Secondly, while you've declared AnObject as Object1, you haven't actually created an instance of that object. And so you should have received the same error for both those lines. Therefore, create a new instance of Object1 using the keyword New.

Public Sub DoSomething()

    Dim AnObject As Object1
    Set AnObject = New Object1
    
    Set AnObject.myListRow = aTable.ListRows(1)

    Set AnObject.Row = aTable.ListRows(1)
                                        
End Sub