Another VBA bug
 

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

Another VBA bug
 

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

"Don Wiss" wrote:

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

Another VBA bug
 

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

Another VBA bug
 

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



"Rick Rothstein (MVP - VB)" wrote in
message ...
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

Another VBA bug
 

See all my inline comments....

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

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