The attempt to use split is correct - that will create a list of "words" (you call them "sections"). Now you need to solve 2 problems: First is to handle those sections, the second is how to combine the results.
As VBA is rather poor in dynamic arrays, I have chosen to use a Dictionary for that. As I always advocate for early binding, you will need to add a reference to the "Scripting.Library", see Does VBA have Dictionary Structure?.
To handle a section, have a closer look to it: Every section starts with a number, optional followed by "to" and another number, optional followed by "by" and a third number. When you replace those keywords with spaces, "10to14by2" would result in "10 14 2". Now use split another time and you have the values you need for a For-loop: Start Value, End Value and Step Value.
To keep the code simple, I assume that there are no syntax errors in the input (eg no other characters, extra spaces...). If this is not the case, you will need to think about error handling.
The following code uses two functions: One to split the input string into sections and loop over this sections, the second to handle a single section. I think the code is rather easy to understand:
Function CreateNumberVector(s As String) As Dictionary
Dim dict As Dictionary
Set dict = New Dictionary
Dim sections() As String, i As Long
sections = Split(s, " ")
For i = LBound(sections) To UBound(sections)
handleSection dict, sections(i)
Next i
Set CreateNumberVector = dict
End Function
Sub handleSection(dict As Dictionary, ByVal section As String)
' Input: 10to14by2
' Output: 10, 12, 14 added to dict
section = Replace(section, "to", " ")
section = Replace(section, "by", " ")
Dim tokens() As String
tokens = Split(section, " ")
' Now we have an array 10, 14, 2
Dim fromVal As Long, toVal As Long, stepVal As Long
fromVal = Val(tokens(0)) ' Startvalue for the loop
If UBound(tokens) > 0 Then ' Endvalue for the loop
toVal = Val(tokens(1))
Else
toVal = fromVal ' If nothing provided, Endvalue=Startvalue
End If
If UBound(tokens) > 1 Then ' Step for the loop
stepVal = Val(tokens(2))
Else
stepVal = 1 ' If nothing provided, step=1
End If
' Now execute the loop and add the values to the dictionary
Dim n As Long
For n = fromVal To toVal Step stepVal
dict(n) = n
Next
End Sub
To test it:
Sub test()
Const s = "1 2 5to7 9 10to14by2"
Dim dict As Dictionary
Set dict = CreateNumberVector(s)
Debug.Print Join(dict.Keys, ", ")
End Sub
Will print 1, 2, 5, 6, 7, 9, 10, 12, 14 to the immediate window.
Note that the dictionary is unsorted and will spit out the keys in the order they are added, so an Input of "3, 10, 5to7" would result in "3, 10, 5, 6, 7". If you need those sorted, search for "Sorting a Dictionary by Keys".
