动态调整数组大小而不在 VBA 中重新调整它？

Dynamically size an array without Redimming it in VBA?

提问by toolshed

I'm currently developing a reporting suite that will interface with Microsoft Excel. The languages that I've used primarily are Java, Perl, and Python. I would consider myself a novice programmer. The only reason that I have chosen to use VBA is because it was suggested as the best language to use if programming a Microsoft Office extension.

我目前正在开发一个将与 Microsoft Excel 交互的报告套件。我主要使用的语言是 Java、Perl 和 Python。我会认为自己是一个新手程序员。我选择使用 VBA 的唯一原因是因为它被建议作为编写 Microsoft Office 扩展程序时使用的最佳语言。

Those things considered, I absolutely HATE the way that arrays perform in VBA. Specifically, having to define the size of an array instead of allowing it to grow dynamically.

考虑到这些事情，我绝对讨厌数组在 VBA 中的表现方式。具体来说，必须定义数组的大小而不是允许它动态增长。

How do I create dynamic arrays without having to Redim them? I'm trying to avoid this as it is clearly resource intensive.

如何创建动态数组而不必重新编辑它们？我试图避免这种情况，因为它显然是资源密集型的。

The only solution I have found is if you are using a single loop, whereby you can set the number of elements to equal the upper bound of the loop (assuming a count 1 iteration).

我发现的唯一解决方案是，如果您使用单个循环，您可以将元素数设置为等于循环的上限（假设计数为 1 次迭代）。

What do I do when I'm not using a single loop and this is not an option?

当我不使用单个循环并且这不是一个选项时，我该怎么办？

Is Redimming inevitable?

Redimming 是不可避免的吗？

回答by psubsee2003

This is not an "array" but VBA does have a Dictionaryobject (this linkis useful too) that might suit your needs. You need to add a reference to Microsoft Scripting Runtime, but then you can create an untyped keyed dictionary (actually the Itemproperty is a Variant), which can grow dynamically. The links have more complete examples, but the usage is basically:

这不是一个“数组”，但 VBA 确实有一个可能适合您需要的Dictionary对象（此链接也很有用）。您需要添加对 的引用Microsoft Scripting Runtime，但随后您可以创建一个可以动态增长的无类型键控字典（实际上Item属性是 a Variant）。链接有更完整的例子，但用法基本上是：

Set dict = New Dictionary

'Example for adding item to Dictionary
For i = 1 to 100
    dict.Add Key:=i, Item:=someData
Next

'Example for retrieving item from dictionary by Key
For i = 1 to 100
    Debug.Print dict(i)
Next

There is also a Collectionobject as well, which offers similar functionality and similar usage but does not require the added reference to the Microsoft Scripting Runtime, however Dictionaryprovides some more useful methods like dict.Exists()to check if a specific key exists. There are also .Keysand .Itemsproperties in Dictionarythat would allow you to extract all of the Items or Keys as a Variant-typed array

还有一个Collection对象，它提供类似的功能和类似的用法，但不需要添加对 的引用Microsoft Scripting Runtime，但是Dictionary提供了一些更有用的方法，例如dict.Exists()检查特定键是否存在。也有.Keys和.Items在性能Dictionary，将允许你提取所有的项目或钥匙作为Variant-typed阵列

Even though you explicitly stated that you do not want to redim the array, the only other alternative to adjust the size of an array after creation is to use the Preservemodifier on the redim statement. This will allow some you flexibility in changing the array size after creation. The reason I said "some flexibility" is you can only change the last dimension of the array.

即使您明确声明不想重新调整数组，在创建后调整数组大小的唯一其他替代方法是Preserve在 redim 语句中使用修饰符。这将允许您在创建后灵活更改数组大小。我说“一些灵活性”的原因是您只能更改数组的最后一个维度。

Dim myarray(2, 2)
Redim Preserve myarray(2, 4) 'this works
Redim Preserve myarray(3, 4) 'this is not allowed

As you mentioned this is resource intensive as you really are not changing the size of the array, you are actually just creating a new array and copying the contents of the old array into it.

正如您所提到的，这是资源密集型的，因为您实际上并没有更改数组的大小，您实际上只是创建了一个新数组并将旧数组的内容复制到其中。

The last option you have is to switch from VBA to .NET. Assuming you have an appropriate version of Visual Studioat your disposal, you'll have access to VSTOinstead of the VBA editor that comes with Office, and then you'll be able to develop CLR-compliant add-ins for Excel and other Office products. You would then have access to all of the .NET collections. Again, these aren't "arrays" since .NET arrays have the same limitation (they can't be resized), but you have dynamically sized collections that might suit your needs.

您拥有的最后一个选项是从 VBA 切换到 .NET。假设您有合适版本的 Visual Studio可供您使用，您将可以访问VSTO而不是 Office 附带的 VBA 编辑器，然后您将能够为 Excel 和其他 Office 开发符合 CLR 的加载项产品。然后您就可以访问所有 .NET 集合。同样，这些不是“数组”，因为 .NET 数组具有相同的限制（它们不能调整大小），但是您有可能适合您需要的动态大小的集合。

回答by M.Doerner

This is a rather old question but I would like to add some context to the answers already given.

这是一个相当古老的问题，但我想在已经给出的答案中添加一些上下文。

VBA does not directly provide a type for dynamic arrays and redimming is necessary to change the size. This seems to be inefficient as it potentially entails copying the contents of the array to a new location in memory. However, this is exactly what dynamic arrays do under the hood: they typically double size when they are full and shrink to half the size when they drop below quarter the size. This guarantees an average runtime for adding and removing items of O(1).

VBA 不直接为动态数组提供类型，需要重新调整大小才能更改大小。这似乎效率低下，因为它可能需要将数组的内容复制到内存中的新位置。然而，这正是动态数组在幕后所做的：它们通常在满时加倍大小，当它们低于四分之一时缩小到一半。这保证了添加和删除 O(1) 项的平均运行时间。

So, if you want to use arrays in VBA because of the high speed of index lookups and you also want dynamic size, you can simply emulate this behavior.

因此，如果您想在 VBA 中使用数组，因为索引查找速度快，并且您还需要动态大小，您可以简单地模拟这种行为。

Edit: One small correction. You get O(1) average performance for adding to or removing from the end of the array. Adding at random positions is always O(N) for arrays. (You have to shift everything.) If you want to add at random positions, you should better use a linked list, which has bad index lookup performance (enumeration has good performance, however), a skip list, which is more complicated but has good lookup performance sacrificing insert performance, or a hash set, for which key lookups are very fast but which has larger space requirements.

编辑：一个小的更正。在数组末尾添加或删除的平均性能为 O(1)。对于数组，在随机位置添加总是 O(N)。（你必须移动一切。）如果你想在随机位置添加，你最好使用一个链表，它的索引查找性能很差（但是枚举性能很好），一个跳过列表，它更复杂但有良好的查找性能会牺牲插入性能，或散列集，对于其键查找非常快但具有更大的空间要求。