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.+?\'(.+?)\'.+?\'(.+?)\'"