Excel VBA - 如何重新绘制二维数组? [英] Excel VBA - How to Redim a 2D array?

问题描述

在通过 Visual Basic 的 Excel 中，我正在遍历加载到 Excel 中的发票的 CSV 文件.发票采用客户可确定的模式.

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?

每个答案的工作代码

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

推荐答案

这不是很直观，但你不能 Redim(VB6 Ref) 一个数组，如果你用维度将它变暗.链接页面的确切报价是:

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:

ReDim 语句用于调整动态数组的大小或调整大小已经使用 Private、Public 或 Dim 正式声明带有空括号的语句(没有维度下标).

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).

换句话说，不是dim invoices(10,0)

你应该使用

Dim invoices()
Redim invoices(10,0)

然后当你ReDim时，你需要使用Redim Preserve (10,row)

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

警告:在重新调整多维数组的维度时，如果你想保留你的值，你只能增加最后一个维度.IE.Redim Preserve (11,row) 甚至 (11,0) 都会失败.

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.

这篇关于Excel VBA - 如何重新绘制二维数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！