vba - 使用 2D 数组进行 Redim

Question

我正在尝试将名称及其值插入二维数组:

 ____________
| name| value| index 0
|name1|value1| index 1
|name2|value2| index 2 ...
      .
      .
      .

到目前为止我做了什么:

Function ParseCSV(ByVal FileName As String)
Dim FS          'As Scripting.FileSystemObject
Dim Txt         'As Scripting.TextStream
Dim CSVLine
Dim arrayOfElements
Dim nbErrors As Integer
Dim namesNotInDb() As String
Dim element As Variant
Dim errorMsg As String
Dim namesAgeMatrix() As String
Dim nbrAges As Integer


Set FS = CreateObject("Scripting.FileSystemObject")
Set Txt = FS.OpenTextFile(FileName, 1, False)
nbrAges = 0
Do While Not Txt.AtEndOfStream
    If nbrAges = 0 Then ReDim namesAgeMatrix(0, 1) Else ReDim Preserve namesAgeMatrix(nbrAges, 1)
    CSVLine = Txt.ReadLine
    arrayOfElements = Split(CSVLine, ",")
    namesAgeMatrix(nbrAges, 0) = arrayOfElements(0)
    If arrayOfElements(1) = "N/A" Then
        nbErrors = nbErrors + 1
        ReDim Preserve snamesNotInDb(nbErrors)
        namesNotInDb(nbErrors) = arrayOfElements(0)
        instrumentPriceMatrix(nbrInstruments, 1) = 0
    Else
        namesAgeMatrix(nbrAges, 1) = arrayOfElements(1)
    End If
    nbrAges = nbrAges + 1
Loop
Txt.Close
If nbErrors > 0 Then 'displaying error/success message
    errorMsg = ""
    For Each name In namesNotInDb
        errorMsg = errorMsg & name & " "
    Next
    MsgBox "Warning : " & errorMsg & "have no feed in DB. Name set by default to John.", vbExclamation
Else
    MsgBox "Importation success!", vbOKOnly
End If
ParseCSV = namesAgeMatrix
End Function

我得到的错误是: Subscription our of range对于以下内容:

Else ReDim namesAgeMatrix(nbrAges, 1)

如何以正确的方式重新调整我的阵列？

提前致谢。

Answer 1

您只能在 VBA 中“重新调整”多维数组的最后一个元素。

使用数组的格式，如...

Variable(Row, Column) 

如果您的想法是添加“行”，则实际上相对于您的流程是落后的。

您需要将数组结构反转为以下...

Variable(Column, Row)

在这种格式中，您可以在开始时定义信息“列”的数量，然后一遍又一遍地重新调整行。

ReDim Preserve namesAgeMatrix(1, nbrAges) ' for example. but you must keep the "1" forever a "1".

这需要一点时间来适应，因为 Excel 和 Office 类型的系统，甚至您的自然倾向是将其结构化为“行、列”