excel - 在函数中使用 IsNumeric

Question

我编写了一个函数，它根据输入单元格生成一个“分数”(1,0,-1)，该单元格应该包含一个数字。但是，有时输入字段可能不是数字，然后该函数应返回输出“0”。

Function ScoreRoE(RoE_Field As Range, goodval As Range, badval As Range)

    Dim RoE As Double, result As Double

    RoE = RoE_Field.Value

        If IsNumeric(RoE_Field.Value) = False Then
            result = "0"
        Else:
            If RoE >= goodval.Value Then
                result = "1"
            ElseIf RoE <= badval.Value Then
                result = "-1"
            Else:
                result = "0"
            End If
        End If

    ScoreRoE = result

End Function

当输入单元格为数字时，该函数可以正常工作。但是，如果不是，则不会，只会返回错误“#VALUE!”

提前非常感谢!

Answer 1

将 RoE 声明为 Variant:

Function ScoreRoE(RoE_Field As Range, goodval As Range, badval As Range)

    Dim RoE As Variant, result As Double

    RoE = RoE_Field.Value

        If Not IsNumeric(RoE) Then
            result = 0
        Else
            If RoE >= goodval.Value Then
                result = 1
            ElseIf RoE <= badval.Value Then
                result = -1
            Else
                result = 0
            End If
        End If

    ScoreRoE = result

End Function

您不能将文本值分配给 double 值。

我个人认为不需要任何变量:

Function ScoreRoE(RoE_Field As Range, goodval As Range, badval As Range)

    If Not IsNumeric(RoE_Field.Value) Then
        ScoreRoE = 0
    Else
        If RoE_Field.Value >= goodval.Value Then
            ScoreRoE = 1
        ElseIf RoE_Field.Value <= badval.Value Then
            ScoreRoE = -1
        Else
            ScoreRoE = 0
        End If
    End If

End Function