VBA:多维列表、数组、集合或字典的性能 [英] VBA: Performance of multidimensional List, Array, Collection or Dictionary

问题描述

我目前正在编写代码来组合包含不同版本数据的两个工作表.在此，我首先想通过 Key Column 对两者进行排序，组合它们，然后在输出工作表中标记版本之间的更改.

I'm currently writing code to combine two worksheets containing different versions of data. Hereby I first want to sort both via a Key Column, combine 'em and subsequently mark changes between the versions in the output worksheet.

由于数据已经达到了 10000 行，并且有一天可能会超过 excel 的每工作表行数限制，我希望这些计算在工作表之外运行.它也应该表现得更好.

As the data amounts to already several 10000 lines and might some day exceed the lines-per-worksheet limit of excel, I want these calculations to run outside of a worksheet. Also it should perform better.

目前我正在考虑对第一个和第二个数据进行快速排序，然后比较每个键/行的数据集.使用比较结果随后相应地格式化单元格.

Currently I'm thinking of a Quicksort of first and second data and then comparing the data sets per key/line. Using the result of the comparison to subsequently format the cells accordingly.

我只是想知道我是否应该使用:

I'd just love to know, whether I should use:

列表 OR 数组 OR 集合 OR 字典
OF 列表 OR 数组 OR 集合 OR 字典

List OR Array OR Collection OR Dictionary
OF Lists OR Arrays OR Collections OR Dictionaries

到目前为止，我无法确定这 16 种可能性之间在可编码性和性能方面的差异.目前我正在实施 Array OF Arrays 方法，一直在想这是否有意义?

I have as of now been unable to determine the differences in codability and performance between this 16 possibilities. Currently I'm implementing an Array OF Arrays approach, constantly wondering whether this makes sense at all?

提前致谢，感谢您的意见和智慧！

Thanks in advance, appreciate your input and wisdom!

推荐答案

前段时间，我遇到了一个客户端宏的问题.除了非常大的行数(超过 50000 并且还在增长)之外，当采用标准方法"时，它存在从某个行数(大约 5000)开始非常缓慢的问题，即每个计算的输入行是从同一个工作表中读取的(上面几行)；这种读写过程使这个过程变得越来越慢(显然，Excel 从第 1 行开始，越低的行，到达那里所需的时间越长).我依靠两种不同的解决方案改善了这种情况:首先，设置每个工作表的最大行数，一旦达到，就会创建一个新工作表并在那里继续读/写(从第一行开始).另一个变化是将 Excel 中的读取/写入移动到从临时 .txt 文件读取并写入 Excel(在开始时读取所有行以填充文件).这两个修改大大提高了速度(从半小时到几分钟).关于你的问题，我不会太依赖带有宏的数组(尽管我不确定这 10000 行中的每一行包含多少信息)；但我想这是个人决定.我不太喜欢集合，因为它比数组效率低；字典也是一样.

Some time ago, I had the same problem with the macro of a client. Additionally to the really big number of rows (over 50000 and growing), it had the problem of being tremendously slow from certain row number (around 5000) when a "standard approach" was taken, that is, the inputs for the calculations on each row were read from the same worksheet (a couple of rows above); this process of reading and writing was what made the process slower and slower (apparently, Excel starts from row 1 and the lower is the row, the longer it takes to reach there). I improved this situation by relying on two different solutions: firstly, setting a maximum number of rows per worksheet, once reached, a new worksheet was created and the reading/writing continued there (from the first rows). The other change was moving the reading/writing in Excel to reading from temporary .txt files and writing to Excel (all the lines were read right at the start to populate the files). These two modifications improved the speed a lot (from half an hour to a couple of minutes). Regarding your question, I wouldn't rely too much on arrays with a macro (although I am not sure about how much information contains each of these 10000 lines); but I guess that this is a personal decision. I don't like collections too much because of being less efficient than arrays; and same thing for dictionaries.

我希望这个简短"的评论会有所帮助.

I hope that this "short" comment will be of any help.

这篇关于VBA:多维列表、数组、集合或字典的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！