CODEWITHSUNDEEP
excelvbaexcel-formulaisnumeric
yannk
yannk

Reputation: 51

Using IsNumeric in a function

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!

Upvotes: 1

Views: 120

Answers (2)

Vityata
Vityata

Reputation: 43595

This is an interpretation of the intentions of the code:

Function ScoreRoe(roeInput As String, goodVal As String, badVal As String) As String

    If Not IsNumeric(roeInput) Or Not IsNumeric(goodVal) Or Not IsNumeric(badVal) Then
        ScoreRoe = "0"
    Else
        Dim goodValNumeric As Double: goodValNumeric = goodVal
        Dim badValNumeric As Double: badValNumeric = badVal
        Dim roeInputNumeric As Double: roeInputNumeric = roeInput

        If roeInputNumeric >= goodValNumeric Then
            ScoreRoe = "1"
        ElseIf roeInputNumeric <= badValNumeric Then
            ScoreRoe = "-1"
        Else
            ScoreRoe = "0"
        End If
    End If

End Function

The main idea is that the input is taken as String and then converted to Double for the comparison. Then if the input is not numeric, the returned value is 0 and if it is we go through If-Else.

In general, it could be a good idea to avoid : in VBA If-Else, in order to avoid unwanted problems like this one - VBA - How the colon `:` works in VBA code with condition

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152660

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

Upvotes: 4

Related Questions