VBA - Iterate through - Regex - Foreach Key

Question

Referring to this question.

How to iterate for each key like:

        For k = 1 To 40
            If allThings(k) <> "" Then
                Dim rsp As String, ptrn As String, i As Long, arr()
                rsp = [A1]
                ptrn = "" + allThings(k) + """:(\d+[^,])""" 'guid,name,ispool,...

                arr = GetMatches(rsp, ptrn)
                For i = LBound(arr) To UBound(arr)
                    MsgBox arr(i)
                Next
            End If
        Next

Answer 1

As follows, just for the concatenating in new search word into the regex.

You might want to swop the regex line (depending on what characters can appear to something like GetMatches(s, arr2(j) & """:(""?[A-Za-z0-9]+[^,])")

Option Explicit
Public Sub test2()
    Dim rsp As String, i As Long, j As Long, arr2(), arr()

    arr2 = Array("guid", "name", "ispool")
    rsp = [A1]
    For j = LBound(arr2) To UBound(arr2)
        arr = GetMatches(rsp, arr2(j) & """:(""?\w+[^,])")
        For i = LBound(arr) To UBound(arr)
            Debug.Print arr(i)
        Next
    Next
End Sub
Public Function GetMatches(ByVal inputString As String, ByVal sPattern As String) As Variant
    Dim matches As Object, iMatch As Object, s As String, arrMatches(), i As Long

    With CreateObject("vbscript.regexp")
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        .Pattern = sPattern

        If .test(inputString) Then
            Set matches = .Execute(inputString)
            ReDim arrMatches(0 To matches.Count - 1)
            For Each iMatch In matches
                arrMatches(i) = iMatch.submatches.item(0)
                i = i + 1
            Next iMatch
        Else
            ReDim arrMatches(0)
            arrMatches(0) = vbNullString
        End If
    End With
    GetMatches = arrMatches
End Function

---

Regex - Try it here

Example with name

name":("?\w+[^,])
/
gm
name": matches the characters name": literally (case sensitive)

1st Capturing Group ("?\w+[^,]) "? matches the character " literally (case sensitive) ? Quantifier — Matches between zero and one times, as many times as possible, giving back as needed (greedy)

\w+ matches any word character (equal to [a-zA-Z0-9_]) + Quantifier — Matches between one and unlimited times, as many times as possible, giving back as needed (greedy)

Match a single character not present in the list below [^,] , matches the character , literally (case sensitive)

---

Results: