excel - 无法为字段为对象的自定义类中的字段赋值

Question

我有 VBA 的自定义类。我创建字段，其中将保留对象工作表的引用:

Private ws As Worksheet

写入属性:

Property Get TargetWorksheet() As Worksheet
    TargetWorksheet = ws
End Property
Property Set TargetWorksheet(ByRef newws As Worksheet)
    ws = newws
End Property

但是当我尝试分配值时

Dim mylist As CustomPropertWS
mylist.TargetWorksheet = Sheet1
or
Set mylist.TargetWorksheet = Sheet1

宏总是抛出异常 “未设置对象变量或 block 变量”。如何使用类似的对象设置字段？谢谢。

Answer 1

对您的类模块进行以下修改:

CustomPropertWS类代码

Private ws As Worksheet

Property Get TargetWorksheet() As Worksheet
    Set TargetWorksheet = ws
End Property

Property Set TargetWorksheet(ByRef newws As Worksheet)
    Set ws = newws
End Property

您的子程序，它调用类代码:

TestClass模块代码

Option Explicit

Sub TestClass()

Dim mylist As CustomPropertWS

Set mylist = New CustomPropertWS ' initiate the new class
'Set mylist.TargetWorksheet = Worksheets("Sheet1")
Set mylist.TargetWorksheet = Sheet1 ' <-- this method passes the worksheet's codename

Debug.Print mylist.TargetWorksheet.Name ' just for DEBUG

End Sub