vba二维数组初始化_Excel的VBA - 如何REDIM一个二维数组？

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

解决方案

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.