VBA Overflow error with type double, Excel

Question

I don't know the overflow error very well, so I'm pretty at a loss here.

Writing in VBA I'm calling a VBA function, which is a wrapper of a C++ function from a dll (which I have no access to the innards of). The VBA/C++ function returns a double. It is a derivative valuation using numerical methods.

For a time yesterday (but it may have gone away now) I was getting the overflow error when trying assign the function return to a variable declared as double. But on the error halt, I could see the correct value assigned to the double, in the locals window (8.17...). And when I instead assign the function return to a variant, I get the same value with no error, the variant being type Variant/Double.

Any idea what could be going on here? I'm completely lost. Thanks in advance for any ideas. R

Edit: One more thing I tried was applying CDbl to the function return when assigning it to the variant. Here's some pseudo code, just to clarify my cases:

Dim db as Double, va as Variant

db = DerivativeValue(a, b, c...) 

This gives the overflow, but db has the correct value in the locals window, on the error.

va = DerivativeValue(a, b, c...)

No error, same value. va is type Variant/Double

va = CDbl(DerivativeValue(a, b, c...))

Now overflow again. I forgot what va did in the Locals window in this case, but likely it's the same as case 1.

Answer 1

My guess is that the function is returning a value that looks like a double but is not.

Variants are designed to take any type of value and the assignment will perform conversions. I wonder if the assignment to a variant works because it can convert the returned value to a double.

What happens if you try the following:

va = DerivativeValue(a,b,c...)
db = va

If this works, it will let you continue development without waiting to discover what the function is actually returning.

Extra point. A double is a 64 bit floating point number. If you have a FPU, it might be returning an 80 bit floating point number.