Creating a macro using the RegEx Find/Replace add-in

[bauman312]

Hello all,

I'm trying to record a macro that uses the RegEx Find/Replace add-in (downloaded from http://www.codedawn.com/excel-add-ins.php). Unfortunately, the macro does not pick up any of the add-in's operations, even when I add Microsoft VBScript Regular Expressions 5.5 as a VBA reference. Does anyone know how I can go about using an add-in within a Macro recording? I would like to learn enough VBA to figure out how to incorporate regular expressions into the code, but I'm a little pressed for time with this project.

[Chippy]

I downloaded and installed that add-in but got errors when opening and closing Excel: System error, and Compile error in hidden module ThisWorkbook. Looking at its help, I think you are meant to use its functions in cell formulae, e.g. =RegExFind(...). The macro recorder should pick up the functions if you type them in during the recording.

In VBA, although I haven't been able to test this, you should be able to call its functions like this:

x = Application.WorksheetFunction.RegExFind(...)

You don't need to set a project reference to Microsoft VBScript Regular Expressions 5.5 to use the add-in's functions.

For a very simplified alternative to regexes, have a look at the Like operator.

[bauman312]

Thanks for your insight, Chippy.
Some of the comments on the CodeDawn website indicated that you'll probably receive those kinds of error messages if you're using Excel 2010 64-bit version.
I'll see what I can do based on your suggestions.