Using IsNumeric in a function

Question

I wrote a function that generates a "score" (1,0,-1) based on a input cell, which should contain a number. However, sometimes the input field might not be numeric and then the function should return the output "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

When the input cell is numeric the function works correctly. However, when not it doesn't and just returns an error "#VALUE!"

Thanks a lot in advance!

Answer 1

Declare RoE as 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

you cannot assign a text value to a double.

---

Personally I do not see the need for any of the variables:

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