ReDim Preserve and `Transpose are not free. I use this pattern when I need to filter an array:
Declare an 1D array (Indices) with the same number of rows as the 2D array
When a match is found add the matches index to the Indices array and increment the counter
If there is matching data, declare a 2d array (Results) with the same dimensions of the Input ...
Based on my comment (and near terminal Covid-19 boredom) here is a starter for 10 for a class called 'FlexArray'. The code compiles cleanly and doesn't generate any untoward Rubberduck code inspections but is otherwise untested.
Dim myArray as FlexArray
set myArray = FlexArray.Make(inputarray)
Note the Rubberduck annotations for PredeclaredId and ...
Avoid the multiple DLookUps with eachTreeView.Nodes.Add call for every row of recordset! At the very least store your DLookUp value once and apply it to treeview. This cuts down the repetitive 4 calculations down to 2 for every row.
But since DLookUp essentially is a query anyway, simply integrate a JOIN to Master object in SQL queries of OpenRecordset calls....
The question is how much speed improvement you expect. What I would do:
I'd analyze RandNorm for this is a user function that you call 10M times that really influences runtime. Considering that rnd provides pseudo-random numbers it might even be unappropriate but it's your choice.
Minor improvements can be achieved by replacing
For i = 1 To N