有集合时为什么要在 VBA 中使用数组？

Why use arrays in VBA when there are collections?

提问by tyrex

many people use extensively arrays in Excel/VBA to store a list of data. However, there is the collection object which in my view is MUCH MUCH more convenient (mainly: don't need to re/define length of the list).

许多人在 Excel/VBA 中广泛使用数组来存储数据列表。但是，在我看来，有一个集合对象要方便得多（主要是：不需要重新/定义列表的长度）。

So, I am sincerely asking myself if I am missing something?Why do other people still use arrays to store a list of data? Is it simply a hangover of the past?

所以，我真诚地问自己，我是否遗漏了什么？为什么其他人仍然使用数组来存储数据列表？这只是过去的宿醉吗？

采纳答案by JMax

Several reasons to use arrays instead of collections (or dictionaries):

使用数组而不是集合（或字典）的几个原因：

• you can transfer easily array to range (and vice-versa) with Range("A1:B12") = MyArray
• collections can store only uniquekeys whereas arrays can store any value
• collections have to store a couple (key, value) whereas you can store whatever in an array

• 您可以轻松地将数组传输到范围（反之亦然） Range("A1:B12") = MyArray
• 集合只能存储唯一键，而数组可以存储任何值
• 集合必须存储一对（键，值），而您可以将任何内容存储在数组中

See Chip Pearson's articleabout arrays for a better understanding

请参阅Chip Pearson 的关于数组的文章以获得更好的理解

A better question would rather be why people would use collections over dictionaries (ok, collections are standard VBA whereas you have to importdictionaries)

一个更好的问题是为什么人们会在字典上使用集合（好吧，集合是标准的 VBA 而你必须导入字典）

回答by Nigel Heffernan

@CharlesWilliams answer is correct: looping through all the values of an array is faster than iterating a Collection or dictionary: so much so, that I always use the Keys() or Items() method of a dictionary when I need to do that - both methods return a vector array.

@CharlesWilliams 答案是正确的：遍历数组的所有值比迭代集合或字典更快：以至于我总是在需要时使用字典的 Keys() 或 Items() 方法 -这两种方法都返回一个向量数组。

A note: I use the Dictionary class far more than I use collections, the Exists() method is just too useful.

注意：我使用 Dictionary 类的次数远多于使用集合，Exists() 方法实在是太有用了。

There are, or course, drawbacks to collections and dictionaries. One of them is that arrays can be 2- or even 3-Dimensional - a much better data structure for tabulated data. You canstore arrays as members of a collection, but there's some downsides to that: one of them is that you might not be getting a reference to the item - unless you use arrItem = MyDictionary(strKey)you will almost certainly get a 'ByVal' copy of the array; that's bad if your data is dynamic, and subject to change by multiple processes. It's also slow: lots of allocation and deallocation.

当然，集合和字典也有缺点。其中之一是数组可以是 2 维或什至 3 维的——对于表格数据来说，这是一种更好的数据结构。您可以将数组存储为集合的成员，但这样做有一些缺点：其中之一是您可能无法获得对该项目的引用 - 除非您使用，否则您arrItem = MyDictionary(strKey)几乎肯定会获得该数组的“ByVal”副本；如果您的数据是动态的，并且会受到多个进程的更改，那将很糟糕。它也很慢：大量的分配和释放。

Worst of all, I don't quite trust VBA to deallocate the memory if I have a collection or dictionary with arrays (or objects!) as members: not on out-of-scope, not by Set objCollection = Nothing, not even by objDictionary.RemoveAll - it's difficult to prove that the problem exists with the limited testing toolkit available in the VBE, but I've seen enough memory leaks in applications that used arrays in dictionaries to know that you need to be cautious. That being said, I never use an array without an Erase command somewhere.

最糟糕的是，如果我有一个包含数组（或对象！）作为成员的集合或字典，我不太相信 VBA 会释放内存：不在范围外，不在 by Set objCollection = Nothing，甚至不在 objDictionary.RemoveAll -很难证明 VBE 中可用的有限测试工具包存在问题，但我在使用字典数组的应用程序中看到了足够多的内存泄漏，我知道您需要谨慎。话虽如此，我从不在某处使用没有 Erase 命令的数组。

@JMax has explained the other big plus for arrays: you can populate an array in a single 'hit' to the worksheet, and write back your work in a single 'hit.

@JMax 解释了数组的另一个重要优点：您可以在一次“命中”中将数组填充到工作表中，并在一次“命中”中写回您的工作。

You can, of course, get the best of both worlds by constructing an Indexed Array class: a 2-dimensional array with associated collection or dictionary objects storing some kind of row identifier as the keys, and the row ordinals as the data items.

当然，您可以通过构造索引数组类来获得两全其美：一个二维数组，带有关联的集合或字典对象，其中存储某种行标识符作为键，行序数作为数据项。

回答by codeghost

Collections that auto-resize are slower (theoretically speaking, different implementations will obviously have their own mileage). If you know you have a set number of entries and you only need to access them in a linear fashion then a traditional array is the correct approach.

自动调整大小的集合较慢（理论上，不同的实现显然会有自己的里程数）。如果您知道您有一定数量的条目并且您只需要以线性方式访问它们，那么传统数组是正确的方法。