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" homeandlearn.org/multidimensional_arrays.html — Marcucciboy2
– Marcucciboy2, Commented Aug 9, 2018 at 18:45
This is no different from setting up a jagged array. The "jagged" part is just 2D. — Comintern
– Comintern, Commented Aug 9, 2018 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
– JackHannum, Commented Aug 9, 2018 at 20:21

Ron Rosenfeld · Accepted Answer · 2018-08-10 01:18:19Z

0

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

answered Aug 10, 2018 at 1:18

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA- Create an Array of Multi-dimensional Arrays

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related