Dividing 1 by a large(ish) number (not that large!)

[AOB]

Hi there,

Having a strange (i.e. unexpected) error when attempting to divide 1 by a large number (specifically : 345,600)

( 1/(60*60*24*4) represents a quarter of a second as a fraction of one day...)

The answer should be ~2.893519x10^-6

It results in an overflow but I'm using the Double variable type which I would have thought could handle this?

Dim x As Double
x = 1 / (60 * 60 * 24 * 4)

From Excel Help :

Double (double-precision floating-point)
variables are stored as IEEE 64-bit (8-byte) floating-point numbers ranging
in value from -1.79769313486231E308 to -4.94065645841247E-324 for negative
values and from 4.94065645841247E-324 to 1.79769313486232E308 for positive
values. The type-declaration character for Double is the number sign (#).

Any ideas as to why this would be causing an overflow, and thus how I can get around it?

Thanks!

AOB

 

[Joe4]

Interesting. I don't know what it is doing that.
However, this workaround seems to work:

Dim x As Double
x = 1 / 345600

 

[mole999]

what happens when you don't declare the double

 

[Joe4]

what happens when you don't declare the double

I tried no declaration, Double, Variant. None of them seemed to work.
But if just hard-code in the result of the multiplication, it seems to work just fine.

Very odd behavior, I must say...

 

[Eric W]

From this page:

https://msdn.microsoft.com/en-us/library/office/gg278733.aspx

They suggest doing this:

Dim x As Double
x = 1 / (CLng(60) * 60 * 24 * 4)

The intermediate result exceeds an Integer size, so you need to tell it somehow that you expect the intermediate result to be bigger.

I suspect Joe's workaround worked because when VBA saw 345600, it knew it needed a Long to hold it. It's still a little odd to me that an intermediate result would be based on the first value it sees, and not just use a variant.

 

[AOB]

Brilliant! Thanks guys. I thought I was going crazy...

 

[wideboydixon]

You can also use type characters to indicate the type you want to use:

https://msdn.microsoft.com/en-us/library/s9cz43ek.aspx

So this also works:

Dim x#
x = 1 / (60& * 60 * 24 * 4)

WBD

 

[GTO]

...The intermediate result exceeds an Integer size, so you need to tell it somehow that you expect the intermediate result to be bigger.

... It's still a little odd to me that an intermediate result would be based on the first value it sees, and not just use a variant.

Hi Eric,

I read the link and wish I could find the one I was referred to some time ago, as I recall it being more detailed and consequently, less "odd" sounding, albeit something that took my poor noggin some hurting to wrap around. Anyways, I hope I get this right, but I don't believe it's an issue of the 'first value it sees'. Rather, while I may write:

x = (2 * 3 * 4)

'x' is just the only variable I know about, but vba is creating a temp variable to first hold the return of (2 * 3) and this variable is sized (cast or typed) based on the largest value being worked on, in this case the 3. Then the temp variable holds the return of (6 * 4), sized as an Integer based on the 6. I do not recall whether the temp variable(s) created are one or many. Regardless, as long as the created temp variable is sized to a Long based on one of the two values evaluating to a Long, everything is hunky-dory; but no matter where it happens in our overall expression, if both the two values currently being looked at/worked on fit in Integer and the result is a Long, the temp variable goes suicidal.

Not sure if my example code is any clearer than my likely muddy explanation...

Sub ahh()
Dim x As Variant, Line As Long


'Orig
'Dim x As Double
'x = 1 / (60 * 60 * 24 * 4)


On Error GoTo errMsg


' Parenthesis included for clarity that a temp variable is created and cast/typed as the 'smallest' needed
' to handle the largest number in the expression being evaluated between the parenthesis.  Hopefully I'm not
' being inarticulate...


Line = 1: x = (60)                      'Fine as 60 is evaluated as an Integer and
                                        'the return cast as Integer is 60/okay
                                        
Line = 2: x = (60 * 60)                 'Fine as both 60's are evaluated as Integers and
                                        'the return cast as Integer is 3600/okay
                                        
Line = 3: x = (60 * 60 * 24)            'Bombs as all values being worked on evaluate to Integer,
                                        'but the return cast as Integer is 86400 and won't fit in Integer


Line = 4: x = (86400 * 4)              'Fine as one of the numbers will be evaluated to Long and the return
                                        'cast as Long fits


Line = 5: x = (4 * 86400)              'See above. The order does not matter, BUT... one of the values must be large
                                        'enough to cast the temp variable to Long before the intermediate returns overflows 2-bytes


Line = 6: x = (CLng(60) * 60 * 24)      'Likewise any of these are Ok
Line = 7: x = (60 * CLng(60) * 24)
Line = 8: x = (60 * 60 * CLng(24))


Line = 9: x = (60 * 60 * CLng(24) * 4) 'Still Ok, as within the expression being worked on, as (3600 * 24&) is cast as a Long


Line = 10: x = (60 * 60 * 24 * 4)       'Kaboom


Line = 11: x = (60 * 60 * 24 * CLng(4)) 'Still Kaboom. (60 * 60) is cast as Integer and the return (3600) fits, but (3600 * 24) [both integers] gets cast as an Integer,
                                        'but the return doesn't fit an Integer and cLng(4) is not being calulated yet.
                                        
Line = 12: x = (60 * 60 * 4& * 24)      'So this works because the temp variable created is changed to a Long at 3600 * cLng(4)


Exit Sub
errMsg:
  MsgBox "error line #" & Line
  Resume Next
End Sub

Mark

 

[Eric W]

Hi Mark,

Certainly a more detailed investigation of the issue. Very nice. Every once in a while I toy with the idea of writing a compiler, but I look at all the odd situations like this and I shudder!

Let me know if you find the link, I'd be curious to see Microsoft's explanation.

 

[GTO]

Hi Mark,

Certainly a more detailed investigation of the issue. Very nice. Every once in a while I toy with the idea of writing a compiler, but I look at all the odd situations like this and I shudder!

Let me know if you find the link, I'd be curious to see Microsoft's explanation.

Thank you and I sure will. (Too bad I am oft horrid at searching, but hopefully...)

Mark