Edit Question - KnowledgeHub

Title Body

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: ?

Tags (comma-separated) Cancel