在用户窗体中使用类模块变量(需要错误"424"对象) [英] Using a class module variable in a userform (Error '424' object required)

问题描述

我有一个包含worksheet_change子类的类模块，在该子模块中必须弹出一个用户窗体.我想在Userform的代码中使用来自类模块的许多变量.但是，无论我做什么，我都无法正常工作.

I have a class module which hosts a worksheet_change sub, and in that sub a Userform has to pop up. I want to use a number of variables from the class module in the Userform's code. Whatever I do, however, I can't get it to work.

我试图从此篇幅很长的指南中应用该方法，但是无济于事.SO上的其他线程无法帮助我.

I have tried to apply the method from this very lenghty guide, but to no avail. Other threads on SO weren't able to help me.

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 变量，否则只是地址)，位于 Target的每个单元格循环中.对于下面代码中的每个变量，我得到

In the userform_initialize macro, I need to be able to get the name of the m_wb workbook, as well as the cell (preferably as a range variable, otherwise just the address) in the For each cell in Target loop. For each variable in the code below I get

必须提供错误的"424"对象

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
'....

我很肯定，我无法理解这些属性的工作原理，这使我感到沮丧.如果有人可以对我做错的事情有所了解，请！

I am positive it's my inability to understand how these properties work that's holding me back.. If someone could shine some light on what I am doing wrong, please!

推荐答案

用户窗体和带有事件处理程序的类是两个不同的作用域.您不能期望能够在不限定该范围的情况下引用其他范围的成员. UserForm_Initialize 中的代码将 cellrange 和 cellr 解释为在用户表单本身中声明的局部变量.您没有在用户表单中声明这样的变量，并且您没有使用 Option Explicit ，因此，当代码隐式地假定其为 Dim cellrange As Variant 从未初始化，因此为 Empty .

The user form and the class with the event handler are two different scopes. You cannot expect to be able to refer to members of a different scope without qualifying that scope. The code in your UserForm_Initialize interprets cellrange and cellr as local variables declared in the user form itself. You don't have such variables declared in the user form, and you are not using Option Explicit, so instead of a compile time error you are getting a runtime error 424 when the code implicitly assumes it's Dim cellrange As Variant which was never initialized and is therefore Empty.

要解决此问题，您需要告诉用户实例应从中获取事件处理类的哪个实例的属性.为此，将其放在UserForm中就足够了:

To fix the problem, you need to tell the instance of the user for which instance of the event-handling class it should get the properties from. For that it would be enough to put this in the UserForm:

Private m_ParentClass As ThatClassThatCreatesForms

Friend Sub Init(ByVal p As ThatClassThatCreatesForms)
  Set m_ParentClass = p
End Sub

，然后将父类中的 For Each 循环更改为:

and change the For Each loop in the parent class as:

For each cell in Target
    ReplaceTask.Init Me
    ReplaceTask.Show
Next cell

您必须有一个单独的"Init"方法，因为VBA类不能具有带参数的构造函数.

You have to have a separate "Init" method because VBA classes cannot have constructors with parameters.

然后， ReplaceTask 中的代码可以使用 m_ParentClass.cell ， m_ParentClass.Workbook 等.但是您不能从 UserForm_Initialize中执行此操作，因为尚未调用 Init .但这不是问题，只需将代码从 UserForm_Initialize 移到 Init .

Then the code in ReplaceTask can use m_ParentClass.cell, m_ParentClass.Workbook etc. But you cannot do that from UserForm_Initialize because Init has not been called yet. It is not a problem though, simply move the code from UserForm_Initialize into Init.

要更进一步，我建议您不要使用隐式表单实例.手动创建实例是一个好习惯:

To take it one step further, I would advice that you stop are using the implicit form instance. It is a good practice to create the instances manually:

For each cell in Target
    Dim f As ReplaceTask
    Set f = New ReplaceTask
    f.Init Me
    f.Show
Next cell

这篇关于在用户窗体中使用类模块变量(需要错误"424"对象)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！