Array and Split commands to create a 2 dimensional array

Question

I'm having some trouble populating an array using a split command.

The string I currently have is below

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"

I have an array that I want to be multi-dimensional and would like each Row# Column# to be in the correct part of the array based on its number.

For example

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"

Now I understand how to populate a single dimension array using the split command

MyArray = Split(MyString, vbNewLine)

This would mean that

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"

But I don't know how to use a split command to populate the second dimension.

Is this possible and if it is how?
If it isn't possible, can anyone suggest how to actually populate this?

Mark Bertenshaw · Accepted Answer · 2012-05-16 09:30:33Z

5

You can't use Split() on anything other than a String, or a Variant containing a String. If you want to generate a two dimensional string array, you will have iterate through the array returned by Split(), and run Split() on each string. The following function should do what you want:

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, "~")

answered May 16, 2012 at 9:30

Mark Bertenshaw

5,7093 gold badges30 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ste Moore Over a year ago

I thought that may be the case, and I would need a different method. Thank you very much, your function works perfectly.

Mark Bertenshaw Over a year ago

If you want excitement, and don't mind having the column and row index the other way round, try wqw's answer!

wqw · Accepted Answer · 2012-05-16 09:45:58Z

5

Here is a faster hack that just swaps 1D and 2D arrays with no loops:

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

answered May 16, 2012 at 9:45

wqw

12k2 gold badges38 silver badges43 bronze badges

3 Comments

Mark Bertenshaw Over a year ago

Ha! Nice hack! If I was to use this, however, I would want a longer explanation as to what you were doing. Also, doing it your way, you are forced to number the array indices (Column, Row).

wqw Over a year ago

@MarkBertenshaw: internal array representation forces lower bounds to come first when indexing. If you use For Each on a 2D array that's the order elements are looped. Both arrays total sizes must match too or risk AV on tear-down. All these constraints are compensated by lack of any loops and no string copying.

Mark Bertenshaw Over a year ago

I knew about how SAFEARRAYs work, but I have never even thought about using For..Each..Next with a non-1D array! Even in an unsupported language, there's still more things to learn.

Bob77 · Accepted Answer · 2012-05-19 18:45:42Z

1

Here is another approach based on the Variant type's ability to contain an array. Instead of a 2D array we have an "array of arrays."

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

The bonus here is "ragged arrays" where each row can have a different number of columns.

answered May 19, 2012 at 18:45

Bob77

13.3k1 gold badge31 silver badges37 bronze badges

1 Comment

Mark Over a year ago

Are you missing return statement from the SplitSplit function?

Collectives™ on Stack Overflow

Array and Split commands to create a 2 dimensional array

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related