×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

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

# 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,

Just traded in my OLD subtlety...
for a NUance!

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

#### 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,

Just traded in my OLD subtlety...
for a NUance!

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

---- Andy

There is a great need for a sarcasm font.

### RE: UDF - IsNumber

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

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

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'

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

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

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

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!