VBA: multidimensional list, array, collection or dictionary performance

Question

I am currently writing code to combine two sheets containing different versions of data. In this regard, I first want to sort both using the key column, combine them and subsequently mark the changes between versions on the output sheet.

Since the data is already several 10000 rows and may exceed the limit of rows on the excel sheet, I want these calculations to be performed outside the worksheet. Also it should work better.

I am currently thinking of Quicksort first and second data, and then comparing the data for each key / row. Using the comparison result for subsequent formatting of the cells, respectively.

Question

I would just like to know if I should use:

List OR Array OR Collection OR Dictionary
OF Lists OR arrays OR collections or dictionaries

I could no longer determine the differences in coding and performance between these 16 features. I am currently implementing the Array OF Arrays approach, constantly wondering if this really makes sense?

Thank you in advance for your contribution and wisdom!

Answer 1

Some time ago I had the same problem with a client macro. In addition to a really large number of lines (more than 50,000 and growing), he had a problem of being extremely slow from a certain line number (about 5000) when the “standard approach” was applied, that is, the inputs for calculations on each line were read from the same sheet (a couple of lines above); this reading and writing process made the process slower and slower (apparently, Excel starts with line 1, and the bottom line takes longer than longer). I improved this situation based on two different solutions: firstly, setting the maximum number of lines on the worksheet, I once reached, a new worksheet was created, and reading / writing continued there (from the first lines). Another change was moving the read / write in Excel to read from temporary .txt files and write to Excel (all lines were read from the very beginning to fill the files). These two modifications significantly improved speed (from half an hour to a couple of minutes). As for your question, I would not rely too much on arrays with macros (although I'm not sure how much information each of these 10,000 lines contains); but I think this is a personal decision. I do not really like collections because they are less efficient than arrays; and the same for dictionaries.

I hope this “short” comment is helpful.