VBA带动态控件与自定义事件的UserForm卸载时触发运行时错误91

应用功能介绍 #

• 开发了一款用于银行账户交易对账的Excel VBA应用，可基于存储全部财务交易的工作簿完成账户余额核对
• 交互流程：
  • 初始窗体用于选择目标账户，获取手续费、已付利息与已收利息参数
  • 选中账户后启动第二个窗体，动态展示所选账户的未对账交易
• 动态实现逻辑：
  • 窗体包含框架容器，可为每笔待处理交易动态添加控件
  • 动态创建标签展示交易日期、收付款方、交易金额等信息
  • 每行动态添加复选框，勾选即可标记交易为已对账
  • 专门编写事件处理程序管理复选框的点击逻辑
• 正常场景：所有存在待对账交易的账户，整套逻辑都能正常运行

异常触发场景 #

• 触发条件：未查询到任何未对账交易时，程序弹出提示消息框并卸载当前窗体
• 报错信息：Runtime error 91——Object variable or With block variable not set
• 异常时机：Private Sub UserForm_QueryClose 执行完成、窗体完全卸载之后
• 初步推断：这是首次使用动态创建控件与自定义事件处理程序开发功能，猜测问题根源与此相关，观察发现只有程序执行完动态控件创建的循环逻辑，才不会触发该错误

相关代码片段 #

事件处理Class模块代码 #

Option Explicit
Public WithEvents CheckBoxEvents As MSForms.CheckBox

Private Sub CheckBoxEvents_Click()
Dim Index As Long

' Get array index
 Index = frmReconcile.Frame1.ActiveControl.Caption
    If Index = 0 Or Index > ReconIndex Then
            MsgBox "CheckBoxEvents Index Out of Bounds"
            GoTo BYE
    End If
    
 If frmReconcile.Frame1.ActiveControl = True Then
            reArray(Index).reCleared = "R"
    Else
         reArray(Index).reCleared = ""
    End If

    ReconUncleared = 0#

' Recalc uncleared balance
    For Index = 1 To ReconIndex
            If reArray(Index).reCleared = "" Then
                    ReconUncleared = ReconUncleared + reArray(Index).reAmount
            End If
    Next Index

    frmReconcile.lblUncleared.Caption = Format(ReconUncleared, "##,###,##0.00")
    ReconCleared = ReconCurrBal - ReconUncleared
    frmReconcile.lblCleared.Caption = Format(ReconCleared, "##,###,##0.00")
    ReconDiff = ReconCleared - ReconStatementBal
    frmReconcile.lblDiff.Caption = Format(ReconDiff, "##,###,##0.00")
BYE:
End Sub

frmReconcile窗体相关代码 #

Dim AddLabel As Control
Dim AddCheckBox As Control
Dim checkArray() As New CheckBoxEvents

Private Sub UserForm_Initialize()
Dim strType As String
Dim TrStartRow As Long
Dim LastRow As Long
Dim RowNum As Long
Dim Index As Long

Me.StartUpPosition = 0
Me.Left = getRegistry("frmReconcile:Left")
Me.Top = getRegistry("frmReconcile:Top")

Me.Height = 303
Me.Width = 563
    
On Error Resume Next
ReconCurrBal = Application.WorksheetFunction.SumIfs(Worksheets("Transactions").Range("Tr_Amount"), Range("Tr_Account"), ReconAccount)
If Err.Number <> 0 Then
    MsgBox ("Account """ & ReconAccount & """ not found.")
    GoTo BYE
End If

lblBalance.Caption = Format(ReconCurrBal, "##,###,###,##0.00")

lblUncleared = Format(0, "##,###,##0.00")
lblCleared = Format(0, "##,###,##0.00")
lblStatementBal = Format(ReconStatementBal, "##,###,#0.00")
lblDiff = Format(0, "##,###,##0.00")

lblSortDateUp.Visible = False
lblSortPayeeUp.Visible = False
lblSortNumUp.Visible = False
lblSortAmountUp.Visible = False
lblSortSplitUp.Visible = False

' Clear filter
On Error Resume Next   'turn off error reporting
ThisWorkbook.Worksheets("Transactions").ShowAllData
On Error GoTo 0   'turn error reporting back on

' Set AutoFilter to get all transactions for Account where Recon equal to "c" or ""
TrStartRow = getRegistry("TrStartRow")
LastRow = GetLastRow("Transactions")
ThisWorkbook.Worksheets("Transactions").Range("$A$" & TrStartRow & ":K" & LastRow).AutoFilter Field:=2, Criteria1:= _
    ReconAccount
ThisWorkbook.Worksheets("Transactions").Range("$A$" & TrStartRow & ":K" & LastRow).AutoFilter Field:=10, Criteria1:="=c", _
    Operator:=xlOr, Criteria2:=""

' Find unreconciled transactions
Erase reArray
ReconUncleared = 0#
ReconIndex = 0
For RowNum = TrStartRow To LastRow
    With Worksheets("Transactions")
         If .Cells(RowNum, 2).Value = ReconAccount And .Cells(RowNum, 10).Value <> "R" Then
            ReconIndex = ReconIndex + 1
            reArray(ReconIndex).reDate = .Cells(RowNum, 1)
            reArray(ReconIndex).rePayee = .Cells(RowNum, 3)
            reArray(ReconIndex).reNum = .Cells(RowNum, 7)
            reArray(ReconIndex).reAmount = .Cells(RowNum, 5)
            reArray(ReconIndex).reSplit = .Cells(RowNum, 6)
            reArray(ReconIndex).reCleared = .Cells(RowNum, 10)
            reArray(ReconIndex).reRow = RowNum
        End If
    End With
Next RowNum

If ReconIndex = 0 Then
    MsgBox "No unreconciled transactions found for account """ & ReconAccount & """"
    GoTo BYE
End If

' Set sort buttons
lblSortDateUp.Visible = False
lblSortPayeeUp.Visible = False
lblSortNumUp.Visible = False
lblSortAmountUp.Visible = False
lblSortSplitUp.Visible = False

' Create controls
ReconUncleared = 0#
ReDim checkArray(ReconIndex)
For RowNum = 1 To ReconIndex
    Set AddLabel = Me.Frame1.Controls.Add("Forms.Label.1", "lblDate" & RowNum)
    With AddLabel
        .Top = 5 + (RowNum * 12 - 12)
        .Left = 6
        .Width = 48
        .BackColor = vbWhite
        .Caption = Format(reArray(RowNum).reDate, "mm/dd/yyyy")
    End With
    Set AddLabel = Me.Frame1.Controls.Add("Forms.Label.1", "lblPayee" & RowNum)
    With AddLabel
        .Top = 5 + (RowNum * 12 - 12)
        .Left = 60
        .Width = 102
        .BackColor = vbWhite
        .Caption = reArray(RowNum).rePayee
    End With
    Set AddLabel = Me.Frame1.Controls.Add("Forms.Label.1", "lblNum" & RowNum)
    With AddLabel
        .Top = 5 + (RowNum * 12 - 12)
        .Left = 168
        .Width = 24
        .BackColor = vbWhite
        .Caption = reArray(RowNum).reNum
    End With
    Set AddLabel = Me.Frame1.Controls.Add("Forms.Label.1", "lblAmount" & RowNum)
    With AddLabel
        .Top = 5 + (RowNum * 12 - 12)
        .Left = 198
        .Width = 60
        .BackColor = vbWhite
        .Caption = Format(reArray(RowNum).reAmount, "##,###,##0.00")
        .TextAlign = 3
    End With
    Set AddLabel = Me.Frame1.Controls.Add("Forms.Label.1", "lblSplit" & RowNum)
    With AddLabel
        .Top = 5 + (RowNum * 12 - 12)
        .Left = 264
        .Width = 60
        .BackColor = vbWhite
        .Caption = Format(reArray(RowNum).reSplit, "##,###,##0.00")
        .TextAlign = 3
    End With
    Set AddCheckBox = Me.Frame1.Controls.Add("Forms.CheckBox.1", "chkCleared" & RowNum)
    With AddCheckBox
        .Top = 5 + (RowNum * 12 - 14)
        .Left = 330
        .Caption = RowNum
        If reArray(RowNum).reCleared <> "" Then
            .Value = True
        End If
    End With

    Set checkArray(RowNum).CheckBoxEvents = AddCheckBox
    
Next RowNum

' Get uncleared balance
For Index = 1 To ReconIndex
    If reArray(Index).reCleared = "" Then
        ReconUncleared = ReconUncleared + reArray(Index).reAmount
    End If
Next Index

' Set labels
lblUncleared.Caption = Format(ReconUncleared, "##,###,##0.00")
ReconCleared = ReconCurrBal - ReconUncleared
lblCleared.Caption = Format(ReconCleared, "##,###,##0.00")
ReconDiff = ReconCleared - ReconStatementBal
lblDiff.Caption = Format(ReconDiff, "##,###,##0.00")

Frame1.ScrollHeight = ReconIndex * 20
Frame1.ScrollTop = 0

BYE:
If ReconIndex = 0 Then Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

If CloseMode = vbFormControlMenu Then
    Cancel = True
    Call cmdCancel_Click
End If

Erase reArray
ReconAccount = ""
ReconIndex = 0
ReconCurrBal = 0#
ReconUncleared = 0#
ReconCleared = 0#
ReconStatementBal = 0#
ReconDiff = 0#

Call saveRegistry("frmReconcile:Left", Me.Left)
Call saveRegistry("frmReconcile:Top", Me.Top)

Call Reset

End Sub

核心疑问 #

使用动态添加控件或自定义事件处理程序时，存在哪些遗漏的配置或逻辑要点，导致上述异常发生？

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