Re: Another VBA bug

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

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

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



.

Relevant Pages

  • 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
    ... string, which it does however surprising at first it may seem. ... Dim dbl As Double ... ' can coerce but can't evaluate ... In Integer Division, if A and/or B are floating point numbers, ...
    (microsoft.public.excel.programming)
  • Re: If/Then Function Question
    ... That is to coerce the text string into a number. ... format as it seems to work with all international settings. ...
    (microsoft.public.excel)
  • Re: Date confusion
    ... Read my reply elsewhere in this string. ... > If you are working with a numeric date and you want the format DDMMYYYY, ... Store the date as a date, and use a format statement to display it how ... since you're relying on Access to coerce the value from ...
    (microsoft.public.access.modulesdaovba)
  • Re: Formating Hex Values
    ... Not just format but any place VB attempts to coerce from string to numeric. ... Your initial version did numeric to string with the Hex function, then string to numeric as the first parameter to the format function, then numeric to string again as the result of the format. ...
    (microsoft.public.vb.general.discussion)