excel - 带有 activex 控件复选框和自定义函数的 Excel 宏显示“#VALUE！” 错误

Question

我只是 excel 和 vb 脚本的新手，并且添加了带有自定义功能的 ActiveX 复选框控件，如果选中复选框，则为行着色，如果未选中，则将其删除。

宏代码：

Public Sub GenerateCheckBox()
    Dim i As Integer
    Dim linkedcell
    linkedcell = "B"
    initialTop = 15
    totalRow = (Cells(Rows.Count, 4).End(xlUp).row - 1)

    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    Dim checkbox As OLEObject

    Range("A:C").Insert
    Range("A1").Value = "Resolved"
    
    For i = 1 To totalRow
        activeRow = i
        Set checkbox = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
            DisplayAsIcon:=False, Left:=0, Top:=initialTop, Width:=13, Height:=15)
        checkbox.linkedcell = linkedcell & activeRow
        checkbox.Object.Caption = ""
        checkbox.Object.BackStyle = 0
        checkbox.Object.Value = False
        initialTop = initialTop + 15

        ws.Range("C" & activeRow).Formula = "=ColorRowIF(B" & activeRow & "=True)"
    Next i
End Sub

Public Function ColorRowIF(Condition As Boolean, Optional r As Integer = 100, Optional g As Integer = 255, Optional b As Integer = 4) As String
    Dim row As Integer
    row = Application.Caller.row

    If Condition = True Then
        ' Paints the checked row with color
        ActiveSheet.Evaluate "ColorRow(" & row & ", " & r & ", " & g & ", " & b & ")"
    Else
        'Removes the color from the unchecked row
        ActiveSheet.Evaluate "RemoveRowColor(" & row & ")"
    End If

    ColorRowIF = Condition
End Function

Sub DeleteAll()
    Dim obj As OLEObject
    For Each obj In ActiveSheet.OLEObjects
        obj.Delete
    Next obj
    Columns("A:C").Delete
End Sub

此代码完美运行，直到行数超过2000 行。在 2000 行之后，C 列返回“#VALUE！” 所有现有单元格的错误，并且不评估结果。

此外，我还添加了一种删除 ActiveX 复选框和前 3 列的方法，这对于添加了复选框和公式的 2000 行也非常有效。

之后VB抛出以下错误：

运行时错误“-2147319765 (8002802b)”：

找不到自动化错误元素

当我单击调试选项时，光标将For Each obj In ActiveSheet.OLEObjects方法显示为有错误。

标签： excelvbaexcel-formula