Using a class module variable in a userform (Error '424' object required)

Question

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.

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

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

Error '424' object required

which shows the variables are not public..

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!

Answer 1

To make this work it needs at least a public object variable of type of your class. And this object variable must be set to be a new instance of your class. This object variable then, and only this object variable, is the public accessable instance of your class.

Example:

Let your class be named clsWorkbook and having following code:

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

Let your UserForm named ReplaceTask having following code:

Option Explicit

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

And in a default Module have following code:

Option Explicit

Public oWB As clsWorkbook

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

Now, after Sub test() was run, do changig something in a worksheet in the workbook the code is in. This should trigger the Sub m_wb_SheetChange(ByVal Sh As Object, ByVal Target As Range) of your class object oWB then, which shows the user form which also can access oWB.Workbook.Name and oWB.cell.Address.

---

Because of the discussion about the need of a global instance of clsWorkbook lets have a complete example which one can reconstruct and which shows how the clsWorkbook can be a private class member:

Let your class be named clsWorkbook and having following code:

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

Let your UserForm named ReplaceTask having following code:

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

And in default class module ThisWorkbook have following code:

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 gets instantiated while workbook open and is a private member of ThisWorkbook and it's workbook member is the workbook which was opened addditional. There the SheetChange are listened by the clsWorkbook oWB instance.

And because the ReplaceTask user form gets instantiated in clsWorkbook and was given the class instance as parameter, this user form knows the class members too.

Answer 2

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.

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

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

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

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

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