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.

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
    ... 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. ... 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: 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)
  • Problem mit XML Serialisierung
    ... public sealed partial class Assignment { ... XmlSerializer serializer = new XmlSerializer); ... private string david; ... private Listdatabases; ...
    (microsoft.public.de.german.entwickler.dotnet.csharp)
  • Re: OpenForm vs. Form menu double click.
    ... was a string and the data type of the where variable matched the syntax... ... In my situatiation the SSN on the input is text as well as the SSN field in ... A command button to add an assignment ... Private Sub Form_Open ...
    (microsoft.public.access.formscoding)