VBA：传递Range对象到公共变量时出现对象未设置错误

Let's break down why this error is happening and how to fix it.

The Root Cause #

When you call eventPicker.showEventPickerForm Target, VBA automatically loads the default instance of your eventPicker form before it runs any code inside the showEventPickerForm subroutine. That means your UserForm_Initialize event fires immediately—before you get to the line Set eventTarget = targetCell. At that point, eventTarget is still Nothing, so trying to access eventTarget.Address throws the error you're seeing.

Solution 1: Use a Custom Initialization Method (Cleaner Approach) #

Instead of relying on UserForm_Initialize for code that depends on your target range, create a public method to set the target and run your initialization logic after the variable is assigned. This avoids issues with the default form instance.

Step 1: Update Sheet1 Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' No need to wrap Target in Range(Target.Address) — use Target directly
    If Not Application.Intersect(Range("C8:C65000"), Target) Is Nothing Then
        Dim frm As eventPicker
        ' Create a new instance of the form
        Set frm = New eventPicker
        ' Set the target and run initialization
        frm.SetEventTarget Target
        frm.Show
    End If
End Sub

Step 2: Update eventPicker Form Code

' Use a private variable instead of public to avoid unintended access
Private m_eventTarget As Range

' Public method to set the target and run initialization
Public Sub SetEventTarget(ByVal targetCell As Range)
    Set m_eventTarget = targetCell
    ' Put your initialization code here (replace the MsgBox with your logic)
    MsgBox m_eventTarget.Address
End Sub

Private Sub UserForm_Initialize()
    ' Leave this empty, or add code that doesn't depend on m_eventTarget
End Sub

Solution 2: Move Code to UserForm_Activate (Quick Fix) #

If you want to keep using the default form instance, move the code that uses eventTarget from UserForm_Initialize to UserForm_Activate. The Activate event runs after the form is shown, so eventTarget will already be set by then.

Update eventPicker Form Code

Public eventTarget As Range

Sub showEventPickerForm(ByVal targetCell As Range)
    Set eventTarget = targetCell
    ' Use Me to refer to the current form instance instead of the default one
    Me.Show
End Sub

Private Sub UserForm_Activate()
    ' This runs after eventTarget is assigned
    MsgBox eventTarget.Address
End Sub

Private Sub UserForm_Initialize()
    ' Remove the MsgBox from here
End Sub

Additional Tips #

• Avoid using the default form instance (like eventPicker.show) unless you specifically need it—creating new instances (Set frm = New eventPicker) prevents leftover values from previous uses of the form.
• Using private variables with public setter methods (like Solution 1) makes your code more maintainable and reduces the risk of accidental overwrites.
• Always check if an object variable is Nothing before accessing its properties/methods (e.g., If Not m_eventTarget Is Nothing Then MsgBox m_eventTarget.Address) to avoid similar errors in the future.

内容的提问来源于stack exchange，提问作者Nasica