To infinity and beyond in VBA

Question

This post is about .NET NaN's & Inifinite values getting passed back to Excel 2010 VBA.

I use a C# method not under my control which (apparently) can return .NET NaN's or Neg,Pos Infinity. The results in VBA are weird (i.e. weirder than usual), and the only way I have come up with to deal with the situation safely is an inelegant triple-string comparison "-1.#IND" or "-1.#INF" or "1.#INF".

Is there a better way?

I've documented the weird part here if you're curious. (The examples are for NaN, but it's the same story for pos or neg infinity.)

double  dVal  =  CSharpMethodReturningDouble()  ' via .NET assembly / COM interop variant vVal  =  CSharpMethodReturningDouble()  ' via .NET assembly / COM interop 

If the C# method returns a double.NaN, then we have (in the immed window):

?dVal                 -1.#IND             ?vVal                  -1.#IND              

The (boxed) variant holding the NaN tests positive for numeric, type = double

?IsNumeric(vVal)   True ?TypeName(vVal)  Double 

Comparisons on the (boxed) variant NaN work, but with the opposite results you'd expect. Comparisons on the (unboxed) doubles cause overflow exceptions

?vVal=1          '<== NaN comparisons should always return false  True                ?vVal=0          '<== that's not what you get with -1.#IND  True                ?dVal=0          '<== strangely, the same comparison on the unboxed double fails  (OverFlow Exc) 

Operations on the (boxed) variant cause overflow exceptions Operations on the (unboxed) doubles work (and return -1.#IND, as expected)

?vVal * 1.1      '<== even stranger, for arith ops its the boxed value that fails  (Overflow Exc) ?dVal * 1.1      '<== but the operation on the unboxed double goes through -1.#IND  

IsError, IsNumeric don't help:

?IsError(vVal)  False             ?IsError(dVal)  False             ?IsNumeric(vVal)  True        ?IsNumeric(dVal)  True             

Can always use string comparison to test:

?vVal = "-1.#IND" True ?dVal = "-1.#IND" True 

Answer 1

Since a Double.NAN field represents a value that is not a number, you're very likely on the right track using a string comparison.

If you were going the other way (i.e., passing values out of VB), ByRef and ByVal are the usual suspects.

VB's IsNumeric() function isn't always intuitive. For example, it will return True if an alphanumeric code happens, by chance, to be a number in scientific notation.