用于创建二维数组的数组和分割命令

我在使用 split 命令填充数组时遇到一些问题。

我目前拥有的字符串如下

MyString = "Row1 Column1[~]Row1 Column2[~]Row1 Column3" & vbNewLine & _
"Row2 Column1[~]Row2 Column2[~]Row2 Column3" & vbNewLine & _
"Row3 Column1[~]Row3 Column2[~]Row3 Column3" & vbNewLine & _
"Row4 Column1[~]Row4 Column2[~]Row4 Column3"

我有一个数组，我希望它是多维的，并且希望每个 Row# Column# 根据其编号位于数组的正确部分。

例如

MyArray(1,1) = "Row1 Column1"
MyArray(2,1) = "Row2 Column1"
MyArray(3,1) = "Row3 Column1"
MyArray(4,1) = "Row4 Column1"

MyArray(1,2) = "Row1 Column2"
MyArray(2,2) = "Row2 Column2"
MyArray(3,2) = "Row3 Column2"
MyArray(4,2) = "Row4 Column2"

MyArray(1,3) = "Row1 Column3"
MyArray(2,3) = "Row2 Column3"
MyArray(3,3) = "Row3 Column3"
MyArray(4,3) = "Row4 Column3"

现在我了解如何使用 split 命令填充一维数组

MyArray = Split(MyString, vbNewLine)

这意味着

MyArray(1) = "Row1 Column1[~]Row1 Column2[~]Row1 Column3"
MyArray(2) = "Row2 Column1[~]Row2 Column2[~]Row2 Column3"
MyArray(3) = "Row3 Column1[~]Row3 Column2[~]Row3 Column3"
MyArray(4) = "Row4 Column1[~]Row4 Column2[~]Row4 Column3"

但我不知道如何使用 split 命令来填充第二个维度。

这可能吗？如果可能的话，如何实现？
如果不可能，有人可以建议如何实际填充它吗？

除了字符串或包含字符串的变体之外，您不能对任何其他对象使用 Split()。如果要生成二维字符串数组，则需要迭代 Split() 返回的数组，并对每个字符串运行 Split()。以下函数应该执行您想要的操作：

Private Function SplitTo2DArray(ByRef the_sValue As String, ByRef the_sRowSep As String, ByRef the_sColSep As String) As String()

    Dim vasValue                    As Variant
    Dim nUBoundValue                As Long
    Dim avasCells()                 As Variant
    Dim nRowIndex                   As Long
    Dim nMaxUBoundCells             As Long
    Dim nUBoundCells                As Long
    Dim asCells()                   As String
    Dim nColumnIndex                As Long

    ' Split up the table value by rows, get the number of rows, and dim a new array of Variants.
    vasValue = Split(the_sValue, the_sRowSep)
    nUBoundValue = UBound(vasValue)
    ReDim avasCells(0 To nUBoundValue)

    ' Iterate through each row, and split it into columns. Find the maximum number of columns.
    nMaxUBoundCells = 0
    For nRowIndex = 0 To nUBoundValue
        avasCells(nRowIndex) = Split(vasValue(nRowIndex), the_sColSep)
        nUBoundCells = UBound(avasCells(nRowIndex))
        If nUBoundCells > nMaxUBoundCells Then
            nMaxUBoundCells = nUBoundCells
        End If
    Next nRowIndex

    ' Create a 2D string array to contain the data in <avasCells>.
    ReDim asCells(0 To nUBoundValue, 0 To nMaxUBoundCells)

    ' Copy all the data from avasCells() to asCells().
    For nRowIndex = 0 To nUBoundValue
        For nColumnIndex = 0 To UBound(avasCells(nRowIndex))
            asCells(nRowIndex, nColumnIndex) = avasCells(nRowIndex)(nColumnIndex)
        Next nColumnIndex
    Next nRowIndex

    SplitTo2DArray = asCells()

End Function

Example:

Dim asCells() As String

asCells() = SplitTo2DArray(MyString, vbNewline, "~")