How to split a string into a 2-dimensional array in vba?

Question

I want to be able to split a formatted string (such as the one following) into an array (which I will then use later on).

Example string: (x, f), (5, 6), (6, 1), (7, 8), (8, 5), (9, 5), (10, 5), (11, 3), (12, 4), (13, 1), (14, 6), (15, 2), (16, 10)

Each set of brackets would be one dimension, and the numbers within would constitute another. For example strArray (4, 2) would result in bracket 4, number 2 -> which is 8 in this instance.

I've thought about brute forcing it by using multiple SPLIT functions, and I've looked into Regular Expressions to do it. I'm confident in making a 1-dimensional array, but struggling with a 2-dimensional array.

I am using Microsoft Excel 2016, and am an intermediate programmer in VBA.

This is one approach I've tried to process each bracket, but it doesn't result in a 2-dimensional array:

Sub EvaluateString(txtString as String)

   txtArray = Split(txtSTring, "),(")

   If IsEmpty(txtArray) Then L=0 Else L=UBound(txtArray)

   'remove leading "(" and last ")"
   txtArray(0) = Replace(txtArray(0), "(", "")
   txtArray(L) = Replace(txtArray(L), ")", "")

   For i = 0 to L
      'Do something
   next i

End Sub

Answer 1

Interesting question. I would suggest making use of a couple of Application methods to "slice" two 1D-arrays from your original string, and then "join" them into a single array through Transpose.

Sub Test()

'Prepare your string
Dim str As String: str = "(x, f), (5, 6), (6, 1), (7, 8), (8, 5), (9, 5), (10, 5), (11, 3), (12, 4), (13, 1), (14, 6), (15, 2), (16, 10)"
str = Replace(Replace(Replace(str, " ", ""), "(", ""), ")", "")

'Prepare your array
Dim arr As Variant: arr = Split(str, ",")
Dim lb As Long: lb = UBound(arr) + 1

'Prepare two "slicer" 1D-arrays
Dim slc1 As Variant: slc1 = Evaluate("TRANSPOSE((ROW(1:" & (lb / 2) & ")*2)-1)")
Dim slc2 As Variant: slc2 = Evaluate("TRANSPOSE(ROW(1:" & (lb / 2) & ")*2)")

'Slice your array and transpose it into a a single array to be used
With Application
    arr = .Transpose(Array(.Index(arr, 1, slc1), .Index(arr, 1, slc2)))
End With

Debug.Print arr(4, 2)

End Sub

Answer 2

If I understand You correctly, try this:

Sub test2()

Dim txtstring As String

txtstring = "(x, f),(5, 6),(6, 1),(7, 8),(8, 5),(9, 5),(10, 5),(11, 3),(12, 4),(13, 1),(14, 6),(15, 2),(16, 10)"

Call EvaluateString(txtstring)

End Sub

Sub EvaluateString(txtstring As String)

Dim txtArray2d As Variant

txtArray = Split(txtstring, "),(")

If IsEmpty(txtArray) Then L = 0 Else L = UBound(txtArray)

'remove leading "(" and last ")"
txtArray(0) = Replace(txtArray(0), "(", "")
txtArray(L) = Replace(txtArray(L), ")", "")

ReDim txtArray2d(0 To L, 1 To 2)

For i = 0 To L
   txtArray2d(i, 1) = Split(txtArray(i), ",")(0)
   txtArray2d(i, 2) = Trim(Split(txtArray(i), ",")(1))
Next i

End Sub

and taking your string variant as example, 2d array values look like this:

etc.