To infinity and beyond in VBA

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:

?