×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

UDF - IsNumber

UDF - IsNumber

UDF - IsNumber

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

CODE

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.

RE: UDF - IsNumber

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: Be Aware: Excel can Change Your Data

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: UDF - IsNumber

(OP)
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:

CODE

strProjNo = "BG12345-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 strProjNo = "BG1E345-KLM" 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.

RE: UDF - IsNumber

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

So how about

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: UDF - IsNumber

>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 :

CODE

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

RE: UDF - IsNumber

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

CODE -->

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

combo

RE: UDF - IsNumber

(OP)
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... smile )


---- Andy

There is a great need for a sarcasm font.

RE: UDF - IsNumber

How about

CODE --> VBA

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 

RE: UDF - IsNumber

This seems to work.

CODE --> VBA

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 

RE: UDF - IsNumber

(OP)
Well mintjulep,

My line of code:
If IsNumeric(Value) Then

already takes care of multiple dots in the expression...wink

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

Also, strongm
I did try your suggestion and did:

Debug.Print vbaIsValidIndexNumber("0.5")


Came up - False sad

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.

RE: UDF - IsNumber

@Andrzejek,

Good point.

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

RE: UDF - IsNumber

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.)

RE: UDF - IsNumber

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 ...

CODE

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 

RE: UDF - IsNumber

(OP)

Quote (Deniall )

(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' smile

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.

RE: UDF - IsNumber

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

What if it is hexadecimal?

RE: UDF - IsNumber

(OP)
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.

RE: UDF - IsNumber

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.

RE: UDF - IsNumber

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

Which looks very like my regexp solution above ... 😀

RE: UDF - IsNumber

(OP)
Thanks strongm thumbsup2


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close