List(Of ) like operation in macro

NOTE: I previously asked this question in Stack Overflow and got pointed here.

I have a macro that I am trying to get running a little more quickly. The intent of the macro is to merge multiple files into one. These files contain a unique ID for each item, some descriptive information about the item, and some measurement outcomes for each item. The measurement outcomes are what differ between the files.

In all the files, each item occupies a row, with the measurements in columns. No file lists all the items, but some items do appear in more than one file.

Currently, I iterate through the files and either use the data to update an existing row (for the items in a file that's already been processed), or create a new row if the item hasn't appeared in any previous file.

The place I'm losing a good chunk of time, especially on the later files, is finding the row for an item I've already added to the unified list, so that I can add the new measurement values to the appropriate column(s).

```
Public OldOName As String
Public INotFnd As String
Public FErr As String

Function MMFProcessor(sfol As String, ifn As String, x As Integer, os As Worksheet, NPU As String, Prods As String, sw As StatWin, PM As String, _
FlgMeas() As String, m As Long, MKO As String, OName As String, Optional Ambetter As Boolean = False) As Variant()
'
Dim src As Workbook
Set src = Workbooks.Open(sfol & "\" & ifn & Right(Year(Now() - 25), 2) & "_.xlsx")
Dim mks As String
Dim mgc As String
Dim sm As String
Dim FMC As Boolean
FMC = False 'want to only check that the filled length of FlgMeas & FMCol match once
Dim ret As Boolean
ret = True
Dim RArr(3) As Variant
Dim fnd As Boolean
Dim y As Integer

If sw.Detailed_Log Then
Call Update("Starting MMFProcessor. Inputs: sfol= " & sfol & "; ifn= '" & ifn & "'; x= '" & x & "'; os= '" & os.Name & "'; m= '" & m & "'; MKO= '" & MKO & "'; OName= '" & OName & "'", 0, sw)
End If

'Make sure we have as many values in the FlgMeas array's column values as

You can optimize your code by reading all the cells into a 2D array and then performing your analysis. Important other notes are UBound(yourarray,1) -> rows UBound(yourarray,2) -> columns

https://blogs.office.com/2009/03/12/excel-vba-performance-coding-best-practices/

I did a test on some code that used .cells or .range in a loop versus range.value and putting it into an array... 540k rows of data and saw my code take 7 seconds versus 113 seconds for the loop.

StackExchange Code Review Q#132946, answer score: 4