excel - Excel VBA On Error GoTo无法按预期工作

我正在使用On Error GoTo函数Linterp捕获错误，该函数接受一个单元格和两个范围。有时，Linterp会引发错误，在这种情况下，我只想将单元格设置为5。但是，其他时候，当Linterp不会引发错误并正确返回期望的数字时，例如。 3.25之类的功能，该函数仍将转到“案例1”，并将单元格设置回5。
此外，尽管this suggestion我在On Error Goto之后插入了一个Exit Sub，但我认为我无法在特定情况下执行此操作，因为我希望函数即使在其中一个单元格正确执行Linterp的情况下仍能遍历每个单元格第一次尝试。

Sub Linterp_Test_1()
    For Each cell In Selection
        Set cell_index = Cells(3, "I")
        Set cell_xs = Range(Cells(cell.Row, "K"), Cells(cell.Row, "O"))
        Set cell_ys = Range(Cells(cell.Row, "D"), Cells(cell.Row, "H"))

        On Error GoTo Case1
        cell.Value = Linterp(cell_index, cell_xs, cell_ys)
        Resume Next

Case1:
        cell.Value = 5
        Resume Next

    Next cell
End Sub

最佳答案

错误处理
对于您的情况，我更喜欢第一个解决方案。

Option Explicit

Sub Linterp_Test_1()

    If TypeName(Selection) <> "Range" Then Exit Sub

    On Error Resume Next

    For Each cell In Selection

        Set cell_index = Cells(3, "I")
        Set cell_xs = Range(Cells(cell.Row, "K"), Cells(cell.Row, "O"))
        Set cell_ys = Range(Cells(cell.Row, "D"), Cells(cell.Row, "H"))

        cell.Value = Linterp(cell_index, cell_xs, cell_ys)
        If Err.Number <> 0 Then
            cell.Value = 5
            Err.Clear
        End If

    Next cell

    On Error GoTo 0

End Sub


Sub Linterp_Test_2()

    If TypeName(Selection) <> "Range" Then Exit Sub

    For Each cell In Selection

        Set cell_index = Cells(3, "I")
        Set cell_xs = Range(Cells(cell.Row, "K"), Cells(cell.Row, "O"))
        Set cell_ys = Range(Cells(cell.Row, "D"), Cells(cell.Row, "H"))

        On Error Resume Next
        cell.Value = Linterp(cell_index, cell_xs, cell_ys)
        If Err.Number <> 0 Then cell.Value = 5
        On Error GoTo 0

    Next cell

End Sub