Excel VBA, 2003 - Convert string to 2-dimensional array

Question

I inherited an old Excel (.xls) spreadsheet starting from 2005.

For some reason the spreadsheet uses the name manager for quite large data stored as strings instead of referring to a table with the data. This is what I am trying to change so it will be easier to change the data currently stored in the name manager (at the moment it is not possible to change any of the data as the character limit in the name manager has somehow been exceeded).

I would like to make use of as much of the current VBA code as possible which is why I currently have a setup where I use VBA to convert data from the spreadsheet to a string in the form of

"CELL"\"CELL"\"...""CELL";"CELL"\"CELL"\"CELL";

Where I (or the old code) make use of backslash, \, as a delimiter for a new column and semicolon, ;, as a delimiter for a new row.

I want to convert my string to an 2-dimensional array called 'arkArray' so I can make use of the following code:

arkCellData = arkArray(i, j)

What is the best way to do so?

Answer 1

How to make a two dimensional array.

Sub test()
    Dim myArray(), vS, vS2
    Dim vMax()
    Dim s As String
    Dim myMax As Integer, i As Integer,  j As Integer
    Dim r As Long

    s = "apple\banana\John;some\reason\use\Tom;table\data\limit;"

    If Right(s, 1) = ";" Then
        s = Left(s, Len(s) - 1)
    End If

    vS = Split(s, ";")
    ReDim vMax(UBound(vS))

    For i = 0 To UBound(vS)
        vS2 = Split(vS(i), "\")
        vMax(i) = UBound(vS2) + 1
    Next i

    myMax = WorksheetFunction.Max(vMax)
    r = UBound(vS) + 1

    ReDim myArray(1 To r, 1 To myMax)

    For i = 1 To UBound(myArray)
        vS2 = Split(vS(i - 1), "\")
        For j = 1 To UBound(vS2) + 1
            myArray(i, j) = vS2(j - 1)
        Next j
    Next i
    Range("a1").Resize(r, myMax) = myArray


End Sub

Answer 2

Here is a typical method:

Sub Fill2D()
    Dim s As String, r As Range
    Dim kolumn As Long, roww As Long
    Dim arr1, arr2, a1, a2

    kolumn = 0
    roww = 1
    s = "alpha\beta\gamma;mike\jim\john;red\blue\green"

    arr1 = Split(s, ";")

    For Each a1 In arr1
        roww = 1
        kolumn = kolumn + 1
        arr2 = Split(a1, "\")
        For Each a2 In arr2
            Cells(roww, kolumn) = a2
            roww = roww + 1
        Next a2
    Next a1

End Sub

(you would change the starting values of kolumn and roww to pick a different starting point)

Once the 2-D array is in cells, you would verify it and then copy it into an internal VBA array.