excel - 在用户窗体中使用类模块变量(需要错误 '424' 对象)

Question

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

我尝试应用this very lenghty guide中的方法，但无济于事。 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

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

Error '424' object required

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

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

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

Answer 1

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

示例:

将您的类命名为 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)然后，您的类对象 oWB 的 Target As Range)，它显示也可以访问 oWB.Workbook.Name 和 oWB.cell 的用户表单.地址.

<小时/>

由于讨论了 clsWorkbook 全局实例的需要，我们可以重构一个完整的示例，并展示 clsWorkbook 如何成为一个私有(private)类成员(member):

将您的类命名为 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

现在，clsWorkbook 在工作簿打开时被实例化，并且是 ThisWorkbook 的私有(private)成员，并且它的工作簿成员是另外打开的工作簿。其中 SheetChange 由 clsWorkbook oWB 实例监听。

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