Excel VBA - How to Redim a 2D array?

Question

In Excel via Visual Basic, I am iterating through a CSV file of invoices that is loaded into Excel. The invoices are in a determinable pattern by client.

I am reading them into a dynamic 2D array, then writing them to another worksheet with older invoices. I understand that I have to reverse rows and columns since only the last dimension of an array may be Redimmed, then transpose when I write it to the master worksheet.

Somewhere, I have the syntax wrong. It keeps telling me that I have already Dimensionalized the array. Somehow did I create it as a static array? What do I need to fix in order to let it operate dynamically?

WORKING CODE PER ANSWER GIVEN

Sub InvoicesUpdate() ' 'Application Settings Application.ScreenUpdating = False Application.DisplayAlerts = False Application.Calculation = xlCalculationManual  'Instantiate control variables Dim allRows As Long, currentOffset As Long, invoiceActive As Boolean, mAllRows As Long Dim iAllRows As Long, unusedRow As Long, row As Long, mWSExists As Boolean, newmAllRows As Long  'Instantiate invoice variables Dim accountNum As String, custName As String, vinNum As String, caseNum As String, statusField As String Dim invDate As String, makeField As String, feeDesc As String, amountField As String, invNum As String  'Instantiate Workbook variables Dim mWB As Workbook 'master Dim iWB As Workbook 'import  'Instantiate Worksheet variables Dim mWS As Worksheet Dim iWS As Worksheet  'Instantiate Range variables Dim iData As Range  'Initialize variables invoiceActive = False row = 0  'Open import workbook Workbooks.Open ("path:excel_invoices.csv") Set iWB = ActiveWorkbook Set iWS = iWB.Sheets("excel_invoices.csv") iWS.Activate Range("A1").Select iAllRows = iWS.UsedRange.Rows.Count 'Count rows of import data  'Instantiate array, include extra column for client name Dim invoices() ReDim invoices(10, 0)   'Loop through rows. Do      'Check for the start of a client and store client name     If ActiveCell.Value = "Account Number" Then          clientName = ActiveCell.Offset(-1, 6).Value      End If      If ActiveCell.Offset(0, 3).Value <> Empty And ActiveCell.Value <> "Account Number" And ActiveCell.Offset(2, 0) = Empty Then          invoiceActive = True          'Populate account information.         accountNum = ActiveCell.Offset(0, 0).Value         vinNum = ActiveCell.Offset(0, 1).Value         'leave out customer name for FDCPA reasons         caseNum = ActiveCell.Offset(0, 3).Value         statusField = ActiveCell.Offset(0, 4).Value         invDate = ActiveCell.Offset(0, 5).Value         makeField = ActiveCell.Offset(0, 6).Value      End If      If invoiceActive = True And ActiveCell.Value = Empty And ActiveCell.Offset(0, 6).Value = Empty And ActiveCell.Offset(0, 9).Value = Empty Then          'Make sure something other than $0 was invoiced         If ActiveCell.Offset(0, 8).Value <> 0 Then              'Populate individual item values.             feeDesc = ActiveCell.Offset(0, 7).Value             amountField = ActiveCell.Offset(0, 8).Value             invNum = ActiveCell.Offset(0, 10).Value              'Transfer data to array             invoices(0, row) = "=TODAY()"             invoices(1, row) = accountNum             invoices(2, row) = clientName             invoices(3, row) = vinNum             invoices(4, row) = caseNum             invoices(5, row) = statusField             invoices(6, row) = invDate             invoices(7, row) = makeField             invoices(8, row) = feeDesc             invoices(9, row) = amountField             invoices(10, row) = invNum              'Increment row counter for array             row = row + 1              'Resize array for next entry             ReDim Preserve invoices(10,row)           End If      End If      'Find the end of an invoice     If invoiceActive = True And ActiveCell.Offset(0, 9) <> Empty Then          'Set the flag to outside of an invoice         invoiceActive = False      End If      'Increment active cell to next cell down     ActiveCell.Offset(1, 0).Activate  'Define end of the loop at the last used row Loop Until ActiveCell.row = iAllRows  'Close import data file iWB.Close 

Answer 1

This isn't exactly intuitive, but you cannot Redim(VB6 Ref) an array if you dimmed it with dimensions. Exact quote from linked page is:

The ReDim statement is used to size or resize a dynamic array that has already been formally declared using a Private, Public, or Dim statement with empty parentheses (without dimension subscripts).

In other words, instead of dim invoices(10,0)

You should use

Dim invoices() Redim invoices(10,0) 

Then when you ReDim, you'll need to use Redim Preserve (10,row)

Warning: When Redimensioning multi-dimensional arrays, if you want to preserve your values, you can only increase the last dimension. I.E. Redim Preserve (11,row) or even (11,0) would fail.

Answer 2

I stumbled across this question while hitting this road block myself. I ended up writing a piece of code real quick to handle this ReDim Preserve on a new sized array (first or last dimension). Maybe it will help others who face the same issue.

So for the usage, lets say you have your array originally set as MyArray(3,5), and you want to make the dimensions (first too!) larger, lets just say to MyArray(10,20). You would be used to doing something like this right?

 ReDim Preserve MyArray(10,20) '<-- Returns Error 

But unfortunately that returns an error because you tried to change the size of the first dimension. So with my function, you would just do something like this instead:

 MyArray = ReDimPreserve(MyArray,10,20) 

Now the array is larger, and the data is preserved. Your ReDim Preserve for a Multi-Dimension array is complete. :)

And last but not least, the miraculous function: ReDimPreserve()

'redim preserve both dimensions for a multidimension array *ONLY Public Function ReDimPreserve(aArrayToPreserve,nNewFirstUBound,nNewLastUBound)     ReDimPreserve = False     'check if its in array first     If IsArray(aArrayToPreserve) Then                'create new array         ReDim aPreservedArray(nNewFirstUBound,nNewLastUBound)         'get old lBound/uBound         nOldFirstUBound = uBound(aArrayToPreserve,1)         nOldLastUBound = uBound(aArrayToPreserve,2)                  'loop through first         For nFirst = lBound(aArrayToPreserve,1) to nNewFirstUBound             For nLast = lBound(aArrayToPreserve,2) to nNewLastUBound                 'if its in range, then append to new array the same way                 If nOldFirstUBound >= nFirst And nOldLastUBound >= nLast Then                     aPreservedArray(nFirst,nLast) = aArrayToPreserve(nFirst,nLast)                 End If             Next         Next                     'return the array redimmed         If IsArray(aPreservedArray) Then ReDimPreserve = aPreservedArray     End If End Function 

I wrote this in like 20 minutes, so there's no guarantees. But if you would like to use or extend it, feel free. I would've thought that someone would've had some code like this up here already, well apparently not. So here ya go fellow gearheads.