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

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

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

Solution

Excel 2D array format is {"x", "f"; "5", "6"; "6", "1"}, so few replaces should be enough :

Sub EvaluateString(txtString as String)

    txtString = Replace(Replace(txtString, " ", ""), "),(", """;""")

    txtString = Replace(Replace(Replace(txtString, "(", "{"""), ")", """}"), ",", """,""")

    txtArray = Evaluate(txtString)          ' txtArray is now 2D array Variant(1 to x, 1 to 2)

End Sub

Note that Application.Evaluate method is limited to 255 characters.