The function you are looking for is Split (this link is for the VB function, but the behavior is virtually the same in VBA). You can pass a specific string to it and specify the delimiter, and it will return an array of each value.
In your case, since your string has multiple delimiters, you'll need to do it multiple times.
First potential issue is what if you do not have a subcategory for a given category? If you will always have at least subcategory for each category in your string, then that's fine, but if there is potentially a case where there is no subcategory, then you need to make sure your highest level grouping is still delimited by a ;.
Since you didn't say how the information is going to get presented to the user, the below example will print something close to what it sounds like you are expecting in the intermediate window in Excel.
Option Explicit
Sub SplitExample()
Dim inputString As String
Dim categories() As String
Dim subCategories() As String
Dim individualSubCat() As String
Dim cat As Variant
Dim subCat As Variant
Dim cnt As Integer
inputString = "1:1i;2:2a;3:3a,3d,3l;4:4a"
categories = Split(inputString, ";")
For Each cat In categories
subCategories = Split(cat, ":")
If UBound(subCategories) >= 0 Then
Debug.Print ("Category " & subCategories(0))
If UBound(subCategories) > 0 Then
individualSubCat = Split(subCategories(1), ",")
Debug.Print (vbTab & "Has " & UBound(individualSubCat) - LBound(individualSubCat) + 1 & " flag(s)")
For Each subCat In individualSubCat
Debug.Print (vbTab & subCat & " was flagged " & CountSubCategory(individualSubCat, subCat) & " time(s)")
Next
Else
Debug.Print (vbTab & "No Subcategories flagged")
End If
Debug.Print ("")
End If
Erase subCategories
Erase individualSubCat
Next
End Sub
This is a function that will count the subcategories easily
Private Function CountSubCategory(individualSubCategories() As String, ByVal subCat As String) As Integer
Dim cnt As Integer
Dim value As Variant
cnt = 0
For Each value In individualSubCategories
If value = subCat Then cnt = cnt + 1
Next
CountSubCategory = cnt
End Function
And using your example string as an input, the above code will print:
Category 1
Has 1 flag(s)
1i was flagged 1 time(s)
Category 2
Has 1 flag(s)
2a was flagged 1 time(s)
Category 3
Has 3 flag(s)
3a was flagged 1 time(s)
3d was flagged 1 time(s)
3l was flagged 1 time(s)
Category 4
Has 1 flag(s)
4a was flagged 1 time(s)
The above code will print every flag, even if there are duplicates. You didn't say whether or not that was desired behavior. Filtering or grouping duplicates from an array is not simple, but is best done with the Collection or Dictionary class in VBA. (Take a look at this question for help filtering duplicates from an array).
The above code is just an example to show what needs done and how to accomplish the parsing (as that was your specific request). To actually make this into working code, you would need to do 2 things:
- Create a Function or Sub in VBA with the code to parse it above (basically what is inside
SplitExample() above) and give it a name (like ParseErrorCodes), and have it accept a string parameter called inputString. You would then call it from the method that builds the string (which you said you already can do) and pass that string to the method.
- Decide how to output the results. Effectively you would replace the
Debug.Print lines with something that will output the results somewhere (presumably to another excel spreadsheet so you can create the chart that you were looking for).
The basic idea would be:
Sub OutputErrorCodes()
Dim inputString as String
' You code to read your string values from where-ever you keep them
' and build the inputString
' this source could be a file, or a worksheet in the Excel Workbook
' or could be an external datasource like a database or even an internet
' location
' once you build your inputString, you just need to call ParseErrorCodes
ParseErrorCodes inputString
End Sub
Sub ParseErrorCodes(input as String)
' MyCode from above with some modifications
' - You need to remove the Dim for inputString and the assignment for
' inputString
' - You need to replace the Debug.Print lines with your method of
' defining the output
' * this can be via outputing directly to an excel spreadsheet or
' maybe a global variable
' * outputing to an excel spreadsheet would probably be the best
' option and allow for more dynamic flags, but you need to decide
' where to output it in the code
End Sub
Private Function CountSubCategory(individualSubCategories() As String,
ByVal subCat As String) As Integer)
' this code can probably be taken directly from my example
End Function
