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?

How long do these strings get? Over 255 characters? Also, would there be any commas in these strings? — JvdV
– JvdV, Commented Feb 14, 2020 at 14:56
They longest is over 15,000 characters (i had to do the following to see the entire string ="refrence in name manager" -> insert the entire string in the spreadsheet -> copy the cells data into a .txt file -> remove tabs -> count characters — Kenno
– Kenno, Commented Feb 14, 2020 at 15:38
IMO, I would not "patch" old VBA here but rewrite to do things properly... Reference the data in the sheet - you can read a range's value into a 2D array with one line of code. The name manager > string > 2D array seems like bad practice. — BigBen
– BigBen, Commented Feb 14, 2020 at 16:18
@BigBen, this seems like a better pratice, would you mind explaining to me how I could do so with only one line of code? (This will most likely lead me to rewrite a lot of the code, but i don't like current setup either) — Kenno
– Kenno, Commented Feb 14, 2020 at 17:07

Gary's Student · Accepted Answer · 2020-02-14 14:38:14Z

1

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.

answered Feb 14, 2020 at 14:38

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

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

Comments

Dy.Lee · Accepted Answer · 2020-02-14 15:28:04Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related