VBA- Create an Array of Multi-dimensional Arrays

Question

To increase the performance of my VBA code for large spreadsheets, I'm converting the sheets into 2D Arrays of Strings that the program can refer to instead of leaving open and looping through the memory intensive spreadsheets. The sheets dramatically slow down my computer, and at present the macro is slower than doing it by hand (I've removed many unnecessary columns and removed all formatting from these sheets, and no formulas seem to extend past the used range-- they're also stored as .xlsb's. They're already about one-half to two-thirds the size of the originals, so I don't think there's anything else to be done to optimize them).

Note that I'm developing in outlook, but relying heavily on data from excel sheets- the use case is an email auto-responder that searches the sheets for an ID supplied in an email, and then replies to that email with a phone number from the sheets. I have the proper references in place, and the program opens the sheets fine, just (painfully) slowly.

I'd like to use nested For loops to load the spreadsheets into the arrays programmatically, and then store those arrays in another array so they can in turn be looped through. In my research, I've found code to make jagged arrays in VBA (How do I set up a "jagged array" in VBA?), and 2D Arrays in VBA (Multi-dimensional array in VBA for Microsoft Word on Mac) but not arrays of 2D Arrays.

This is the code I wrote to make the 2D arrays- the Dim and ReDim lines throw syntax errors.

For k = LBound(sheetsArr) To UBound(sheetsArr)
        Dim myWbksArr(k)() As String
        ReDim myWbksArr(k)(sheetsArr(k).UsedRange.Rows.Count, sheetsArr(k).UsedRange.Columns.Count)
Next k

Where sheetsArr is an array of Worksheets into which I copied the sheets I'm referencing to avoid another for loop to iterate through Workbooks as well, using

Dim sheetsArr() As Worksheet, runningIndex As Long
runningIndex = 0
ReDim sheetsArr(1 To totalSheets) 'It would make sense to me to extend this syntax to an array as above, since an array is itself a type/object in other languages 
For j = LBound(myWbks) To UBound(myWbks) 'j iterates through the workbooks
    Set myWbks(j) = Workbooks.Open(FileName:=arr(j), UpdateLinks:=False)   'false should suppress update links msgbox
    For Each sh In myWbks(j).Worksheets 'iterates through the worksheets in each workbook
        sheetsArr(runningIndex) = sh  'add the worksheet to an array of worksheets so we don't have to use another for loop to get through the workbook layer too
        runningIndex = runningIndex + 1
    Next
Next j

What's the correct syntax to create an array of 2D arrays using for loops?

"To set up an array with more than one dimension, you simply add a comma after the first number between the round brackets of your array name, then add another number. Like this: `Dim MyArray(5, 4) As Integer`" https://www.homeandlearn.org/multidimensional_arrays.html — Marcucciboy2, Aug 09 '18 at 18:45
This is no different from setting up a jagged array. The "jagged" part is just 2D. — Comintern, Aug 09 '18 at 18:47
@Comintern you're right, but the syntax I thought would `Dim` the 2D array stored in the array of arrays-- `Dim myWbksArr(k)() As String ReDim myWbksArr(k)(sheetsArr(k).UsedRange.Rows.Count, sheetsArr(k).UsedRange.Columns.Count)` (Dim the array element as an array w/(), ReDim it to the correct dimensions with (rows,columns) as recommended in @Marcucciboy2's comment) didn't work-- could you give me the proper syntax? — JackHannum, Aug 09 '18 at 20:21
See this answer on [Excel VBA - How to Redim a 2D array](https://stackoverflow.com/a/21014121/4088852). — Comintern, Aug 09 '18 at 20:31
@Comintern I have the ReDimming the 2D array part down I think-- `ReDim myWbksArr(k)(sheetsArr(k).UsedRange.Rows.Count, sheetsArr(k).UsedRange.Columns.Count)` should do that since UsedRange...Count are ints/longs, but it's putting it as part of the array of 2D arrays that's the problem. — JackHannum, Aug 09 '18 at 20:44
If I understand what you are trying to do, you don't need to `reDim` the array that contains the data from the worksheet range. If `arr` is a variant array, then `arr = myRange` will create a 2D array of the correct size. If `myRange` is a `Union` of multiple ranges, then `arr` will be a 3D array. So you just need to store each sheets `myRange` in an array of Worksheets; and each array of Worksheets in an array or Workbooks. — Ron Rosenfeld, Aug 10 '18 at 01:41

score 0 · Answer 1 · answered Aug 10 '18 at 01:18

Maybe this will help. It will read the contents of all the open workbooks into an array of arrays.

You'll probably want to change the method of selected the range to store, and maybe even the method of deciding which workbooks to go through. But this should give you an idea:

Option Explicit
Sub wbtoArr()
    Dim wbsArr, wbArr
    Dim WB As Workbook, WS As Worksheet
    Dim I As Long, J As Long

ReDim wbsArr(1 To Workbooks.Count)
J = 0
For Each WB In Workbooks
    J = J + 1
    ReDim wbArr(1 To WB.Worksheets.Count)
    I = 0
    For Each WS In WB.Worksheets
        I = I + 1
        wbArr(I) = WS.UsedRange
    Next WS
    wbsArr(J) = wbArr
Next WB

End Sub

VBA- Create an Array of Multi-dimensional Arrays

1 Answers1