VBA parsing 2D delimited string into a range in excel

Question

I have a two dimensional string delimited by line and within each line delimited by value.

So it's a comma delimited string with an EOL marker at the end of each line. Example:

val1, val2, val3 ... valn [EOL]
val1, val2, val3 ... valn [EOL]
...
val1, val2, val3 ... valn [EOL]

If I create a loop to split() each line by [EOL] then another loop inside that to split() each value by ',' and then write each value one at a time to a cell in the worksheet it takes forever so I'm looking for a more efficient solution.

Is it possible to parse the string into a 2D array/variant and then write the whole thing at once to a named range?

Answer 1

We can do what @Macro Man said in the comments. It will be easy if all rows contain the same count of comma delimited values. If not, it will be more complicated. But nevertheless solvable.

Option Base 0

Sub test()

 sString = "val1, val2, val3 ... valn" & Chr(10) & "val1, val2 ... valn" & Chr(10) & "val1, val2, val3, val4 ... valn" & Chr(10) & "val1" & Chr(10)

 Dim aDataArray() As Variant
 Dim lLinesCount As Long
 Dim lValuesCount As Long
 Dim lMaxValuesCount As Long

 aLines = Split(sString, Chr(10))
 lLinesCount = UBound(aLines)
 ReDim aDataArray(0 To lLinesCount, 0)

 For i = LBound(aLines) To UBound(aLines)
  aValues = Split(aLines(i), ",")
  lValuesCount = UBound(aValues)
  If lValuesCount > lMaxValuesCount Then lMaxValuesCount = lValuesCount
  ReDim Preserve aDataArray(0 To lLinesCount, 0 To lMaxValuesCount)

  For j = LBound(aValues) To UBound(aValues)
   aDataArray(i, j) = aValues(j)
  Next
 Next

 With ActiveSheet
  .Range("B2").Resize(lLinesCount + 1, lMaxValuesCount + 1).Value = aDataArray
 End With

End Sub