excel - 使用On Error Goto尝试查找为什么我遇到错误

Question

运行代码时出现类型不匹配的情况。有没有一种方法可以使用On Error Goto来帮助我调试它？这是我的代码。

Private Sub FH_CNC_HideOrders_Click()
    On Error GoTo errHandler

    If Me.FH_CNC_HideOrders.Caption = "Hide" Then
        'Intiansiate objects and setup variables
        Dim tbl As ListObject
        Dim c As Range
        Dim colStartDate As Range
        Dim FoundDate As Date
        'Set object/variable values
        Set tbl = ActiveWorkbook.Worksheets("Production Tracking").ListObjects("Table293")

        With tbl
            'Search "Start Date" (Col2), top to bottom, searching for the first cell with a color index of 15 and the "End Date" (Col3) which has an index color of anything other than 15
            Set colStartDate = .ListColumns("CNC Begins").DataBodyRange

            For Each c In colStartDate.Cells
                'MsgBox "c.Value:" & c.Value & "   |   c.Interior.ColorIndex:" & c.Interior.ColorIndex & "   |   c.Address:" & c.Address _
                    & Chr(10) & Chr(10) & "c.Offset.Value):" & c.Offset(0, 1).Value & "   |   c.Interior.ColorIndex:" & c.Offset(0, 1).Interior.ColorIndex & "   |   c.Address:" & c.Offset(0, 1).Address

                If c.Interior.ColorIndex = 15 And c.Offset(0, 1).Interior.ColorIndex <> 15 Then
                    FoundDate = c.Value
                    Exit For
                End If
            Next c

            For Each c In colStartDate.Cells

                If Not c.EntireRow.Hidden = True Then
errHandler:
                Msbox c.Value
                Exit Sub
                    'Hide dates prior to colStartDate but not empty cells
                    If Not IsEmpty(c.Value) Then
                        If Not c.Value >= FoundDate And IsDate(c.Value) Then


                            c.EntireRow.Hidden = True
                            'MsgBox c.Address
                        End If
                    End If
                End If
            Next c

        End With

        Me.FH_CNC_HideOrders.Caption = "Show"
    ElseIf Me.FH_CNC_HideOrders.Caption = "Show" Then
        Me.FH_CNC_HideOrders.Caption = "Hide"
    End If
End Sub

我在代码中添加了一个注释，如果发生错误，我想对该值进行 MsgBox。

Answer 1

您“可以”，但我真的不明白您为什么“应该”。

如果由于c包含非日期而导致不匹配错误，为什么不测试c并找出原因呢？

If IsDate(c.Value) Then ...

或者，也许不是测试日期，而是查找日期是多少天？

Select Case VarType(c.Value)
    Case 2 to 6
        MsgBox "These are not dates"
         Exit Sub
    Case 7
        c.EntireRow.Hidden = True
     Case Else
         ....

或者，如果您不想打扰那些VBA常量...

If TypeName(c.Value) = "String" Then MsgBox "This is not a date"