I've written in a C++ dll the following function to return to VBA a substring containing a certain pattern, given an input string, which contains that substring:

VARIANT _stdcall ReturnT(VARIANT FileName)

    wstring str(FileName.bstrVal, SysStringLen(FileName.bstrVal));
    wregex ToFind(L"_[tT]\\d{2,3}(_|.)");
    wsmatch TStr;

    regex_search(str, TStr, ToFind);
    wstring TVal = TStr.str(0).erase(0, 2);
    TVal.erase(TVal.end() - 1);

    BSTR bs = SysAllocStringLen(TVal.data(), TVal.size());

    VARIANT TValue;
    TValue.vt = VT_BSTR;
    TValue.bstrVal = bs;

    return TValue;

This works flawlessly, but I suspect I am doing something more complicated than it really should be.

Notice that I'm using VARIANT to avoid interop problems ( BSTR as input would return kanjis, and in output would return a string with spaces between the characters)


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.


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.