Re: Another VBA bug

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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


"Rick Rothstein (MVP - VB)" <rickNOSPAMnews@xxxxxxxxxxxxxxxxx> wrote in
message news:%23HuPEBtVIHA.4696@xxxxxxxxxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: coerce for arbitrary types
    ... coerce to accept any other result-type than the ones explicetly listed. ... to infer "intent" because the fact of a representation's use is not ... different things depending on the intention of the programmer as ... parsing from a string, and no further processing was done after ...
    (comp.lang.lisp)
  • Re: Another VBA bug
    ... In Integer Division, if A and/or B are floating point numbers, those numbers are rounded first, BEFORE the integer division takes place. ... d = s ' try to coerce ... try printing out that same string inside a CDbl function call.... ...
    (microsoft.public.excel.programming)
  • Re: Another VBA bug
    ... to your Format example, ie an exact .5 always rounds up. ... rather coerce to a valid number. ... string, which it does however surprising at first it may seem. ... dbl = CDate' OK' ...
    (microsoft.public.excel.programming)
  • Re: Another VBA bug
    ... string, which it does however surprising at first it may seem. ... dbl = CDate' OK' ... 'can only coerce to variable declared 'As Date' ... Using CDbl specifically casts 's' as a Double before the assignment is made. ...
    (microsoft.public.excel.programming)
  • Re: Another VBA bug
    ... d = s ' try to coerce ... Function IsDigitsOnly(Value As String) As Boolean ... ' Get local setting for decimal point ...
    (microsoft.public.excel.programming)