title: To infinity and beyond in VBA
body: <p>This post is about .NET NaN's &amp; Inifinite values getting passed back to Excel 2010 VBA.</p>
  
  <p>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".  </p>
  
  <p>Is there a better way?</p>
  
  <p>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.)</p>
  
  <pre><code>double  dVal  =  CSharpMethodReturningDouble()  ' via .NET assembly / COM interop
  variant vVal  =  CSharpMethodReturningDouble()  ' via .NET assembly / COM interop
  </code></pre>
  
  <p>If the C# method returns a double.NaN, then we have (in the immed window):</p>
  
  <pre><code>?dVal               
   -1.#IND            
  ?vVal                
   -1.#IND             
  </code></pre>
  
  <p>The (boxed) variant holding the NaN tests positive for numeric, type = double</p>
  
  <pre><code>?IsNumeric(vVal) 
   True
  ?TypeName(vVal)
   Double
  </code></pre>
  
  <p>Comparisons on the (boxed) variant NaN work, but with the opposite results you'd expect. 
  Comparisons on the (unboxed) doubles cause overflow exceptions</p>
  
  <pre><code>?vVal=1          '&lt;== NaN comparisons should always return false
   True               
  ?vVal=0          '&lt;== that's not what you get with -1.#IND
   True               
  ?dVal=0          '&lt;== strangely, the same comparison on the unboxed double fails
   (OverFlow Exc)
  </code></pre>
  
  <p>Operations on the (boxed) variant cause overflow exceptions
  Operations on the (unboxed) doubles work (and return -1.#IND, as expected)</p>
  
  <pre><code>?vVal * 1.1      '&lt;== even stranger, for arith ops its the boxed value that fails
   (Overflow Exc)
  ?dVal * 1.1      '&lt;== but the operation on the unboxed double goes through
  -1.#IND 
  </code></pre>
  
  <p>IsError, IsNumeric don't help:</p>
  
  <pre><code>?IsError(vVal)
   False            
  ?IsError(dVal)
   False            
  ?IsNumeric(vVal)
   True       
  ?IsNumeric(dVal)
   True            
  </code></pre>
  
  <p>Can always use string comparison to test:</p>
  
  <pre><code>?vVal = "-1.#IND"
  True
  ?dVal = "-1.#IND"
  True
  </code></pre>