使用Array和Split命令创建二维数组

Question

使用Array和Split命令创建二维数组

7

我在使用分割命令时遇到了一些问题，无法填充数组。

我目前拥有的字符串如下所示

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"

我有一个数组，想要将其变成多维数组，并希望每个行和列都能根据它们的编号正确地放在数组的相应位置。

举个例子：

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"

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

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

- Ste Moore

3个回答

5

您不能在除了字符串或包含字符串的变量之外的任何内容上使用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

例子：

Dim asCells() As String

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

- Mark Bertenshaw

我本以为可能是这样，我需要一个不同的方法。非常感谢，你的函数完美地运行。 - Ste Moore

如果你想要一些刺激，而且不介意行和列的索引相反，可以尝试wqw的答案！ - Mark Bertenshaw

1

这里是另一种基于 Variant 类型能够包含数组的方法。我们不使用二维数组，而是使用“数组的数组”。

Option Explicit

Private Function SplitSplit(ByRef Delimited As String) As Variant
    Dim Rows() As String
    Dim AryOfArys As Variant
    Dim I As Long

    Rows = Split(Delimited, vbNewLine)
    ReDim AryOfArys(UBound(Rows))
    For I = 0 To UBound(Rows)
        AryOfArys(I) = Split(Rows(I), "[~]")
    Next
    SplitSplit = AryOfArys
End Function

Private Sub Form_Load()
    Dim MyString As String
    Dim MyAry As Variant

    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"

    MyAry = SplitSplit(MyString)

    AutoRedraw = True
    DumpAry MyAry
End Sub

Private Sub DumpAry(ByRef AryOfArys As Variant)
    Dim Row As Long, Col As Long

    For Row = 0 To UBound(AryOfArys)
        For Col = 0 To UBound(AryOfArys(Row))
            Print AryOfArys(Row)(Col),
        Next
        Print
    Next
End Sub

这里的奖励是“不规则数组”，其中每一行可以有不同数量的列。

- Bob77

你的SplitSplit函数是否缺少了return语句？ - Mark

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- wqw · Accepted Answer

这是一种更快的技巧，可以在不使用循环的情况下交换1D和2D数组：

Option Explicit

Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)

Private Sub Form_Load()
    Dim MyString        As String
    Dim MyFlatArray     As Variant
    Dim MyArray         As Variant

    '--- split source string by column&row separator
    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"
    MyFlatArray = Split(Replace(MyString, "[~]", vbCrLf), vbCrLf)
    '--- convert to 2D array
    ReDim MyArray(1 To 3, 1 To 4) As String
    pvSwapArrays MyArray, MyFlatArray
    '--- access row 2
    Debug.Print MyArray(1, 2)
    Debug.Print MyArray(2, 2)
    Debug.Print MyArray(3, 2)
End Sub

Private Sub pvSwapArrays(vDst As Variant, vSrc As Variant)
    Dim nDstType        As Integer
    Dim nSrcType        As Integer
    Dim pSrcArray       As Long
    Dim pDstArray       As Long
    Dim lTemp           As Long

    '--- sanity check types (VARIANT.vt)
    Call CopyMemory(nDstType, vDst, 2)
    Call CopyMemory(nSrcType, vSrc, 2)
    Debug.Assert (nSrcType And &H2000) <> 0 '--- check if VT_ARRAY
    Debug.Assert nDstType = nSrcType '--- check if dest type matches src (&H2008 = VT_ARRAY | VT_BSTR)
    '--- get VARIANT.parray
    Call CopyMemory(pSrcArray, ByVal VarPtr(vSrc) + 8, 4)
    Call CopyMemory(pDstArray, ByVal VarPtr(vDst) + 8, 4)
    '--- swap SAFEARRAY.pvData
    Call CopyMemory(lTemp, ByVal pSrcArray + 12, 4)
    Call CopyMemory(ByVal pSrcArray + 12, ByVal pDstArray + 12, 4)
    Call CopyMemory(ByVal pDstArray + 12, lTemp, 4)
End Sub