I was working on a project recently where I had to convert a string into to a 2 dimensional array. I'm not saying this is the
best way, but its the way I ended up doing it
When calling the function, just specify the string, the number of
columns you want the resulting array to have and the seperator to use for converting the string to a 2 dimensional array. The
default seperator is a space character, but could be anything you want. It will calculate the required number of rows.
Option Base 0
Public Function Str_2d(str As String, intCol, Optional Delim As String = " ") As Variant
'convert a string to a 2 dimensional array - using space as default delimiter.
Dim Num_Rows As Long
Dim arrTemp, arrTemp2
Dim iCount As Integer, Row_Count As Integer, Col_Count As Integer
Num_Rows = Application.RoundUp((Len(str) - Len(Replace(str, Delim, "")) + 1) / intCol, 0) 'determine size and shape of
resulting array - number of rows for number columns
arrTemp = Split(str, Delim): iCount = 0 'icount is the index for arrtemp and that is a zero based array
Redim arrTemp2(Num_Rows - 1, intCol - 1)
For Row_Count = 1 To Num_Rows
For Col_Count = 1 To intCol
arrTemp2(Row_Count - 1, Col_Count - 1) = Trim(arrTemp(iCount))
iCount = iCount + 1
If iCount > UBound(arrTemp) Then Exit For
Str_2d = arrTemp2
Public Sub driver()
x = Str_2d("This is a sweet function for 2 dimensional arrays Ha! Ha", 3)
'x = Str_2d("This is a sweet function^for 2 dimensional arrays^Ha! Ha", 3, "^")
'x = Str_2d("This is a sweet,function for 2,dimensional,arrays,Ha! Ha", 1, ",")
'display the result....
ActiveSheet.Range("A1").Resize(UBound(x, 1) + 1, UBound(x, 2) + 1) = x
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Hope someone finds it useful.