Incorrect VBA Overflow using Doubles (Excel)

Question

For some reason, the following statement evaluates to zero. I assume it's due to overflow, but all the interim values seem to be well within the limits of a double.

DiffieHellmanKey = (43 ^ 47) - (53 * Fix((43 ^ 47) / 53))

I think it's overflow because when I execute it with different numbers (below), it results in the correct value of 29.

DiffieHellmanKey = (5 ^ 22) - (53 * Fix((5 ^ 22) / 53))

What gives? Now, back to the original numbers that are giving me overflow. All variables involved are Doubles. It doesn't even work if I calculate it as a Worksheet formula instead of in VBA:

=(43 ^ 47) - (53 * ROUNDDOWN(((43 ^ 47) / 53), 0))

And if I implement the above example in VBA using the equivalent form (below), I get an incorrect result of -1.75357E+62.

DiffieHellmanKey = (43 ^ 47) - (53 * WorksheetFunction.RoundDown(((43 ^ 47) / 53), 0))

danielpiestrak · Accepted Answer · 2012-10-19 02:20:33Z

1

You are kind of right but your problem is not with overflow, but with significant digits.

Numbers in Microsoft Excel can never have more than 15 significant digits, but decimals can be as large as 127.

source: http://msdn.microsoft.com/en-us/library/office/ff838588.aspx

This is what was happening with your original formula:

(43 ^ 47) - (53 * Fix((43 ^ 47) / 53)) simplifies to (43 ^ 47) - fix(43 ^ 47) Then the (43^47) has about 76 digits long so they are both getting cut down to the same amount and equating to 0.

The largest variable type in VBA is 'Decimal' Which only holds 29 digits of significance. You won't be able to perform math this large using visual basic natively.

edited Oct 19, 2012 at 2:20

answered Oct 19, 2012 at 1:25

danielpiestrak

5,4793 gold badges33 silver badges30 bronze badges

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

4 Comments

ndgibson Over a year ago

I understand the logic here, but for me your fixed function: (.43 ^ 47) - (53 * Fix((.43 ^ 47) / 53))*10000 evaluates to 5.92949097309764E-18, not 29.

danielpiestrak Over a year ago

Did some double checking, and realized even my answer still hit the max. Tried a few other things and ended up with the above answer. The bottom line is we need up to 74 digits of precision for your example and none of the variables in VBA go that high.

ndgibson Over a year ago

Okay, thanks. So then strings would be the only way to go, it seems.

danielpiestrak Over a year ago

I'd be interested to know if there is a workaround in VBA to perform calculations this large. My guess is that you should be able to make an array that holds say 300 digits 0-9 and use it as a massive variable. Then to write functions that perform mathematical functions on it.

Tim Williams · Accepted Answer · 2012-10-19 00:57:28Z

1

? (43^47) 
 5.92949097309764E+76

? 53*Fix((43^47)/53)
 5.92949097309764E+76 

? (53*Fix((43^47)/53)) = (43^47)
True

answered Oct 19, 2012 at 0:57

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

6 Comments

ndgibson Over a year ago

I had my numbers switched in some of the examples, sorry. Fixed.

Tim Williams Over a year ago

I was looking at your first example, and it still looks the same to me. What do you expect as a result there?

ndgibson Over a year ago

It should evaluate to 29. This is basic RSA cryptography, so all of the four examples above should give 29. Only the second one (with the smaller numbers) works.

Tim Williams Over a year ago

I think you're butting up against the limit of precision for doubles in VBA (about 15 digits?)

Tim Williams Over a year ago

You can do this kind of thing in VB(A), but it requires handling the required large integers as strings: di-mgt.com.au/crypto.html#dhvb

|

Collectives™ on Stack Overflow

Incorrect VBA Overflow using Doubles (Excel)

2 Answers 2

4 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related