vba 乘以整数并分配给 Long 时溢出

Overflow when multiplying Integers and assigning to Long

提问by Excel_Cowboy_Yeeha

If I type the following into the immediate window I get Runtime error '6': Overflow.

如果我在即时窗口中键入以下内容，我会收到运行时错误“6”：溢出。

MsgBox 24 * 60 * 60

Why is this?

为什么是这样？

This also fails:

这也失败了：

Dim giveTime  As Long 
giveTime  = 24 * 60 * 60

Why is this? giveTimeis declared as a Long type, so 24 × 60 × 60 = 86400 should comfortably fit.

为什么是这样？giveTime被声明为 Long 类型，因此 24 × 60 × 60 = 86400 应该很合适。

回答by Jean-Fran?ois Corbett

This is a really odd VBA quirk. I'm amazed I've never bumped into this.

这是一个非常奇怪的 VBA 怪癖。我很惊讶我从来没有碰到过这个。

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 resultis usually the same as that of the most precise expression.

导致= NUMBER1* NUMBER2

[...]

结果的数据类型通常与最精确表达式的数据类型相同。

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.

默认情况下，您的文字 24、60 和 60 都是 Integer 类型，因此您的*(或+) 运算符返回一个 Integer，它会溢出，因为结果大于 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.

但是，上面第三个示例中的文字 32,768 默认为 Long 类型（因为它太大而不能成为 Integer），因此+返回一个 Long；没有溢出。

The help file also says this:

帮助文件也这样说：

If [...] the data type of resultis an Integer variantthat overflows its legal range [...] Then resultis [...] converted to a Long variant.

如果 [...]结果的数据类型是溢出其合法范围的 Integer变体[...] 然后结果[...] 转换为 Long 变体。

Emphasismine. 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.

强调我的。现在这个小规则听起来像是常识，任何人都会合理地假设它适用于您的情况。但是您的数字是整数类型，而不是变体/整数，因此 VBA 不适用此规则！对我来说绝对没有意义，但这就是事实，这就是文档所说的。

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.

解决方案：使您的*运算符的参数之一的类型比整数（例如 Long）更精确，问题就会消失。

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.

事实上，这可能就是为什么我从来没有遇到过这种怪癖的原因，我习惯于将所有 Integer 变量声明为 Long，除非特别担心使用 Longs 而不是 Integers 会导致内存使用问题或执行时间（几乎从来没有这种情况）。当然，当您对小于 32,768 的文字进行操作时，这将无济于事，因为它们默认为 Integer 类型。

---

---

You ask in a commentwhat 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:

您在评论中询问什么是 Variant/Integer。Variant 基本上是任何其他数据类型的容器类型。在你让它包含一个整数的特殊情况下：

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:

但如上所述，一个普通的旧 Integer 会触发溢出错误：

Dim b As Integer
b = 32767
x = b + 1 ' overflow

回答by tango_golfaus

After every number, place #. It defines each number as a double. Think of it as, each number is placed in to memory for the calculation as sort of a temp variable. If you define each number, it will allow enough space for the calculations.

在每个数字之后，放置#。它将每个数字定义为双精度数。把它想象成，每个数字都被放入内存中以作为一种临时变量进行计算。如果您定义每个数字，它将为计算留出足够的空间。

eg:

例如：

Dim x As Long

暗 x 长

x = 24# * 60# * 60#

x = 24# * 60# * 60#

or 24& 'indicates long

或 24&' 表示长