Excel VBA - 运行时错误 13 类型不匹配

Excel VBA - Run Time Error 13 Type Mismatch

提问by Haxed

I'm creating a task list style excel sheet in which people are assigned tasks and can use the sheet as a progress tracker.

我正在创建一个任务列表样式的 Excel 工作表，其中为人们分配了任务，并且可以将工作表用作进度跟踪器。

Once a task is completed, a person marks it off as completed and the sheet pops up with a message box asking whether a change reference number is needed.

任务完成后，有人将其标记为已完成，并且会弹出带有消息框的表单，询问是否需要更改参考编号。

However, when I filter by say, the person who completed the task, and try to add a new row for a new task I get the Run Time Error 13 Type Mismatch. I think it's because my VBA is constantly checking for the status of the task to be completed, so when you create a blank row, there's nothing in the status column.

但是，当我按完成任务的人进行过滤并尝试为新任务添加新行时，我收到运行时错误 13 类型不匹配。我想是因为我的 VBA 一直在检查要完成的任务的状态，所以当你创建一个空白行时，状态列中没有任何内容。

Here's the below code that's highlighted with the error message.

以下是错误消息突出显示的以下代码。

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim answer
    If Not Intersect(Target, Range("H5:H1000")) Is Nothing Then
        If (Target.Value) = "Completed" Then
            answer = MsgBox("Do you need to add a change reference?", vbYesNo + vbQuestion, "Change      Reference Reminder")
            If answer = vbYes Then
                MsgBox "Add in the column to the right", , ("Change Reference Tip")
            Else
                'do nothing'
            End If
        End If
    End If
End Sub

回答by

There are a few things going on here; too many to describe in a comment.

这里发生了一些事情；太多了，无法在评论中描述。

1. First of all, the Worksheet_Changesheet macro is triggered by an event, specifically when one or more cells' contents change. If you want to avoid heavy processing when you insert a row (subsequently changing the values of many cells), you need it to kick out if Targetis more than a single cell. Typically, including If Target.cells.count > 1 then exit subas the first line is sufficient to accomplish this. This is especially important as you want to compare a single changed cell's value to Completed.
2. Your code as supplied doesn't actually change anything but it looks like at some point it will be intended to. In order that changing a cell's value does not launch a second iteration of the same Worksheet_Changeevent maco (subsequently trying to run on top of itself) you would halt event handling temporarily and restart it just before leaving the macro.
3. If you turn off events, you need error handling so that they are turned back on in case of an error. Errors can occur to the best written code procedure and you want to catch and report the error to somewhere like the Immediate windowthen turn the event handling back on before safely exiting the event macro.
4. Minor point but I never Dima variable until my boolean conditions dictate that I am actually going to use it.

1. 首先，工作Worksheet_Change表宏是由事件触发的，特别是当一个或多个单元格的内容更改时。如果您想在插入一行时避免繁重的处理（随后更改许多单元格的值），则需要将其踢出如果Target不止一个单元格。通常，包括If Target.cells.count > 1 then exit sub作为第一行就足以完成此操作。这一点尤其重要，因为您想将单个更改的单元格的值与Completed进行比较。
2. 您提供的代码实际上并没有改变任何东西，但在某些时候看起来它是打算改变的。为了更改单元格的值不会启动同一Worksheet_Change事件宏的第二次迭代（随后尝试在其自身之上运行），您将暂时停止事件处理并在离开宏之前重新启动它。
3. 如果关闭事件，则需要错误处理，以便在发生错误时重新打开它们。最好的编写代码过程可能会发生错误，您希望捕获错误并将其报告给诸如立即窗口之类的地方，然后在安全退出事件宏之前重新打开事件处理。
4. 次要问题，但Dim在我的布尔条件指示我实际上要使用它之前，我从不使用变量。

With these 3? things in mind, the sample code provided might be better as,

用这3个？请记住，提供的示例代码可能会更好，

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("H5:H1000")) Is Nothing Then
        On Error GoTo Erreur
        Application.EnableEvents = False
        If LCase(Target.Value) = "completed" Then
            Dim answer As Variant
            answer = MsgBox("Do you need to add a change reference?", vbYesNo + vbQuestion, "Change      Reference Reminder")
            If answer = vbYes Then
                MsgBox "Change references are added in the column to the right." _
                  & Chr(10) & Chr(10) & "Let's go there now.", vbOKOnly, "Change Re ference Tip"
                Target.Offset(0, 1).Activate
            Else
                'do nothing'
            End If
        End If
    End If
    GoTo Fìn
Erreur:
    Debug.Print Err
Fìn:
    Application.EnableEvents = True
End Sub

I've added one line of code that moved the user to the cell one column to the right of the cell that triggered the event.

我添加了一行代码，将用户移动到触发事件的单元格右侧一列的单元格。