vba 整数与长混淆

Integer Vs Long Confusion

提问by Siddharth Rout

I have seen many believe in the following

我看到很多人相信以下内容

VBA converts all integer values to type Long

VBA 将所有整数值转换为 Long 类型

In fact, even the MSDN articlesays

其实连MSDN的文章都说

“In recent versions, however, VBA converts all integer values to type Long, even if they're declared as type Integer.”

“然而，在最近的版本中，VBA 将所有整数值转换为 Long 类型，即使它们被声明为 Integer 类型。”

How is this possible? Consider this simple example.

这怎么可能？考虑这个简单的例子。

Sub Sample()
    Dim I As Integer
    I = 123456789
End Sub

If VBAconverts all Integervalues to type Longeven if they're declared as type Integer, then the above should never give you the Overflowerror!

如果VBA将所有Integer值转换为类型，Long即使它们被声明为 Integer 类型，那么上面的内容永远不会给你Overflow错误！

What am I missing here? Or should I take it that the statement is incorrect and pay serious heed to that the link says in the beginning

我在这里缺少什么？或者我应该认为该陈述是不正确的，并认真注意链接在开头所说的

采纳答案by RubberDuck

An integer declared as an Integeris still type checked as an Integer. The msdn documentation is referencing how the variable is stored internally. On a 32 bit system, an Integer will be stored in 32 BITSnot Bytes, while on a 16 bit system the value is stored in a 16 BIT space or register, it would have been stored in 16. Hence the maximum size.

声明为 an 的整数Integer仍作为 进行类型检查Integer。msdn 文档参考了变量在内部的存储方式。在 32 位系统上， Integer 将存储在 32 BITS而不是Bytes，而在 16 位系统上，该值存储在 16 BIT 空间或寄存器中，它将存储在 16 中。因此最大大小。

There is no type conversion going on as far as VBA is concerned. An int is an int and a long is a long, even though they now take up just as much space.

就 VBA 而言，没有进行类型转换。int 是 int 而 long 是 long，即使它们现在占用的空间一样多。

回答by Bathsheba

I've spent a lot of time working in the VBA environment and have every reason to believe that the claim in this article is at best, misleading.

我花了很多时间在 VBA 环境中工作，并且有充分的理由相信本文中的声明充其量只是具有误导性。

I've never come across a situation where an automatic unexpected conversion is made. Of course, assignment by valueto a larger type (such as a Doubleor Long) would be implicit.

我从未遇到过自动意外转换的情况。当然，按值分配给更大的类型（例如 aDouble或Long）将是隐式的。

One specific case where automatic conversion would be a breaking change would be an assignment to a Varianttype. Without a conversion, the type would be VT_I2, with conversion VT_I4.

自动转换将是重大更改的一种特定情况是对Variant类型的分配。如果没有转换，类型将为 VT_I2，转换为 VT_I4。

Passing an Integer type ByRefto a function expecting a Longemits a type mismatch in Office 2013.

在 Office 2013 中，将整数类型传递ByRef给期望 a 的函数会Long发出类型不匹配。

I suspect they are referring to the internal storage of the Integer: it's very likely that they are not aligned on 16 bit words in memory (cf. a shortstructure member in C / C++). They are probably talking about that.

我怀疑他们指的是内部存储Integer：它们很可能没有在内存中的 16 位字上对齐（参见shortC/C++ 中的结构成员）。他们可能正在谈论那个。

回答by enderland

“In recent versions, however, VBA converts all integer values to type Long, even if they're declared as type Integer.”

“然而，在最近的版本中，VBA 将所有整数值转换为 Long 类型，即使它们被声明为 Integer 类型。”

I don't believe that documentation. Consider the following simple example (run in Excel 2010):

我不相信那个文件。考虑以下简单示例（在 Excel 2010 中运行）：

Sub checkIntegerVsLong()

    'Check the total memory allocation for an array
    Dim bits As Integer 'or long? :)
    Dim arrInteger() As Integer
    ReDim arrInteger(1 To 5)
    arrInteger(1) = 12
    arrInteger(2) = 456
    'get total memory allocation for integer in array
    bits = VarPtr(arrInteger(2)) - VarPtr(arrInteger(1))
    Debug.Print "For integer: " & bits & " bits and " & bits * 8 & " bytes."


    Dim arrLong() As Long
    ReDim arrLong(1 To 5)
    arrLong(1) = 12
    arrLong(2) = 456

    'get memory allocation for long
    bits = VarPtr(arrLong(2)) - VarPtr(arrLong(1))
    Debug.Print "For long: " & bits & " bits and " & bits * 8 & " bytes."

End Sub

This prints:

这打印：

For integer: 2 bits and 16 bytes.

For long: 4 bits and 32 bytes.

对于整数：2 位和 16 字节。

长：4 位和 32 字节。

You can also test this on individual variables using the following:

您还可以使用以下方法对单个变量进行测试：

Sub testIndividualValues()

    Dim j As Long
    Dim i As Integer
    Dim bits As Integer

    bits = LenB(i)
    Debug.Print "Length of integer: " & bits & " bits and " & bits * 8 & " bytes."
    bits = LenB(j)
    Debug.Print "Length of long: " & bits & " bits and " & bits * 8 & " bytes."



End Sub

which prints

哪个打印

Length of integer: 2 bits and 16 bytes.

Length of long: 4 bits and 32 bytes.

整数长度：2位16字节。

long 的长度：4 位 32 字节。

Last, you can use a type comparison here:

最后，您可以在此处使用类型比较：

Public Type myIntegerType
    a As Integer
    b As Integer
End Type
Public Type myLongType
    a As Long
    b As Long
End Type

Public Sub testWithTypes()
    Dim testInt As myIntegerType
    Dim testLong As myLongType
    Dim bits As Integer

    bits = VarPtr(testInt.b) - VarPtr(testInt.a)
    Debug.Print "For integer in types: " & bits & " bits and " & bits * 8 & " bytes."

    bits = VarPtr(testLong.b) - VarPtr(testLong.a)
    Debug.Print "For long in types: " & bits & " bits and " & bits * 8 & " bytes."

End Sub

which alsoprints:

这也打印：

For integer in types: 2 bits and 16 bytes.

For long in types: 4 bits and 32 bytes.

对于整数类型：2 位和 16 字节。

对于 long 类型：4 位和 32 字节。

This is pretty compelling evidence to me that VBA actually doestreat Integerand Longdifferently.

这对我来说非常令人信服的证据表明VBA其实做治疗Integer和Long不同。

If VBA silently converted behind the scenes, you would expect those to return the same number of bits/bytes for each of the pointer allocations locations. But in the first case, with Integer, it is only allocating 16 bits, while for Long variables, it allocates 32 bits.

如果 VBA 在幕后静默转换，您会期望它们为每个指针分配位置返回相同数量的位/字节。但是在第一种情况下，对于 Integer，它只分配 16 位，而对于 Long 变量，它分配 32 位。

So what?

所以呢？

So to your question of

所以对于你的问题

If VBA converts all Integer values to type Long even if they're declared as type Integer, then the above should never give you the Overflow error!

如果 VBA 将所有 Integer 值转换为 Long 类型，即使它们被声明为 Integer 类型，那么上面的代码永远不会给你溢出错误！

It makes complete sense that you would get an Overflow error, as VBA has not actually allocated the memory for a Longto the Integerdeclaration.

你会得到一个溢出错误是完全有道理的，因为 VBA 实际上没有为 a 分配内存Long给Integer声明。

I would be curious too if this returns the same on all versions of Office. I can only test on Office 2010 on 64 bit Windows 7.

如果这在所有版本的 Office 上返回相同，我也会很好奇。我只能在 64 位 Windows 7 上的 Office 2010 上进行测试。

回答by Pradeep Kumar

The conversion is only for memory optimization, not for user code. For the programmer, there is practically no change since the min/max limits of datatypes remain the same.

转换仅用于内存优化，而不用于用户代码。对于程序员来说，实际上没有变化，因为数据类型的最小/最大限制保持不变。

If you take that para as a whole, you will realize that that statement is in context of performance only, and not otherwise. This is because the default size of numbers is Int32 or Int64 (depending on whether it is 32-bit or 64-bit system). The processor can process upto that big number in one go. If you declare a smaller unit than this, the compiler has to downsize it, and that needs more efforts than simply using the default type. And the processor has really no gain either. So even though you declare your variable as Integer, the compiler allocates it a Longmemory, because it knows that it has to do more work without any gain.

如果你把那段作为一个整体，你会意识到那句话只是在表演的背景下，而不是在其他情况下。这是因为数字的默认大小是 Int32 或 Int64（取决于它是 32 位还是 64 位系统）。处理器可以一次性处理这个大数字。如果你声明一个比这个更小的单元，编译器必须缩小它的大小，这比简单地使用默认类型需要更多的努力。处理器也确实没有任何收益。因此，即使您将变量声明为Integer，编译器也会为它分配一块Long内存，因为它知道它必须在没有任何收益的情况下做更多的工作。

As a VBA programmer what is of significance for you is – Declare your variables as LONG instead of INTEGER even if you want to store small numbers in them.

作为 VBA 程序员，对您而言重要的是—— Declare your variables as LONG instead of INTEGER even if you want to store small numbers in them.

回答by Erik A

As far as my testing goes, A VBA integer still takes two bytes (Tested on Access 2016, build 8201).

就我的测试而言，VBA 整数仍然需要两个字节（在 Access 2016 上测试，版本 8201）。

Implicit casting to a long (and back, if it's a write operation) occurs for operations, not for storage, as far as I can find. E.g. if I do myInt + 1, myIntgets cast to a long, then one gets added to that long, and then the result is cast back to an int, resulting in a performance loss compared to just using a Long. So while it costs less memory to use an integer, all operations will suffer in performance.

据我所知，隐式转换为 long （如果是写操作，则返回）发生在操作中，而不是存储中。例如，如果我这样做myInt + 1，myInt被强制转换为一个 long，然后一个被添加到那个 long，然后结果被强制转换回一个 int，与仅使用一个相比导致性能损失Long。因此，虽然使用整数会消耗更少的内存，但所有操作都会影响性能。

As Mathieu Guindon noted under Enderland/Elysian Fields's answer, testing VBA's storage with VBA functions can't prove anything, so let's go more low-level and directly view what's stored in memory, and manipulate that memory.

正如 Mathieu Guindon 在 Enderland/Elysian Fields 的回答中指出的那样，使用 VBA 函数测试 VBA 的存储不能证明任何事情，所以让我们更底层，直接查看存储在内存中的内容，并操作该内存。

First, declarations:

一、声明：

Declare PtrSafe Sub CopyMemory Lib "Kernel32.dll" Alias "RtlMoveMemory" (ByVal Destination As LongPtr, ByVal Source As LongPtr, ByVal Length As Long)

Public Function ToBits(b As Byte) As String
    Dim i As Integer
    For i = 7 To 0 Step -1
        ToBits = ToBits & IIf((b And 2 ^ i) = (2 ^ i), 1, 0)
    Next
End Function

Now, I'm going to prove two things:

现在，我要证明两件事：

1. The memory VarPtr points to contains 16-bits integers
2. Manipulating this memory manipulates the integers VBA uses, even if you manipulate it outside of VBA

1. VarPtr 指向的内存包含 16 位整数
2. 操作此内存会操作 VBA 使用的整数，即使您在 VBA 之外操作它

The code:

编码：

Dim i(0 To 1) As Integer
'Using negatives to prove things aren't longs, because of the sign bit
i(0) = -2 ^ 15 + (2 ^ 0) '10000000 00000001
i(1) = -2 ^ 15 + (2 ^ 1) '10000000 00000010
Dim bytes(0 To 3) As Byte
CopyMemory VarPtr(bytes(0)), VarPtr(i(0)), 4
Dim l As Long
For l = 3 To 0 Step -1
    Debug.Print ToBits(bytes(l)) & " ";
    'Prints 10000000 00000010 10000000 00000001
Next
'Now, let's write something back
bytes(0) = &HFF '0xFFFF = signed -1
bytes(1) = &HFF
CopyMemory VarPtr(i(0)), VarPtr(bytes(0)), 2
Debug.Print i(0) '-1

So, we can be certain that VBA indeed both writes 2-byte integers to the memory, and reads them back from the memory, when we're declaring things as an integer.

因此，当我们将事物声明为整数时，我们可以确定 VBA 确实将 2 字节整数写入内存，并从内存中读取它们。

回答by this

So far, I have not seen anyone mention the problem of byte alignment. In order to manipulate the numbers, it needs to be loaded into register and as a rule, a register cannot contain more than one variable. I believe that registers also need to be cleared from the previous instruction, so to ensure the variable is loaded correctly it needs to be re-aligned, which may also involve sign extending or zeroing out the register.

到目前为止，我还没有看到有人提到字节对齐的问题。为了操作数字，需要将其加载到寄存器中，并且通常一个寄存器不能包含多个变量。我相信寄存器也需要从前一条指令中清除，因此为了确保正确加载变量，它需要重新对齐，这也可能涉及符号扩展或将寄存器清零。

You also can observe the byte alignment using VBA code:

您还可以使用 VBA 代码观察字节对齐：

Public Type x
    a As Integer
    b As Integer
    l As Long
End Type

Public Type y
    a As Integer
    l As Long
    b As Integer
End Type

Public Sub test()
    Dim x As x
    Dim y As y

    Debug.Print LenB(x)
    Debug.Print LenB(x.a), LenB(x.b), LenB(x.l)

    Debug.Print LenB(y)
    Debug.Print LenB(y.a), LenB(y.l), LenB(y.b)
End Sub

Even though the UDT xand ycontains same number of members and each member are same data type; with the only difference being the ordering of the members, LenB()will give different results; on a 32-bit platform, xconsumes only 8 bytes whereas ywill need 12 bytes. The high word between the x.aand x.land after the x.bis simply ignored.

即使 UDTx和y包含相同数量的成员并且每个成员都是相同的数据类型；唯一的区别是成员的排序，LenB()将给出不同的结果；在 32 位平台上，x仅消耗 8 个字节，而y需要 12 个字节。x.a和x.l和之后之间的高字x.b被简单地忽略。

The other point is that the problem is not unique to VBA. For example, C++ has the same considerations as illustrated hereand here. So this is actually much lower level and thus why you can't "see" the sign-extending/zero-extending behavior when loading the variables into registers for performing the operation. To see that, you need the disassembly.

另一点是该问题并非 VBA 独有。例如，C++ 具有与此处和此处所示相同的注意事项。因此，这实际上是低得多的级别，因此在将变量加载到寄存器中以执行操作时，您无法“看到”符号扩展/零扩展行为。要看到这一点，您需要进行拆卸。

回答by Greedo

Looking at the other answers and the MSDN documentation, I think the phrase "Internally stored" is imprecise and that's what's confusing.

查看其他答案和 MSDN 文档，我认为“内部存储”一词不准确，这令人困惑。

Tl;DR

Tl; DR

Integers aren't "stored internally" as Longs, that is they do not require the same amount of memory to save their values as a Long does. Rather they are "used internally" as Longs, meaning their value is temporarily stored in a Long variable whenever it is accessed (e.g incrementing a loop counter) before being copied back, and generally speaking, an array of Integers will require half as much memory as an array of Longs.

整数不像 Long 那样“在内部存储”，也就是说，它们不需要与 Long 相同数量的内存来保存它们的值。相反，它们在“内部”作为 Longs 使用，这意味着它们的值在被复制回之前，无论何时被访问（例如增加循环计数器）都会临时存储在 Long 变量中，一般来说，整数数组将需要一半的内存作为一个多头数组。

---

---

@enderland's answershows that the memory layout of Integers, Integer Arrays and UDTs composed of Integers such as a DWORDall conform to the idea that the value contained in a variable declared as an integer takes up 2 bytes of memory.

@enderland 的回答表明，整数、整数数组和由整数组成的 UDT 的内存布局（例如 DWORD）都符合以下想法：声明为整数的变量中包含的值占用 2 个字节的内存。

This is from the viewpoint of VBA code, meaning it's possible to assume that

这是从 VBA 代码的角度来看的，这意味着可以假设

1. The memory locations and sizes given by VarPtrand LenBrespectively are incorrect(lies) in the case of Integers to avoid breaking existing code when the switch from 16 to 32 bit systems took place
2. There is some sort of abstraction layerwhich means the memory appears as one thing but is actually another

1. VarPtr和LenB分别给出的内存位置和大小在整数的情况下是不正确的（谎言），以避免在从 16 位系统切换到 32 位系统时破坏现有代码
2. 有某种抽象层，这意味着内存看起来是一回事，但实际上是另一回事

---

---

We can rule these both out.

我们可以排除这两种情况。

It is possible to use the CopyMemory API with an address given by the VarPtr and a width given by LenB to overwrite values in an array directly. The API is not under VBA's control, and all it does is directly write bits to memory. The fact that this is possible at all means that VarPtrmust point to an area in memory where LenBbytes are used to store the value of that Integer; no other way around it, 2 bytes is the amount of space used to encode an Integer's value.

可以使用 CopyMemory API 和 VarPtr 给定的地址和 LenB 给定的宽度直接覆盖数组中的值。API 不受 VBA 的控制，它所做的只是直接将位写入内存。事实上，这是可能的意味着VarPtr必须指向内存中的一个区域，其中LenB字节用于存储该整数的值；没有其他办法，2 个字节是用于编码整数值的空间量。

The abstraction layer could still be true though; VBA could hold one array of 2 byte spaced memory (SAFEARRAYS are all consecutive memory, that's why CopyMemory can write 2 entries at once) where VarPtr points to. Meanwhile a separate 4 byte spaced block of memory shadows the 2 byte spaced block, staying constantly in sync so that Integers can be stored as Longs. Sounds weird but could happen right?

尽管如此，抽象层仍然可能是真实的；VBA 可以保存 VarPtr 指向的一组 2 字节间隔内存（SAFEARRAYS 都是连续内存，这就是 CopyMemory 可以一次写入 2 个条目的原因）。同时，一个单独的 4 字节间隔内存块遮蔽了 2 字节间隔块，保持同步，以便整数可以存储为长整数。听起来很奇怪，但可能发生吧？

---

---

It doesn't, and we can see this by looking at the process memory in Task Manager:

它没有，我们可以通过查看任务管理器中的进程内存来看到这一点：

Idle, Excel uses 155,860KBof memory (155,860 * 1024 bytes)

空闲，Excel 使用155,860KB的内存（155,860 * 1024 字节）

Run this:

运行这个：

Sub testLongs()
    Dim longs(500, 500, 500) As Long
    Stop
End Sub

...and it spikes to 647,288KB. Taking the difference and dividing by the number of array elements gives ~4.03 bytes per Long. The same test for Integers:

...它飙升至647,288KB. 取差值并除以数组元素的数量给出每个 Long 约 4.03 个字节。对整数的相同测试：

Sub t()
    Dim ints(500, 500, 500) As Integer
    Stop
End Sub

...gives 401,548KB, or ~2.01 bytes per Integer

...给出401,548KB, 或每个整数约 2.01 个字节

There will be a slight variation in the idle memory usage so the exact numbers don't matter, but clearly the Integer array is using ~ half the memory of the Long array

空闲内存使用量会略有变化，因此确切数字无关紧要，但很明显 Integer 数组使用的是 Long 数组的内存的一半

---

---

So my interpretation of the MSDN article is the following:

所以我对MSDN文章的解读如下：

Memory-wise, Integers are really stored as 2 byte values, not as 4 byte Longs. There is no abstraction or trickery with pointers to hide this from us.

在内存方面，整数实际上存储为 2 字节值，而不是 4 字节长整数。没有抽象或诡计用指针来向我们隐藏这一点。

Rather the article tells us that when Integers are used in operations (multiplication/addition etc.) their values are fist copied to the lower half of an int32/ VBA Long, the calculation takes place in an optimized 32-bit friendly way, and then the result is copied back to Integer and overflow errors are raised as necessary. For Longs there is no need to copy forward and back (hence the recommendation).

更确切地说，本文告诉我们，当整数在操作用于（乘法/加法等）它们的值被拳头复制到的下半部分int32/VBA Long时，计算需要在优化的32位友好的方式到位，然后其结果是复制回 Integer 并根据需要引发溢出错误。对于多头，无需向前和向后复制（因此推荐）。