在用户窗体中使用类模块变量（需要错误“424”对象）

我有一个类模块，其中托管有一个 worksheet_change 子项，并且在该子项中必须弹出一个用户表单。我想在用户窗体的代码中使用类模块中的许多变量。然而，无论我做什么，我都无法让它发挥作用。

我尝试应用该方法这个非常长的指南 https://excelmacromastery.com/vba-class-modules/#Class_Module_Properties，但无济于事。 SO 上的其他线程无法帮助我。

Private cell As Range

Public WithEvents m_wb As Workbook

Property Get cellr() As Range
    Set cellr = cell
End Property

Property Set cellr(cellrange As Range)
    Set cell = cellrange
End Property

Public Property Set Workbook(wb As Workbook)
    Set m_wb = wb
End Property

Public Property Get Workbook() As Workbook
    Set Workbook = m_wb
End Property

Public Sub m_wb_SheetChange(ByVal Sh As Object, ByVal Target As Range) 'simplified, but accurate
    Application.EnableEvents = False

    For each cell in Target
        ReplaceTask.Show
    Next cell

    Application.EnableEvents = True
End Sub

In the userform_initialize宏，我需要能够获取该宏的名称m_wb工作簿以及单元格（最好是range变量，否则只是地址）For each cell in Target环形。对于下面代码中的每个变量我得到

需要错误“424”对象

这表明变量不是公开的..

Private Sub UserForm_Initialize()
Debug.Print cellrange.Address
Debug.Print cell.Address
Debug.Print cellr.Address
Debug.Print m_wb.Name
'....

我确信我无法理解这些属性是如何工作的，这阻碍了我。如果有人能指出我做错了什么，拜托！

为了完成这项工作，它至少需要一个类类型的公共对象变量。并且该对象变量必须设置为类的新实例。那么这个对象变量，并且只有这个对象变量，才是类的公共可访问实例。

Example:

让你的班级被命名clsWorkbook并具有以下代码：

Option Explicit

Private m_cell As Range

Private WithEvents m_wb As Workbook

Property Let cell(cellrange As Range)
    Set m_cell = cellrange
End Property

Property Get cell() As Range
    Set cell = m_cell
End Property

Public Property Let Workbook(wb As Workbook)
    Set m_wb = wb
End Property

Public Property Get Workbook() As Workbook
    Set Workbook = m_wb
End Property

Private Sub m_wb_SheetChange(ByVal Sh As Object, ByVal Target As Range) 'simplified, but accurate
    Application.EnableEvents = False

    For Each m_cell In Target
        ReplaceTask.Show
    Next m_cell

    Application.EnableEvents = True
End Sub

让您的用户表单命名为ReplaceTask有以下代码：

Option Explicit

Private Sub UserForm_Initialize()
 Debug.Print oWB.Workbook.Name
 Debug.Print oWB.cell.Address
End Sub

在默认模块中有以下代码：

Option Explicit

Public oWB As clsWorkbook

Public Sub test()
 Set oWB = New clsWorkbook
 oWB.Workbook = ThisWorkbook
End Sub

现在，之后Sub test()运行后，请更改代码所在工作簿中的工作表中的某些内容。这应该会触发Sub m_wb_SheetChange(ByVal Sh As Object, ByVal Target As Range)你的类对象oWB然后，它显示了也可以访问的用户表单oWB.Workbook.Name and oWB.cell.Address.

因为关于需要一个全局实例的讨论clsWorkbook让我们有一个完整的例子，可以重建它并展示如何clsWorkbook可以是私有类成员：

让你的班级被命名clsWorkbook并具有以下代码：

Option Explicit

Private m_cell As Range

Private WithEvents m_wb As Workbook

Property Let Cell(cellrange As Range)
    Set m_cell = cellrange
End Property

Property Get Cell() As Range
    Set Cell = m_cell
End Property

Property Let Workbook(wb As Workbook)
    Set m_wb = wb
End Property

Property Get Workbook() As Workbook
    Set Workbook = m_wb
End Property


Private Sub m_wb_SheetChange(ByVal Sh As Object, ByVal Target As Range) 'simplified, but accurate
    Application.EnableEvents = False

    Dim frm As ReplaceTask
    For Each m_cell In Target
        Set frm = New ReplaceTask
        frm.Init Me
        frm.Show
    Next m_cell

    Application.EnableEvents = True
End Sub

让您的用户表单命名为ReplaceTask有以下代码：

Option Explicit

Private m_ParentClass As clsWorkbook

Friend Sub Init(ByVal p As clsWorkbook)
    Set m_ParentClass = p
    Me.Caption = p.Workbook.Name & " : " & p.Cell.Address
End Sub

并在默认类模块中ThisWorkbook有以下代码：

Option Explicit

Private oWB As clsWorkbook

Private Sub Workbook_Open()
    Set oWB = New clsWorkbook
    oWB.Workbook = Workbooks.Open("P:/Mappe1.xlsx")
End Sub

Now the clsWorkbook在工作簿打开时被实例化，并且是ThisWorkbook它的工作簿成员是额外打开的工作簿。那里有SheetChange被聆听者clsWorkbook oWB实例。

并且因为ReplaceTask用户表单被实例化clsWorkbook并且被赋予类实例作为参数，该用户表单也知道类成员。