Return a substring containing a certain pattern to VBA
String Operations

regex_search provides various overloads for various input types. You don't need to copy the data into a wstring just to pass it to regex_search. 

Since the return string is only a substring of the original, you can simply construct the BSTR from part of the match value, avoiding the erase calls.

ATL

Microsoft's ATL library provides a lot of helper classes for dealing with COM. They can eliminate a lot of boilerplate code.

Regex Library

I haven't used VBA lately but I expect something as common as regular expressions to already be available. If not, I would probably expose COM classes wrapping regex functionality to VBA and keep the actual expressions in VBA closer to the rest of the business code.