vba 为什么使用整数而不是长整数？

Why Use Integer Instead of Long?

提问by Gareth

I often see questions relating to Overflowerrors with vba.

我经常看到Overflow与vba错误有关的问题。

My question is why use the integervariable declaration instead of just defining all numerical variables (excluding doubleetc.) as long?

我的问题是为什么使用integer变量声明而不是仅仅将所有数值变量（不包括double等）定义为long?

Unless you're performing an operation like in a for loop where you can guarantee that the value won't exceed the 32,767 limit, is there an impact on performance or something else that would dictate not using long?

除非您正在执行类似于 for 循环的操作，您可以保证该值不会超过 32,767 限制，否则是否会对性能或其他要求不使用的影响产生影响long？

回答by RubberDuck

Integer variables are stored as 16-bit (2-byte) numbers

整数变量存储为 16 位（2 字节）数字

Office VBA Reference

Office VBA 参考

Long (long integer) variables are stored as signed 32-bit (4-byte) numbers

Long（长整型）变量存储为有符号的 32 位（4 字节）数字

Office VBA Reference

Office VBA 参考

So, the benefit is in reduced memory space. An Integer takes up half the memory that a Long does. Now, we are talking about 2 bytes, so it's not going to make a real difference for individual integers, it's only a concern when you are dealing with TONS of integers (e.g large arrays) and memory usage is critical.

因此，好处是减少了内存空间。Integer 占用的内存是 Long 的一半。现在，我们谈论的是 2 个字节，因此它不会对单个整数产生真正的影响，这只是在处理 TONS 整数（例如大数组）并且内存使用至关重要时才需要考虑的问题。

BUTon a 32bit system, the halved memory usage comes at a performance cost. When the processor actually performs some computation with a 16 bit integer (e.g. incrementing a loop counter), the value silently gets converted to a temporary Long without the benefit of the larger range of numbers to work with. Overflows still happen, and the register that the processor uses to store the values for the calculation will take the same amount of memory (32 bits) either way. Performance may even be hurtbecause the datatype has to be converted (at a very low level).

但是在32位系统上，内存使用量减半是以性能为代价的。当处理器实际使用 16 位整数执行某些计算时（例如递增循环计数器），该值会被悄悄地转换为临时 Long，而无法使用更大范围的数字。溢出仍然发生，处理器用来存储计算值的寄存器将占用相同数量的内存（32 位）。性能甚至可能会受到损害，因为必须转换数据类型（在非常低的级别）。

Not the reference I was looking for but....

不是我正在寻找的参考，但是......

My understanding is that the underlying VB engine converts integers to long even if its declared as an integer. Therefore a slight speed decrease can be noted. I have believed this for some time and perhaps thats also why the above statement was made, I didnt ask for reasoning.

我的理解是底层的 VB 引擎将整数转换为 long，即使它被声明为整数。因此可以注意到轻微的速度下降。我已经相信了一段时间，也许这也是为什么做出上述陈述的原因，我没有要求推理。

ozgrid forums

ozgrid 论坛

This is the reference I was looking for.

这是我正在寻找的参考。

Short answer, in 32-bit systems 2 byte integers are converted to 4 byte Longs. There really is no other way so that respective bits correctly line up for any form of processing. Consider the following

MsgBox Hex(-1) = Hex(65535) ' = True

Obviously -1 does not equal 65535 yet the computer is returning the correct answer, namely "FFFF" = "FFFF"

However had we coerced the -1 to a long first we would have got the right answer (the 65535 being greater than 32k is automatically a long)

MsgBox Hex(-1&) = Hex(65535) ' = False

"FFFFFFFF" = "FFFF"

Generally there is no point in VBA to declare "As Integer" in modern systems, except perhaps for some legacy API's that expect to receive an Integer.

简短的回答，在 32 位系统中，2 字节整数被转换为 4 字节长整数。确实没有其他方法可以使各个位正确排列以进行任何形式的处理。考虑以下

MsgBox Hex(-1) = Hex(65535) ' = True

显然 -1 不等于 65535 但计算机返回正确答案，即“FFFF”=“FFFF”

但是，如果我们先将 -1 强制为 long，我们就会得到正确的答案（大于 32k 的 65535 自动为 long）

MsgBox Hex(-1&) = Hex(65535) ' = False

"FFFFFFFF" = "FFFF"

通常，在现代系统中，VBA 中没有必要将“作为整数”声明为“作为整数”，除了一些希望接收整数的遗留 API。

pcreview forum

pcreview 论坛

And at long last I found the msdn documentationI was really truly looking for.

终于，我找到了我真正想要的msdn 文档。

Traditionally, VBA programmers have used integers to hold small numbers, because they required less memory. In recent versions, however, VBA converts all integer values to type Long, even if they're declared as type Integer. So there's no longer a performance advantage to using Integer variables; in fact, Long variables may be slightly faster because VBA does not have to convert them.

传统上，VBA 程序员使用整数来保存小数，因为它们需要较少的内存。然而，在最近的版本中，VBA 将所有整数值转换为 Long 类型，即使它们被声明为 Integer 类型。因此，使用 Integer 变量不再具有性能优势；事实上，Long 变量可能会稍微快一些，因为 VBA 不必转换它们。

_{To clarify based on the comments: Integers stillrequire less memory to store - a large array of integers will need significantly less RAM than an Long array with the same dimensions. But because the processor needs to work with 32 bit chunks of memory, VBA converts Integers to Longs temporarilywhen it performs calculations}

_{根据评论澄清：整数仍然需要更少的内存来存储 - 与具有相同维度的 Long 数组相比，大型整数数组需要的 RAM 少得多。但是因为处理器需要使用 32 位的内存块，所以 VBA在执行计算时会临时将 Integers 转换为 Longs}

---

---

So, in summary, there's almost no good reason to use an Integertype these days. Unlessyou need to Interop with an old API call that expects a 16 bit int, or you are working with large arrays of small integers and memory is at a premium.

所以，总而言之，现在几乎没有充分的理由使用一种Integer类型。除非您需要与需要 16 位 int 的旧 API 调用进行互操作，或者您正在使用小整数的大型数组并且内存非常宝贵。

One thing worth pointing out is that some old API functions may be expecting parameters that are 16-bit (2-byte) Integers and if you are on a 32 bit and trying to pass an Integer (that is already a 4-byte long) by reference it will not work due to difference in length of bytes.

值得指出的一件事是，一些旧的 API 函数可能需要 16 位（2 字节）整数的参数，如果您使用 32 位并尝试传递整数（已经是 4 字节长）通过引用，由于字节长度不同，它将不起作用。

Thanks to Vba4All for pointing that out.

感谢 Vba4All 指出这一点。

回答by Patrick

As noted in other answers, the real difference between int and long is the size of its memory space and therefore the size of the number it can hold.

正如其他答案中所指出的，int 和 long 之间的真正区别在于其内存空间的大小以及它可以容纳的数字的大小。

here is the full documentation on these datatypes http://msdn.microsoft.com/en-us/library/office/ms474284(v=office.14).aspx

这是有关这些数据类型的完整文档 http://msdn.microsoft.com/en-us/library/office/ms474284(v=office.14).aspx

an Integeris 16 bits and can represent a value between -32,768 and 32,767

一个整数是16位，并且可以表示-32,768和32767之间的值

a Longis 32 bits and can represent -2,147,483,648 to 2,147,483,647

a Long是 32 位，可以表示 -2,147,483,648 到 2,147,483,647

and there is a LongLongwhich is 64 bits and can handle like 9 pentilion

并且有一个LongLong是 64 位，可以像 9 pentilion 一样处理

One of the most important things to remember on this is that datatypes differ by both language and operating system / platform. In your world of VBA a long is 32 bits, but in c# on a 64 bit processor a long is 64 bits. This can introduce significant confusion.

要记住的最重要的事情之一是数据类型因语言和操作系统/平台而异。在您的 VBA 世界中，long 是 32 位，但在 64 位处理器上的 c# 中，long 是 64 位。这可能会导致严重的混淆。

Although VBA does not have support for it, when you move to any other language in .net or java or other, I much prefer to use the system datatypes of int16, int32and int64which allows me to b much more transparent about the values that can be held in these datatypes.

尽管 VBA 不支持它，但是当您使用 .net 或 java 或其他语言中的任何其他语言时，我更喜欢使用int16、int32和int64的系统数据类型，这使我能够更加透明地了解可以保存在这些数据类型中。

回答by Alex K.

VBA has a lot of historical baggage.

VBA 有很多历史包袱。

An Integeris 16 bits wide and was a good default numeric type back when 16 bit architecture/word sizes were prevalent.

AnInteger是 16 位宽，在 16 位体系结构/字大小流行时是一个很好的默认数字类型。

A Longis 32 bits wide and (IMO) should be used wherever possible.

ALong是 32 位宽，应尽可能使用 (IMO)。

回答by PGSystemTester

Even though this post is four years old, I was curious about this and ran some tests. The most important thing to note is that a coder should ALWAYS declare a variable as SOMETHING. Undeclared variables clearly performed the worst (undeclared are technically Variant)

尽管这篇文章已有四年历史，但我对此很好奇并进行了一些测试。需要注意的最重要的事情是编码人员应该始终将变量声明为 SOMETHING。未声明的变量显然表现最差（技术上未声明的变量Variant）

Longdid perform the fastest, so I have to think that Microsoft's recommendation to always use Longinstead of Integermakes sense. I'm guessing the same as true with Byte, but most coders don't use this.

Long确实执行得最快，所以我不得不认为微软的建议总是使用Long而不是Integer有意义的。我猜和 一样Byte，但大多数编码员不使用它。

RESULTS ON 64 BIT WINDOWS 10 LAPTOP

在 64 位 WINDOWS 10 笔记本电脑上的结果

Code Used:

使用的代码：

Sub VariableOlymics()
'Run this macro as many times as you'd like, with an activesheet ready for data
'in cells B2 to D6
Dim beginTIME As Double, trials As Long, i As Long, p As Long

    trials = 1000000000
    p = 0

    beginTIME = Now
    For i = 1 To trials
        Call boomBYTE
    Next i
    Call Finished(p, Now - beginTIME, CDbl(trials))
    p = p + 1

    beginTIME = Now
    For i = 1 To trials
        Call boomINTEGER
    Next i
    Call Finished(p, Now - beginTIME, CDbl(trials))
    p = p + 1


    beginTIME = Now
    For i = 1 To trials
        Call boomLONG
    Next i
    Call Finished(p, Now - beginTIME, CDbl(trials))
    p = p + 1


    beginTIME = Now
    For i = 1 To trials
        Call boomDOUBLE
    Next i
    Call Finished(p, Now - beginTIME, CDbl(trials))
    p = p + 1


    beginTIME = Now
    For i = 1 To trials
        Call boomUNDECLARED
    Next i
    Call Finished(p, Now - beginTIME, CDbl(trials))
    p = p + 1

End Sub


Private Sub boomBYTE()
Dim a As Byte, b As Byte, c As Byte

    a = 1
    b = 1 + a
    c = 1 + b
    c = c + 1

End Sub


Private Sub boomINTEGER()
Dim a As Integer, b As Integer, c As Integer

    a = 1
    b = 1 + a
    c = 1 + b
    c = c + 1

End Sub


Private Sub boomLONG()
Dim a As Long, b As Long, c As Long

    a = 1
    b = 1 + a
    c = 1 + b
    c = c + 1

End Sub


Private Sub boomDOUBLE()
Dim a As Double, b As Double, c As Double

    a = 1
    b = 1 + a
    c = 1 + b
    c = c + 1

End Sub


Private Sub boomUNDECLARED()

    a = 1
    b = 1 + a
    c = 1 + b
    c = c + 1

End Sub

Private Sub Finished(i As Long, timeUSED As Double, trials As Double)

    With Range("B2").Offset(i, 0)
            .Value = .Value + trials
            .Offset(0, 1).Value = .Offset(0, 1).Value + timeUSED
            .Offset(0, 2).FormulaR1C1 = "=ROUND(RC[-1]*3600*24,0)"
    End With

End Sub

回答by Alter

This is a spacevs necessityproblem.

这是一个空间与必要性的问题。

In some situations it's a necessityto use a long. If you're looping through rows in a large excel file, the variable that holds the row number should be a long.

在某些情况下，有必要使用 long。如果您在大型 excel 文件中循环遍历行，则保存行号的变量应该很长。

However, sometimes you will know that an integer can handle your problem and using a long would be a waste of space(memory). Individual variables really don't make much of a difference, but when you start dealing with arrays it can make a big difference.

但是，有时您会知道整数可以解决您的问题，而使用 long 会浪费空间（内存）。单个变量确实没有太大区别，但是当您开始处理数组时，它会产生很大的不同。

• In VBA7, Integers are 2 bytes and longs are 4 bytes

• If you have an array of 1 million numbers between 1 and 10, using an Integer array would take up about2MB of RAM, compared to roughly 4MB of RAM for a long array.

• 在 VBA7 中，整数为 2 个字节，长整数为 4 个字节

• 如果您有一个包含 1 到 10 之间的 100 万个数字的数组，则使用 Integer 数组将占用大约2MB 的 RAM，而长数组大约需要 4MB 的 RAM。

回答by AJD

I have taken @PGSystemTester's method and updated it to remove some potential variability. By placing the loop in the routines, this removes the time taken to call the routine (which is a lot of time). I have also turned off screen updating to remove any delays this may cause.

我采用了@PGSystemTester 的方法并更新了它以消除一些潜在的可变性。通过在例程中放置循环，这消除了调用例程所花费的时间（这是很多时间）。我还关闭了屏幕更新以消除这可能导致的任何延迟。

Longstill performed the best, and as these results are more closely limited to the impacts of the variable types alone, the magnitude of variation is worth noting.

Long仍然表现最好，而且由于这些结果更接近于单独变量类型的影响，因此变化的幅度值得注意。

My results (desktop, Windows 7, Excel 2010):

我的结果（桌面、Windows 7、Excel 2010）：

Code used:

使用的代码：

Option Explicit

Sub VariableOlympics()
'Run this macro as many times as you'd like, with an activesheet ready for data
'in cells B2 to D6
Dim beginTIME As Double, trials As Long, i As Long, p As Long
Dim chosenWorksheet As Worksheet

    Set chosenWorksheet = ThisWorkbook.Sheets("TimeTrialInfo")

    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

    trials = 1000000000 ' 1,000,000,000 - not 10,000,000,000 as used by @PGSystemTester

    p = 0

    beginTIME = Now
    boomBYTE trials
    Finished p, Now - beginTIME, CDbl(trials), chosenWorksheet.Range("B2")
    p = p + 1

    beginTIME = Now
    boomINTEGER trials
    Finished p, Now - beginTIME, CDbl(trials), chosenWorksheet.Range("B2")
    p = p + 1


    beginTIME = Now
    boomLONG trials
    Finished p, Now - beginTIME, CDbl(trials), chosenWorksheet.Range("B2")
    p = p + 1


    beginTIME = Now
    boomDOUBLE trials
    Finished p, Now - beginTIME, CDbl(trials), chosenWorksheet.Range("B2")
    p = p + 1


    beginTIME = Now
    boomUNDECLARED trials
    Finished p, Now - beginTIME, CDbl(trials), chosenWorksheet.Range("B2")
    p = p + 1

    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    chosenWorksheet.Calculate

End Sub


Private Sub boomBYTE(numTrials As Long)
Dim a As Byte, b As Byte, c As Byte

Dim i As Long
For i = 1 To numTrials
    a = 1
    b = 1 + a
    c = 1 + b
    c = c + 1
Next i

End Sub


Private Sub boomINTEGER(numTrials As Long)
Dim a As Integer, b As Integer, c As Integer

Dim i As Long
For i = 1 To numTrials
    a = 1
    b = 1 + a
    c = 1 + b
    c = c + 1
Next i

End Sub


Private Sub boomLONG(numTrials As Long)
Dim a As Long, b As Long, c As Long

Dim i As Long
For i = 1 To numTrials
    a = 1
    b = 1 + a
    c = 1 + b
    c = c + 1
Next i

End Sub


Private Sub boomDOUBLE(numTrials As Long)
Dim a As Double, b As Double, c As Double

Dim i As Long
For i = 1 To numTrials
    a = 1
    b = 1 + a
    c = 1 + b
    c = c + 1
Next i

End Sub


Private Sub boomUNDECLARED(numTrials As Long)
Dim a As Variant, b As Variant, c As Variant

Dim i As Long
For i = 1 To numTrials
    a = 1
    b = 1 + a
    c = 1 + b
    c = c + 1
Next i

End Sub

Private Sub Finished(i As Long, timeUSED As Double, trials As Double, initialCell As Range)

    With initialCell.Offset(i, 0)
            .Value = trials
            .Offset(0, 1).Value = timeUSED
            .Offset(0, 2).FormulaR1C1 = "=ROUND(RC[-1]*3600*24,2)"
    End With

End Sub

回答by Ama

As others already mentioned, a Longmaytake twice as much space as an Integer. As others also already mentioned, the high capacity of current computers means you will see no difference in performance whatsoever, unless you are dealing with extra extra extra large amounts of data:

正如其他人已经提到的，Long可能需要两倍于Integer 的空间。正如其他人也已经提到的，当前计算机的高容量意味着您不会看到任何性能差异，除非您正在处理额外的额外大量数据：

Memory

记忆

Considering 1 million values, the difference between using Integers versus Longs would be of 2 bytes for each value, so that is 2 * 1 000 000 / 1,024 / 1024 = less than 2 MB of difference in your RAM, which is likely much less than 1% or even 0.1% of your RAM capacity.

考虑到100 万个值，使用 Integers 与 Longs 之间的差异将为每个值 2 个字节，因此 2 * 1 000 000 / 1,024 / 1024 =小于 2 MB 的 RAM 差异，这可能远小于您的 RAM 容量的 1% 甚至 0.1%。

Processing

加工

Considering the benchmark done by PGSystemTester's, you can see a difference of 811 - 745 = 66 seconds between Longs and Integers, when processing 10 billion batches of 4 operations each. Reduce the number to 1 million of operationsand we can expect 66 / 10 000 / 4 = less than 2ms of difference in execution time.

考虑到由 PGSystemTester 完成的基准测试，当处理 100 亿个批次，每个批次 4 个操作时，您可以看到 Longs 和 Integers 之间的差异为 811 - 745 = 66 秒。将操作数量减少到100 万次，我们可以预期 66 / 10 000 / 4 =执行时间的差异小于 2 毫秒。

---

---

I personally use Integers and Longs to help readability of my code, particularly in loops, where an Integer indicates the loop is expected to be small (less than 1000 iterations), whereas a Long tells me the loop is expected to be rather large (more than 1000).

我个人使用 Integers 和 Longs 来帮助我的代码可读性，特别是在循环中，其中 Integer 表示循环预计很小（少于 1000 次迭代），而 Long 表示循环预计相当大（更多超过 1000）。

Note this subjective threshold is way below the Integer upper limit, I use Longs just to make the distinction between my own definitions of small and large.

请注意，这个主观阈值远低于整数上限，我使用 Longs 只是为了区分我自己对小和大的定义。