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.

Tony Dallimore · Accepted Answer · 2012-01-21 19:48:42Z

0

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.

edited Jan 21, 2012 at 19:48

answered Jan 21, 2012 at 19:37

Tony Dallimore

12.4k7 gold badges35 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RomnieEE2 Over a year ago

Thanks. My error seems to have fixed itself for the moment. But I'll these and report back otherwise if it comes up again.

Collectives™ on Stack Overflow

VBA Overflow error with type double, Excel

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related