UDF - IsNumber 1

[Andrzejek]

I am trying to write my own Function to check if whatever I have is a number.
This is what I have:

Public Function IsNumber(ByRef Value As Variant) As Boolean
Dim bln As Boolean
Dim X As Integer

bln = True

If Len(Trim(Value)) = 0 Then
    bln = False
Else
    If IsNumeric(Value) Then
        For X = 1 To Len(Value)
            If (Mid(Value, X, 1) < 0 Or Mid(Value, X, 1) > 9) _
            And Mid(Value, X, 1) <> "." Then
                bln = False
                Exit For
            End If
        Next X
    Else
        bln = False
    End If
End If

IsNumber = bln

End Function

It is supposed to be a replacement for IsNumeric since it allows expressions such as 2e3 or 4d3 to be evaluated as True, but - in my world - it should be False.

Should I just make it simple and after IsNumeric returns True, just check for the 2 letters (E and D) and be done with it? Would it be then 'full-proof'?


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Andy,

I’m puzzled. Are you trying to convert a string to a number?

Used to work for a manufacturing outfit that has Work Center designations that had values such as 171E2. When this value imported into a worksheet, Excel would convert 171E2 to 17100. Faq68-7375

But under “normal” circumstances, a NUMBER displayed as Scientific Notation has no D or E in it. The underlying value is a pure number.

???

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

No, I am not "trying to convert a string to a number", more like 'validate' what is a number and what is not.
I have another program, by somebody else, where a part of a project number is used as an index of an array. Something to that effect:

strProjNo = "BG[blue]123[/blue]45-KLM"
If Isnumeric(Mid(strProjNo, 3, 3)) Then
    strMyIndex = Mid(strProjNo, 3, 3)
Else
    strMyIndex = "0"
End If
SomeAray(strMyIndex) = whatever

Which works 97% of times, but then [tt]strProjNo = "BG[blue]1E3[/blue]45-KLM"[/tt] and - crash.

I know it is a mess, index should not be a string, stc. But I don't want to spend days of fixing it (long story...), just prevent it from crashing.



---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

But there you did it. MID() returns a String. So you ARE converting a String of digits to a Number.

So how about

If IsNumeric(Evaluate(Mid(strProjNo, 3, 3))) Then
   ‘Got a number
   strMyIndex = Mid(strProjNo, 3, 3)
Else
   ‘No num
   strMyIndex = “0”
End If

Evaluate() will convert sci note string to number.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[strongm]

>Evaluate() will convert sci note string to number

And so does IsNumeric by itself ... unfortunately your code exhibits the same problem that Andrzejek is trying to avoid - it validates "2e3" as a number ...

Something liike the following might work for this specific requirement :

[blue]Public Function vbaIsValidIndexNumber(myNum As Variant) As Boolean
    vbaIsValidIndexNumber = (myNum = Format(myNum, String(Len(myNum), "0"))) And IsNumeric(myNum)
End Function[/blue]

 

[combo]

If you need to test fixed position as in your example, then:

If Mid(strProjNo, 3, 3) Like "###" Then

combo

 

[Andrzejek]

combo, that's a very good idea (for this particular scenario), but I would like to have something more generic so I can use it all over, like strongm's code (need to find some time to test it, not that I question his code... )


---- Andy

There is a great need for a sarcasm font.

 

[mintjulep]

How about

Public Function IsNumber(ByRef Value As Variant) As Boolean
Dim X As Integer

IsNumber = True

If Len(Trim(Value)) = 0 Then
    IsNumber = False
Else
    If IsNumeric(Value) Then
        For X = 1 To Len(Value)
            IsNumber = IsNumeric((Mid(Value, X, 1))
            if NOT IsNumber then Exit For
        Next X
    End If
End If

End Function

 

[mintjulep]

This seems to work.

Public Function IsitaNumber(ByRef Value As Range) As Boolean
Dim X As Integer
Dim DotCount As Integer
Dim DotFound As Boolean

DotFound = False
DotCount = 0

IsitaNumber = True

If Len(Trim(Value.Text)) = 0 Then
    IsitaNumber = False
Else
    For X = 1 To Len(Value.Text)
        DotFound = Mid(Value.Text, X, 1) = "."
        IsitaNumber = DotFound Or IsNumeric(Mid(Value.Text, X, 1))
        If DotFound Then DotCount = DotCount + 1
            If DotCount > 1 Then
                IsitaNumber = False
                Exit For
            End If
        DotFound = False

        If Not IsitaNumber Then Exit For
    Next X
End If

End Function

 

[Andrzejek]

Well mintjulep,

My line of code:[tt]
If IsNumeric(Value) Then[/tt]
already takes care of multiple dots in the expression...

Unless there is a possibility of something that I did not expect or test for...?

Also, strongm
I did try your suggestion and did:
[tt]
Debug.Print vbaIsValidIndexNumber([blue]"0.5"[/blue])[/tt]

Came up - False :-(

I get - indexes should not be 0.5, but still, I am trying to modify IsNumeric to do more exact 'stuff' for my use.

---- Andy

There is a great need for a sarcasm font.

 

[mintjulep]

@Andrzejek,

Good point.

Anyway, working with value.text instead of value exposes the "e" in the scientific notation case.

 

[Deniall]

The objectives here are a bit vague, particularly if we take the exercise beyond the needs you originally enunciated.[ ]
»[ ] Do you want the resulting function to handle negative numbers?
»[ ] Do you want it for use only in the Excel environment, or the VBA environment as well?
»[ ] A cell entry of 1D2 will be recognised as numeric in VBA but not in Excel.
»[ ] Calling your VBA UDF "IsNumber" is a bad idea, given that IsNumber is an inbuilt Excel function.

(You have opened quite a kettle of worms here.)

 

[strongm]

Absolutely, because my quick line of code was to solve for the scenario you outlined, which didn't involve decimals, which is why it was called vbaIsValidIndexNumber ...

Have an alternative ...

[blue]ublic Function vbaSimpleIsNumber(mynum As Variant) As Boolean
    If IsNumeric(mynum) Then
        With CreateObject("vbscript.regexp")
            .Pattern = "\-?\d*\.?\d*" ' Only works in locales where . is decimal separator
            vbaSimpleIsNumber = (mynum = .Execute(mynum).Item(0))
        End With
    End If
End Function[/blue]

 

[Andrzejek]

(You have opened quite a kettle of worms here.)

Sorry about it. I was afraid that will happen.
What I am after is a simple function that accepts the string and returns True if it is a number (positive, negative, decimal, etc.) or False if it is not.
Turns out, IsNumeric works most of the time, except I need to check if the text contains E, e, D, or d - then it is NOT a number (in my book/function), pretty much 'ignore scientific notation'

And yes, IsNumber may not be the best name for it since Excel uses it already.


---- Andy

There is a great need for a sarcasm font.

 

[strongm]

>text contains E, e, D, or d - then it is NOT a number

What if it is hexadecimal?

 

[Andrzejek]

OK, another way to put it:
If 'text' has digits 0-9, (optional) minus in front, (optional) one decimal point (period) - True
Anything else - False


---- Andy

There is a great need for a sarcasm font.

 

[Deniall]

It is not directly applicable to this thread, but many years ago I needed to explore exactly how Excel's and VBA's various ISxxx() functions respond to different sorts of cell contents.[ ] I attach the resulting spreadsheet (which has been slightly expanded in the light of this conversation).[ ] Note that it uses a UDF to access the VBA functions.

 

[strongm]

>If 'text' has digits 0-9, (optional) minus in front, (optional) one decimal point (period) - True

Which looks very like my regexp solution above ...

 

[Andrzejek]

Thanks strongm


---- Andy

There is a great need for a sarcasm font.