worksheet as vba class module property - runtime error 9

Question

I have the following vba class module called clsAgent:

Option Explicit

Private pAgentSheetName As String
Private pAgentSheet As Worksheet


Public Property Get AgentSheetName() As String
    AgentSheetName = pAgentSheetName
End Property

Public Property Let AgentSheetName(AgentSheetName As String)
    pAgentSheetName = AgentSheetName
End Property


Public Property Get AgentSheet() As Worksheet
    AgentSheet = pAgentSheet
End Property

Public Property Let AgentSheet(AgentSheet As Worksheet)
    pAgentSheet = AgentSheet
End Property

Here's my class instantiation in a different module:

Sub test_agent_class()
    Dim agent1 As clsAgent
    Set agent1 = New clsAgent

    Set agent1.AgentSheet = Worksheets(agent1.AgentSheetName)
    Debug.Print agent1.AgentSheet.Name
End Sub

When I run test_agent_class(), however, I get an error: run-time error 9 subscript out of range. The following line gets highlighted in yellow:Set agent1.AgentSheet = Worksheets(agent1.AgentSheetName).

I read this answer, but I don't get what the problem is because agent1.AgentSheetName is a string.

What am I doing wrong?

EDIT

Per the first answer I added the line:

agent1.AgentSheetName = "agentsFullOutput.csv"

right after the class in instantiated. Now I get an error:

Run-time error '91': Object variable or With block variable not set. 

Answer 1

Because pAgentSheet is a Worksheet which is an object type, you need to use Set to assign it within the Property Let procedure. It's also probably clearer to use Property Set instead of Property Let like this:

Public Property Set AgentSheet(AgentSheet As Worksheet)
    Set pAgentSheet = AgentSheet
End Property

If you use a Property Let procedure then you do not use Set when calling the procedure (but you still need it within the procedure) so the call would be agent1.AgentSheet = Worksheets(agent1.AgentSheetName)

If you use a Property Set procedure then you must use Set when calling the procedure (and also within the procedure) so the call would be Set agent1.AgentSheet = Worksheets(agent1.AgentSheetName)

This VB6 answer might explain things more clearly

Answer 2

The issue is that you have not assigned a string to agent1.AgentSheetName. So you are trying to set agent1.AgentSheet to the worksheet whose name is the empty string. I doubt that you have such a sheet. You should start by assigning a value to agent1.AgentSheetName.