CODEWITHSUNDEEP
regexvba
Jonathan Mee
Jonathan Mee

Reputation: 38949

From MatchCollection to Array or Pair of Values

Is there a way to put regex captures directly into an array without the intervening MatchCollection?

I want something like: Set myArray = myRegEx.Execute(myString)(0).SubMatches

Or at a minimum, if I knew the number of captures that I could "tie" the return values: (myFirst, mySecond) = myRegEx.Execute(myString)(0).SubMatches

I know that use of SubMatches is made up, I'm just trying to find a way to accomplish the circumvention of the intervening MatchCollection.

Upvotes: 2

Views: 1462

Answers (1)

David Zemens
David Zemens

Reputation: 53653

OK, maybe this will get you started.

From Outlook macro to Excel macro is not something I would do. Instead, I would recommend binding one application to the other and doing whatever you need to do with both object model references exposed to the VBProject.

But in any case you can do it the way you describe, and this should be an example.

This example assumes that Excel is already open, and further that the Workbook which contains the macro is also open in that instance of Excel. In Excel I create a simple procedure which accepts a generic Object argument. I do this to avoid needing the explicit reference tot he Microsoft VBScript Regular Expressions library.

This way, you have a macro in Excel which accepts (requires, actually) an object variable. In this case it is going to be a SubMatches object. (Make sure to change "Book9" to the name of your workbook, or modify as needed to allow user to select/open a workbook, etc.)

Sub excelmacro(SM As Object)

    MsgBox SM.Count & " submatches"

End Sub

Now, I have a very simple Outlook procedure to test this and verify it works. In this case there will be no submatches, so the Excel procedure above will display a messagebox 0 submatches.

Sub test_to_Excel()
'### Requires reference to Microsoft VBScript Regular Expressions 5.0 ###
Dim re As New RegExp
Dim mySubmatches As SubMatches
Dim xl As Object 'Excel.Application
Dim wb As Object 'Excel.Workbook

With re
    .Global = True
    .Pattern = "asd"
    '## Now get a handle on the particular indexed match.submatches() 
    Set mySubmatches = .Execute("asdfkjasdfj; asdl asdfklwedrewn adg")(1).SubMatches
End With

'## Now we can send to Excel procedure:
'## Assumes Excel is already running and the file which contains the macro
'   is already open
Set xl = GetObject(, "Excel.Application")
Set wb = xl.Workbooks("Book9")

'## This tells the Excel application to run a named procedure
'   and passes the variable argument(s) to that procedure
xl.Application.Run "excelmacro", mySubmatches

End Sub

Upvotes: 1

Related Questions