Another VBA bug

[Don Wiss]

This is xl2002. VBA says that IsNumeric("3,45") is okay. There is a simple
workaround. WorksheetFunction.IsNumber("3,45") correctly notes that it is
not a number.

Don't you just like the consistency of the function naming here?

Don <www.donwiss.com> (e-mail link at home page bottom).

 

[Jerry W. Lewis]

Hardly a bug, since both functions behave exactly as documented by their
respective Help's. VBA is a subset of VB, which is a completely separate
entity from Excel. It is a mistake to think of them as the same language.

Jerry

 

[Rick Rothstein \(MVP - VB\)]

This is xl2002. VBA says that IsNumeric("3,45") is okay. There is a simple

workaround. WorksheetFunction.IsNumber("3,45") correctly notes that it is
not a number.

The VBA IsNumeric function is actually a lot worse than you have yet
discovered. Here is part of a canned response that I post (originally, over
in the compiled VB newsgroups, but everything in my posting applies to VBA)
whenever someone posts about (or recommends using) the VB/VBA IsNumeric
function. Usually, the person having trouble with this function is trying to
use it to determine if a user's entry is all digits or a "normally" formed
number; hence the theme of the posting.

I usually try and steer people away from using IsNumeric to "proof"
supposedly numeric text. Consider this (also see note below):

ReturnValue = IsNumeric("($1,23,,3.4,,,5,,E67$)")

Most people would not expect THAT to return True. IsNumeric has some "flaws"
in what it considers a proper number and what most programmers are looking
for.

I had a short tip published by Pinnacle Publishing in their Visual Basic
Developer magazine that covered some of these flaws. Originally, the tip was
free to view but is now viewable only by subscribers.. Basically, it said
that IsNumeric returned True for things like -- currency symbols being
located in front or in back of the number as shown in my example (also
applies to plus, minus and blanks too); numbers surrounded by parentheses as
shown in my example (some people use these to mark negative numbers);
numbers containing any number of commas before a decimal point as shown in
my example; numbers in scientific notation (a number followed by an upper or
lower case "D" or "E", followed by a number equal to or less than 305 -- the
maximum power of 10 in VB); and Octal/Hexadecimal numbers (&H for
Hexadecimal, &O or just & in front of the number for Octal).

NOTE:
======
In the above example and in the referenced tip, I refer to $ signs and
commas and dots -- these were meant to refer to your currency, thousands
separator and decimal point symbols as defined in your local settings --
substitute your local regional symbols for these if appropriate.

As for your question about checking numbers, here are two functions that I
have posted in the past for similar questions..... one is for digits only
and the other is for "regular" numbers:

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

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 <> "." And _
Value <> vbNullString
End Function

Here are revisions to the above functions that deal with the local settings
for decimal points (and thousand's separators) that are different than used
in the US (this code works in the US too, of course).

Function IsNumber(ByVal Value As String) As Boolean
Dim DP As String
' Get local setting for decimal point
DP = Format$(0, ".")
' 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" & DP & "]*" And _
Not Value Like "*" & DP & "*" & DP & "*" And _
Len(Value) > 0 And Value <> DP And _
Value <> vbNullString
End Function

I'm not as concerned by the rejection of entries that include one or more
thousand's separators, but we can handle this if we don't insist on the
thousand's separator being located in the correct positions (in other words,
we'll allow the user to include them for their own purposes... we'll just
tolerate their presence).

Function IsNumber(ByVal Value As String) As Boolean
Dim DP As String
Dim TS As String
' Get local setting for decimal point
DP = Format$(0, ".")
' Get local setting for thousand's separator
' and eliminate them. Remove the next two lines
' if you don't want your users being able to
' type in the thousands separator at all.
TS = Mid$(Format$(1000, "#,###"), 2, 1)
Value = Replace$(Value, TS, "")
' 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" & DP & "]*" And _
Not Value Like "*" & DP & "*" & DP & "*" And _
Len(Value) > 0 And Value <> DP And _
Value <> vbNullString
End Function

Rick

 

[Peter T]

Don,
There's a big difference between IsNumeric and IsNumber. Surely the text
"3.45" is indeed numeric.

If you want to test the 'type' of a variable look as VarType.


Rick,

ReturnValue = IsNumeric("($1,23,,3.4,,,5,,E67$)")

Wow !
Actually, as my currency is not USD I did this -

s = "($1,23,,3.4,,,5,,E67$)"
s = Replace(s, "$", Application.International(xlCurrencyCode))

Raised my curiosity -

Dim d as double
on error resume next
d = s ' try to coerce
If err.number then
err.clear
else
blnIsNumeric = true
end if

With the your string, Internationalized if necessary, it can be coerced and
somehow returns -1.23345E+70

However -
v = application.Evaluate(s) ' Error 2015

Which leads me to wonder if the following in help -
"Returns a Boolean value indicating whether an expression can be evaluated
as a number"

might be better written as "- can be COERCED as a number"

Not sure why a Date is not considered as numeric although it can 'sometimes'
if not typically be coerced to a double.

Regards,
Peter T

This is xl2002. VBA says that IsNumeric("3,45") is okay. There is a simple
workaround. WorksheetFunction.IsNumber("3,45") correctly notes that it is
not a number.

The VBA IsNumeric function is actually a lot worse than you have yet
discovered. Here is part of a canned response that I post (originally, over
in the compiled VB newsgroups, but everything in my posting applies to VBA)
whenever someone posts about (or recommends using) the VB/VBA IsNumeric
function. Usually, the person having trouble with this function is trying to
use it to determine if a user's entry is all digits or a "normally" formed
number; hence the theme of the posting.

I usually try and steer people away from using IsNumeric to "proof"
supposedly numeric text. Consider this (also see note below):

ReturnValue = IsNumeric("($1,23,,3.4,,,5,,E67$)")

Most people would not expect THAT to return True. IsNumeric has some "flaws"
in what it considers a proper number and what most programmers are looking
for.

I had a short tip published by Pinnacle Publishing in their Visual Basic
Developer magazine that covered some of these flaws. Originally, the tip was
free to view but is now viewable only by subscribers.. Basically, it said
that IsNumeric returned True for things like -- currency symbols being
located in front or in back of the number as shown in my example (also
applies to plus, minus and blanks too); numbers surrounded by parentheses as
shown in my example (some people use these to mark negative numbers);
numbers containing any number of commas before a decimal point as shown in
my example; numbers in scientific notation (a number followed by an upper or
lower case "D" or "E", followed by a number equal to or less than 305 -- the
maximum power of 10 in VB); and Octal/Hexadecimal numbers (&H for
Hexadecimal, &O or just & in front of the number for Octal).

NOTE:
======
In the above example and in the referenced tip, I refer to $ signs and
commas and dots -- these were meant to refer to your currency, thousands
separator and decimal point symbols as defined in your local settings --
substitute your local regional symbols for these if appropriate.

As for your question about checking numbers, here are two functions that I
have posted in the past for similar questions..... one is for digits only
and the other is for "regular" numbers:

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

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 <> "." And _
Value <> vbNullString
End Function

Here are revisions to the above functions that deal with the local settings
for decimal points (and thousand's separators) that are different than used
in the US (this code works in the US too, of course).

Function IsNumber(ByVal Value As String) As Boolean
Dim DP As String
' Get local setting for decimal point
DP = Format$(0, ".")
' 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" & DP & "]*" And _
Not Value Like "*" & DP & "*" & DP & "*" And _
Len(Value) > 0 And Value <> DP And _
Value <> vbNullString
End Function

I'm not as concerned by the rejection of entries that include one or more
thousand's separators, but we can handle this if we don't insist on the
thousand's separator being located in the correct positions (in other words,
we'll allow the user to include them for their own purposes... we'll just
tolerate their presence).

Function IsNumber(ByVal Value As String) As Boolean
Dim DP As String
Dim TS As String
' Get local setting for decimal point
DP = Format$(0, ".")
' Get local setting for thousand's separator
' and eliminate them. Remove the next two lines
' if you don't want your users being able to
' type in the thousands separator at all.
TS = Mid$(Format$(1000, "#,###"), 2, 1)
Value = Replace$(Value, TS, "")
' 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" & DP & "]*" And _
Not Value Like "*" & DP & "*" & DP & "*" And _
Len(Value) > 0 And Value <> DP And _
Value <> vbNullString
End Function

Rick

 

[Rick Rothstein \(MVP - VB\)]

See all my inline comments....

Wow !

Yeah, that was pretty much my reaction when it first dawned on me that
IsNumeric didn't do what I thought it did. There are a lot of answers that I
posted in my early days of volunteering in the compiled VB newsgroups that
include the IsNumeric function as a "number proofer". Then, one day, by
chance, I typed in something like 12e3 into a TextBox in order to test the
error handling section of some code I wrote and, lo-and-behold, no error was
generated. It took a few seconds for it to dawn on me that the 4-character
garbage "number" I thought I typed in was really an actual 5-digit number
(12e3=12000) to the IsNumeric function. That set me off on my investigation
of the IsNumeric function which led to my publish article that I cited in my
last post.

While we are talking about surprises like what IsNumeric considers numbers,
VB/VBA contains lots of such surprises. Let me give you another one that I
had misunderstood at first... Integer Division (where you use the backward
slash to for the division sign). In the beginning, I had thought (like I
have found many people do) that A\B was a sort of short-hand for Int(A/B).
IT IS NOT! In Integer Division, if A and/or B are floating point numbers,
those numbers are rounded first, BEFORE the integer division takes place. On
top of that, the rounding method used is the one known as Banker's Rounding
(where numbers ending in 5 that are being rounded up to the previous digit
position round to the nearest even digit). Here is the problem... in my
beginnings with VB, I had figured (as most people still do) that 4.5\1.5 was
equivalent to Int(4.5/1.5) and that the answer would be 3; but IT IS NOT,
rather, the answer it 2! The 4.5 is rounded to 4 before the division and the
1.5 is rounded to 2 before the division... only then are they divided
producing 2 (from this...4/2) as the answer.

Actually, as my currency is not USD I did this -

s = "($1,23,,3.4,,,5,,E67$)"
s = Replace(s, "$", Application.International(xlCurrencyCode))

Raised my curiosity -

Dim d as double
on error resume next
d = s ' try to coerce
If err.number then
err.clear
else
blnIsNumeric = true
end if

With the your string, Internationalized if necessary, it can be coerced
and somehow returns -1.23345E+70

The commas are completely ignored... the string becomes -123345E+67 which,
in scientic notation, is simplified to -1.23345E+70.

However -
v = application.Evaluate(s) ' Error 2015

Which leads me to wonder if the following in help -
"Returns a Boolean value indicating whether an expression can be
evaluated as a number"

might be better written as "- can be COERCED as a number"

I think the "evaluated" part refers to the Cxxx converter such as CLng,
CDbl, etc. Perhaps you didn't know... try printing out (in the Immediate
window) that same string inside a CDbl function call....

s = "($1,23,,3.4,,,5,,E67$)"
s = Replace(s, "$", Application.International(xlCurrencyCode))
Print CDbl(s)

Yep, it prints out -1.23345E+70. If IsNumeric returns True for a string
expression, then CInt, CLng, CCur, CSng and CDbl will accept the string as a
valid argument and convert it to a number (assuming that number is small
enough to fit in the indicated data type). Cute, huh?

Not sure why a Date is not considered as numeric although it
can 'sometimes' if not typically be coerced to a double.

I never thought to try Date before. I think the answer is in what the date
represents. As a String, something like "6/7/08" (or even "6/7/2008") is not
really a number of any sort, so IsNumeric reports False for it and CDbl will
error out on it. Odd though, if you use the date delimiter symbols (#)
around it instead (that is, #6/7/08# or #6/7/2008#), CDbl convert it, but
IsNumeric will still report False for it. Very odd indeed.


Rick

 

[Peter T]

OK we agree that
cDbl("($1,23,,3.4,,,5,,E67$)") ' change '$' to local currency symbol
returns a value and also the string will coerce to a number if assigned to a
variable declared as a double.

Therefore surely we should expect IsNumeric to return true when testing that
string, which it does however surprising at first it may seem. So I'm
wondering after all that, is there anything actually unreliable or buggy
about IsNumeric.

However I still suspect Help might be better written along the lines I
suggested using the word 'coerced' vs 'evaluated', as further suggested in
the following

Sub test()
Dim s$, v
Dim dbl As Double
Dim dt As Date

s = "3,4.5,,,6,7"
s = "3.45"
v = Application.Evaluate(s) ' Error 2015
dbl = s ' 34.567

Debug.Print v, dbl, IsNumeric(s)
' can coerce but can't evaluate

''' Date
Debug.Print IsNumeric(Date) ' false as per help
s$ = Format(Date, "mmm dd yyyy")
dt = s ' OK
dbl = CDate(s) ' OK'
dbl = s ' error
'can only coerce to variable declared 'As Date'

End Sub

btw, behind the scenes is there any difference between doing
dbl = s vs dbl = cDbl(s) ?

I guess for consistency the authors thought best to exclude dates from being
considered IsNumeric, ie depending on the string the date may or may not
simply coerce to a number.

Concerning the Integer divisor: I had been aware of it's Banker's Rounding
nature for a long time but was surprised once to be hit with something like
this:
n = 4 \ 0.5 ' error div by zero

Regards,
Peter T

 

[Peter T]

However I still suspect Help might be better written along the lines I

suggested using the word 'coerced' vs 'evaluated'

Forgot about this bit you mentioned previously:

"I think the "evaluated" part refers to the Cxxx converter such as CLng,
CDbl, etc. Perhaps you didn't know... try printing out (in the Immediate
window) that same string inside a CDbl function call...."

Yes, with that interpretation of 'evaluated' vs app.evaluate it makes sense,
with the exception of cDate

Regards,
Peter T

 

[Rick Rothstein \(MVP - VB\)]

OK we agree that

cDbl("($1,23,,3.4,,,5,,E67$)") ' change '$' to local currency symbol
returns a value and also the string will coerce to a number if assigned to
a
variable declared as a double.

Therefore surely we should expect IsNumeric to return true when testing
that
string, which it does however surprising at first it may seem. So I'm
wondering after all that, is there anything actually unreliable or buggy
about IsNumeric.

Well, I think both the Cxxx functions and IsNumeric are to "generous" in
what they consider numbers. Why is a number with a minus sign in both the
front **and** back considered a valid number?

However I still suspect Help might be better written along the lines I
suggested using the word 'coerced' vs 'evaluated',
Agreed

''' Date
Debug.Print IsNumeric(Date) ' false as per help
s$ = Format(Date, "mmm dd yyyy")
dt = s ' OK
dbl = CDate(s) ' OK'
dbl = s ' error
'can only coerce to variable declared 'As Date'

In your last statement, the variable 's' is a Variant of sub-type String,
but that String value is no different to the assignment than a/b/c would be,
so I understand that type mismatch error.

btw, behind the scenes is there any difference between doing
dbl = s vs dbl = cDbl(s) ?

Well, technically, I think yes. Using CDbl specifically casts 's' as a
Double before the assignment is made. Just assigning 's' directly to 'dbl'
means VB's type coercion mechanism has to decide what to do... while I know
it doesn't do this, it could theoretically cast it internally to a Single
(losing precision) first before deciding it should be a Double. As I said,
I'm positive it doesn't do this, but why leave the job to chance when you
know what it should be?

I guess for consistency the authors thought best to exclude dates from
being
considered IsNumeric, ie depending on the string the date may or may not
simply coerce to a number.

What I don't understand is why IsNumeric(Now) returns False while CDbl(Now)
evaluates to a value (and, of course, the example I gave earlier). If
IsNumeric is a "proofer" for the Cxxx functions, why not handle this also?

Concerning the Integer divisor: I had been aware of it's Banker's Rounding
nature for a long time but was surprised once to be hit with something
like
this:
n = 4 \ 0.5 ' error div by zero

Yeah, I got caught with that one too. As a point of information, VB/VBA uses
Banker's Rounding in every situation where rounding is necessary EXCEPT
inside the Format function. The Format function uses what I consider to be
normal rounding rules (5's are always rounded upward)....

Print Format(5/2, "0") ====> 3

Rick

 

[Peter T]

Hi again,
Several interesting observations. FWIW cell's numberformat works similarly
to your Format example, ie an exact .5 always rounds up. If anything I would
have expected it to truncate down.

Well, I think both the Cxxx functions and IsNumeric are to "generous" in
what they consider numbers. Why is a number with a minus sign in both the
front **and** back considered a valid number?

I don't know about 'too generous', why not 'helpfully accommodating' <g>
As for "both front **and** back", I take it you mean either/or and not both
together. "-123-" fails, but why shouldn't "123-" be allowed to evaluate, or
rather coerce to a valid number.

If one accepts the IsNumeric function is consistent with 'can cDbl without
error' and 'is not a date' (as documented), perhaps it should not be
considered as buggy!

Regards,
Peter T