30

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
Community
  • 1
  • 1
tpascale
  • 2,516
  • 5
  • 25
  • 38

3 Answers3

2

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.

David Healy
  • 83
  • 4
1

I agree with David that your current workaround is acceptible. Be sure to test on foreign language installs though!

There example that you give could be part of the solution: a value that is equal to 1 and to 0 at the same time, is not a normal number. That test can distinguish NaNs from normal numbers. Possibly you can find similar rules like this to find +/-infinity.

0

Make sure you take into account current locale. NaN can be "-1.#IND" or "-1,#IND" depending on decimal separator settings.

One way to avoid this may be InStr comparison:

InStr(CStr(dVal), "#IND") <> 0
apan
  • 1