Excel的VBA - 如何REDIM一个二维数组？ [英] Excel VBA - How to Redim a 2D array?

问题描述

在通过Visual Basic中的Excel，我迭代通过加载到Excel中的发票CSV文件。发票是由客户端确定的模式。

我读它们变成一个动态二维数组，然后将它们与旧发票写到另一个工作表。我明白我必须扭转的行和列，因为只有一个数组的最后一维可Redimmed，然后转当我把它写主表。

在某处，我有语法错误。它使告诉我，我已经量纲数组。不知怎的，我才将其创建为一个静态数组？我需要做什么才能修复让它动态操作？

工作code PER给出的答复

 子InvoicesUpdate（）

应用程序设置
Application.ScreenUpdating =假
Application.DisplayAlerts =假
Application.Calculation = xlCalculationManual实例化控制变量
昏暗allRows长，currentOffset长，invoiceActive由于布尔，mAllRows只要
昏暗iAllRows长，unusedRow长，排长，mWSExists由于布尔，newmAllRows只要实例化变量发票
昏暗accountNum作为字符串，CUSTNAME作为字符串，vinNum作为字符串，caseNum作为字符串，statusField作为字符串
昏暗invDate作为字符串，makeField作为字符串，feeDesc作为字符串，amountField作为字符串，INVNUM作为字符串实例化工作簿变量
昏暗MWB作为工作簿主
昏暗IWB作为工作簿进口实例化工作表变量
昏暗的MWS作为工作表
昏暗的IWS作为工作表实例化的变量范围
昏暗IDATA作为范围'初始化变量
invoiceActive = FALSE
行= 0打开工作簿进口
Workbooks.Open（路径：excel_invoices.csv）
设置IWB = ActiveWorkbook
设置全球联保= iWB.Sheets（excel_invoices.csv）
iWS.Activate
范围（A1）。选择
iAllRows = iWS.UsedRange.Rows.Count'进口数据的计数行实例化阵列，包括客户名称额外的列
昏暗的发票（）
使用ReDim发票（10,0）通过行'循环。
做    检查客户端和存储客户端名称的开始
    如果ActiveCell.Value =帐号那么        CLIENTNAME = ActiveCell.Offset（-1，6）。价值    万一    如果ActiveCell.Offset（0，3）。价值＆所述;＆GT;空，ActiveCell.Value＆LT;＆GT; 帐户号和ActiveCell.Offset（2,0）=空接        invoiceActive = TRUE        填写的账户信息。
        accountNum = ActiveCell.Offset（0，0）。价值
        vinNum = ActiveCell.Offset（0，1）。价值
        离开了客户名称FDCPA原因
        caseNum = ActiveCell.Offset（0，3）。价值
        statusField = ActiveCell.Offset（0，4）。价值
        invDate = ActiveCell.Offset（0,5）。价值
        makeField = ActiveCell.Offset（0，6）。价值    万一    如果invoiceActive = True和ActiveCell.Value =空并且ActiveCell.Offset（0，6）。价值=空，ActiveCell.Offset（0，9）。价值=空然后        让超过$ 0℃发票其他东西肯定
        如果ActiveCell.Offset（0,8）。价值＆所述;＆GT; 0，则            填充各个项值。
            feeDesc = ActiveCell.Offset（0,7）。价值
            amountField = ActiveCell.Offset（0,8）。价值
            INVNUM = ActiveCell.Offset（0,10）.value的            将数据传输到数组
            发票（0，行）== TODAY（）
            发票（1行）= accountNum
            发票（2行）= CLIENTNAME
            发票（3行）= vinNum
            发票（4行）= caseNum
            发票（5行）= statusField
            发票（6行）= invDate
            发票（7行）= makeField
            发票（8行）= feeDesc
            发票（9行）= amountField
            发票（10行）= INVNUM            为阵'增量行计数器
            排排= + 1            调整为下一个条目数组
            使用ReDim preserve发票（10行）         万一    万一    查找发票的结束
    如果invoiceActive = TRUE且ActiveCell.Offset（0,9）LT;＆GT;空接        的标志设置为发票外
        invoiceActive = FALSE    万一    '增加活动单元格到下一个单元格下来
    ActiveCell.Offset（1,0）.Activate在上次使用的行定义循环结束
循环直到ActiveCell.row = iAllRows关闭导入数据文件
iWB.Close
 

解决方案

这是不完全直观，但你不能REDIM的（VB6 REF），如果你有尺寸变暗它的数组。从链接页面确切的报价是：

  

ReDim语句用于大小还是调整，有一个动态数组
  已经使用私有，公共，或在昏暗正式宣布
  with语句的空括号（无量纲标）。

而不是换句话说，昏暗的发票（10,0）

您应该使用

 昏暗的发票（）
REDIM发票（10,0）
 

然后，当您使用ReDim，你需要使用 REDIM preserve（10行）

警告：当Redimensioning多维数组，如果你想preserve你的价值观，你只能增加最后一个维度。 I.E. REDIM preserve（11行）甚至（11,0）将失败。

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.

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