Bullet-proof test for numeric data

[JenniferMurphy]

A UDF I was working on failed when passed data from a cell that was "empty". To get it to work, I had to change the If statement to this:

If IsNumeric(parm) And Not IsEmpty(Parm) Then . . .

Is this the correct (& optimal) statement?

 

[Rick Rothstein]

In my Excel,

1e23 becomes 1E23 when I type it in & that returns TRUE. A good result.

Maybe... if i use your function on a TextBox with the maximum number of characters set to 4 because I want to receive a 4-digit number from the user, your function would let in 1E23 (because it is 4 characters) which, as an expanded number, is a 1 followed by 23 zeros (just a tad more than 4 digits).

...entering with double quotes as "1e23" returns FALSE. Depends on what is wanted: as it is text being entered, I'd be happy with FALSE

&HEAD returns FALSE for me. And you get TRUE
1,,2,,3 also returns FALSE. And you get TRUE

Something strange

Just tested again... I get True for all of them (including "1e23" in quotes).



By the way, I think the logic on this line of code may be wrong...

IsNumber = Len(Value) And Value = Value * 1

Because Len(Value) is not a Boolean, I think the And operator is And'ing the Len(Value) with the Value variable before testing the equal operator. I believe it should be written this way...

IsNumber = (Len(Value) > 0) And (Value = Value * 1)

with the parentheses added to clarify what is being tested. Performing the above "correction" does not change the results for me, those three values still return True.

 

[Rick Rothstein]

Yikes!!! I had no idea this could be so complicated.

Since this is for my personal use, I am inclined to ignore some of the more exotic cases that I would never use. Of course, there always fat-fingered typos that could look like one of your cases and that would get past my checker. I would rather catch a few valid numbers entered with non-standard (for me) notation than let any errors get through and corrupt the results unbeknownst to me.

These two functions (posted earlier) are bullet-proof... the first will guarantee that an entry is composed only of digits while the second will guarantee that an entry is a valid number composed only of digits and possibly a single decimal point. I also allow for a plus/minus sign in the second one (see instructions in the function if you do not want to allow them)...

Function IsDigitsOnly(Value As String) As Boolean
    IsDigitsOnly = Len(Value) > 0 And Not Value Like "*[!0-9]*"
End Function

[table="width: 500"]
[tr]
	[td]Function IsNumber(ByVal Value As String) As Boolean
    '   Leave the next statement out if you don't
    '   want to provide for plus/minus signs
    If Value Like "[+-]*" Then Value = Mid$(Value, 2)
    IsNumber = Not Value Like "*[!0-9.]*" And Not Value Like "*.*.*" And Len(Value) > 0 And Value <> "."
End Function[/td]
[/tr]
[/table]

 

[Fazza]

hi, Rick

I was rather surprised that we were getting different results using the code (I posted). IsNumber = Len(Value) And Value = Value * 1

I think I understand now. I was using the function only from a worksheet cell. And if I use the code purely within VBA - which I hadn't planned for, btw - I get results as did you.

I'm still curious if, used in a worksheet, my proposal is suitable.

regards

 

[Rick Rothstein]

I think I understand now. I was using the function only from a worksheet cell. And if I use the code purely within VBA - which I hadn't planned for, btw - I get results as did you.

I'm still curious if, used in a worksheet, my proposal is suitable.

You chose a bad name for your function... IsNumber is changed by Excel into ISNUMBER (the built-in Excel function)... your code is not being run (put a breakpoint on the embedded line of code... execution never gets to it). Change your function name to something else and then try the values I suggested (with the cells formatted as TEXT to stop Excel from pre-processing them).

 

[Fazza]

Ah, Rick. What a mess, eh? I simply copied the function name from your thread, BTW.
Thanks very much for the lesson & sorry for wasting your time. regards

 

[Rick Rothstein]

Ah, Rick. What a mess, eh? I simply copied the function name from your thread, BTW.
Thanks very much for the lesson & sorry for wasting your time. regards

Oh my gosh... you are right... the bad function name was mine. I developed that write up and attendant code back when I was a Microsoft MVP for (the compiled version of) Visual Basic (well before I did anything with Excel), so that function name was not a problem. When I became a Microsoft MVP for Excel, it never dawned on me (until just now when you pointed it out) that the name I chose was in conflict with a built-in Excel function name. I have changed my filed write up to change the function's name. Thanks for noting that the name came from my write-up. And don't think for a moment that you wasted my time... you didn't.

 

[Fazza]

You're a champion, Rick

 

[pbornemeier]

Thanks for this post.
Why 1234D05 is seen as scientific notation? Could not find anything in this forum or via google.

 

[Rick Rothstein]

Thanks for this post.
Why 1234D05 is seen as scientific notation? Could not find anything in this forum or via google.

It is a carry over from the days of the original BASIC language which, in turn, got it from the FORTRAN language way back before Visual Basic was ever thought of. In FORTRAN, the D had something to do with double precision powers of 10 whereas the E was, I guess, single precision. That distinction was lost when BASIC inherited it, but both letters were retained. Excel formulas only recognize the E notation.

 

[pbornemeier]

Thanks for the information.