vba, 연산자 결과 overflow... 결과가 integer를 넘으면 모두 long으로...펌

Overflow when multiplying Integers and assigning to Long

No problem. We won't show you that ad again. Why didn't you like it?

• Uninteresting
• Misleading
• Offensive
• Repetitive

Oops! I didn't mean to do this.

up vote4down votefavorite

If I type the following into the immediate window I get Runtime error '6': Overflow. MsgBox 24 * 60 * 60 Why is this? This also fails: Dim giveTime As Long giveTime = 24 * 60 * 60 Why is this? giveTime is declared as a Long type, so 24 × 60 × 60 = 86400 should comfortably fit. vba integer-overflow share|improve this question edited Jun 27 at 6:35 Jean-François Corbett 12.1k82661 asked Jun 26 at 8:56 Excel_Cowboy_Yeeha 233

Further to this. - if I put msgbox 26 * 60 ^ 2 in the immediate window there's no problem. Is it an order of precedence issue? That seems unlikely... – Excel_Cowboy_Yeeha Jun 26 at 9:02 That's because the ^ operator returns a Double, which sidesteps the problem described in my answer below. – Jean-François Corbett Jun 26 at 10:38

1 Answer

activeoldestvotes

up vote7down voteaccepted

This is a really odd VBA quirk. I'm amazed I've never bumped into this. Dim x As Long x = 24 * 60 * 60 ' Overflow x = 32767 + 1 ' Overflow. x = 32768 + 1 ' Works fine! So it looks like the * and + operators return an Integer in the first two examples. Sure enough, in the help file for the *operator (similar for the + operator): result = number1 * number2 [...] The data type of result is usually the same as that of the most precise ｅxpression. Your literals 24, 60, and 60 are all of type Integer by default, so your * (or +) operator returns an Integer, which overflows because the result is greater than 32,767. However, the literal 32,768 in the third example above defaults to Long type (since it is too large to be an Integer) and so the + returns a Long; no overflow. The help file also says this: If [...] the data type of result is an Integer variant that overflows its legal range [...] Then result is [...] converted to a Long variant. Emphasis mine. Now this little rule sounds like common sense, and anyone would reasonably assume that it applies in your case. But your numbers are of type Integer, not Variant/Integer, so VBA doesn't apply this rule! Makes absolutely no sense to me, but that's how it is, and that's what the documentation says. Solution: make one of the arguments of your * operator be of a type more precise than Integer (e.g. Long) and the problem will go away. x = CLng(24) * 60 * 60 ' Result is Long, works fine. In fact, and this is probably why I've never bumped into this quirk, I make a habit of declaring all of my Integer variables as Long instead, unless there is a specific concern that having Longs instead of Integers will cause problems with memory use or execution time (which is almost never the case). Granted, this won't help in cases when you operate on literals smaller than 32,768, because they default to Integer type. In your comment, you ask what a Variant/Integer is. Variant is basically a container type for any other data type. In the particular case where you make it contain an Integer: Dim a As Variant ' a is now Empty a = CInt(32767) ' a is now Variant/Integer x = a + 1 ' works fine But as noted above, a plain old Integer triggers the overflow error: Dim b As Integer b = 32767 x = b + 1 ' overflow share|improve this answer edited Jun 27 at 6:52 answered Jun 26 at 9:22 Jean-François Corbett 12.1k82661

Certainly is a Quirk! So, what's the difference between a Variant/Integer and an Integer? I presume one is declared in the code, the other declared internally? – Excel_Cowboy_Yeeha Jun 26 at 9:50 See edited answer. – Jean-François Corbett Jun 26 at 10:19 Got it. Nice one, thanks! – Excel_Cowboy_Yeeha Jun 26 at 10:42