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