regex (vba) - repeat a pattern

My code is:

Dim regEx, retVal
' Create regular expression.
set text = "update my_table      set time4 = sysdate,      randfield7 = 'FAeKE',      randfield3 = 'MyE',      the_field9 = 'test'      WHERE my_key = '37',             tymy_key = 'me';"
Set regEx = CreateObject("vbscript.regexp")
regEx.pattern = ".+where.+ \'(.+)\'+.*;"
regEx.IgnoreCase = True
re开发者_运维技巧gEx.MultiLine = True
regEx.Global = True

Set objRegexMC = regEx.Execute(text)
MsgBox objRegexMC(0).SubMatches(0)

I want it to msgbox 37 and then msgbox me but it only msgboxes me.

---

Sorry, this answer is for Excel, but maybe it'll help put you on the right track. VBA doesn't support lookbehind, but you given the situation, there's a way you can do this (using a substring of the original).

Here is the code. Assuming text was in cell A1, here's what you'd write:

=RegexExtract(RegexExtract(A1,"WHERE(.+)"),"\'(\w+)\'")

It would yield the result: "37, me"

Function RegexExtract(ByVal text As String, _
                      ByVal extract_what As String, _
                      Optional seperator As String = ", ") As String

Application.ScreenUpdating = False
Dim i As Long, j As Long
Dim result As String
Dim allMatches As Object, RE As Object
Set RE = CreateObject("vbscript.regexp")

RE.Pattern = extract_what
RE.Global = True
Set allMatches = RE.Execute(text)

With allMatches
For i = 0 To .Count - 1
    For j = 0 To .Item(j).submatches.Count - 1
        result = result & (seperator & .Item(i).submatches.Item(j))
    Next
Next
End With

If Len(result) <> 0 Then
    result = Right$(result, Len(result) - Len(seperator))
End If

RegexExtract = result
Application.ScreenUpdating = True

End Function

---

You need to make the match non-greedy, like this:

regEx.pattern = "where.+?\'(.+?)\'.+?\'(.+?)\'"