Regular Expression Pattern Add-In

[Firefly2012]

For those that enjoy playing with Regular Expressions, I hope you find the following Add-In useful:

https://skydrive.live.com/?cid=F5B94C37770FB324&id=F5B94C37770FB324!105

Download it to a location of your choice and install it in your Excel via Office button/File Ribbon>Options/Excel Options>Add-Ins>Manage Add-Ins.

How to use it

1. It adds an additional option to the right-click Cell menu (that should appear at the bottom) named "RegExp Tester".
2. Clicking on this will load a userform. The contents of the active cell are loaded into the large text box and you can also edit this text as you require, or clicking on another cell will load the contents of that cell to the box.
3. The above is the text you want to match against. The pattern to match with goes into the smaller textbox at the top. As soon as you start typing in a pattern, the match will be displayed in the larger text box in bold red underlined text.
4. There are 3 vbscript regular expression settings which can be made via checking the checkboxes on the right hand side. Please note that Multiline is an undocumented property of the vbscript regexp object.
5. There is a "Copy Pattern To Clipboad" button which copies the entered pattern to the clipboard.
6. Finally there is a further checkbox "Disable Match Text update" whose purpose is simply to disable automatic loading of a newly selected cell's contents to the larger textbox (this is useful in case you want to copy a pattern to the clipboard and enter it into a blank cell without losing the contents of the larger textbox).


Points to note

I have only tested this on xl2010. I have no other versions until I return to work to test against.

The userform contains Microsoft's InkEdit control. I have no idea how compatible this is with other Excel versions.

The userform uses Andy Pope's form resizing code (thank you Andy!) to permit dynamic resizing when the Userform has been loaded.

Please post any questions into this thread - thanks.

Hope it proves useful

 

[Hermanito]

When I responded earlier, I was thinking of including the given testpattern from the userform as a constant inside the function. But thinking a bit more shows this is a bad idea.
The function snippet should be as generic as possible, to promote easy reuse. So a generic function with 2 parameters: text to search and pattern to match, should do the trick. That part will always be the same. The variable part of the code output could then be a simple function calling the generic function with the values from the userform as calling parameters.
The generic function could be provided in two or more flavors: returning a bool to indicate a match, returning a string to return the first match (or maybe even return an array of strings, with all matches), or performing a substitution (is that supported in the library that you refer to?).

At this rate, you'll be matching Google Chrome's version number quite soon
If I can help you with anything, let me know, but you'll probably manage

 

[Firefly2012]

Right, interesting properties about InkEdit controls:

1. Assigning text (eg from a cell) containing a single linefeed will result in the InkEdit holding text with a CarriageReturn-LineFeed combination (vbCrLf)
2. The Text property of an InkEdit does return the text string inclusive of the vbCrLf
3. The SelText property however returns only the CarriageReturn and not the LineFeed
4. SelStart and SelLength properties reflect SelText (so no LineFeed, only the CarriageReturn)

Originally, I was matching against the Text property of the InkEdit. I have now amended the code to match against the full SelText instead, thereby avoiding a mismatch when the SelStart and SelLength properties are used to apply formatting to the matches.

This means that text including linefeeds and/or carriage returns should now be properly formatted with the pattern matches. However, patterns containing linefeed matches themselves may well not work (due to the substitutions). This is a limitation I can live with.

Latest version now is appended with _4.

 

[JP2112]

Tested in Excel 2003, it works.

I like it, but I don't like the red underlined text. If there's a match, it should be bold or green, or simply output a message stating whether the input text matched the pattern.

 

[Firefly2012]

Hi JP2112

Thanks for the feedback!

I'll have to disagree with you there - I like the fact it's red 'cos at least I can see it clearly (maybe I'm a lot older than you with poorer eyesight!).

In principle, it would be straightforward to make it green (or another colour):

'line160 in the userform code module:
 
.SelColor = RGB(0, 200, 0)
 
'if you change to the above, the match will be highlighted in Green

The underlining is needed as if the pattern matches spaces, there isn't another way to indicate these are matched (as there is no visible character to colour).

The reason i wanted to write this add-in is so that the pattern matching would be dynamic as the patter was typed - I have seen a few (non-Excel) apps for Regex that rely on a button being clicked to applky the pattern and this I didn't want to do. There needs to be feedback to the user as to what has matched to his/her pattern.

 

[Peter_SSs]

I agree with Firefly in relation to the underlining. It could be quite confusing if spaces are involved and they are not identified in some way.

If there's a match ... simply output a message stating whether the input text matched the pattern.

I also disagree with this. If there are matches, I want to see where they are, particularly if the 'Global' setting is on.

 

[Firefly2012]

Minor update to version 5 (RegExTester_5.xlam) which now includes a checkbox option to cycle the match colours (so that individual matches are coloured in a sequence of Red then Green then Blue to clearly identify different matches).

 

[pbornemeier]

@FireFly2012: Is the Regular Expression Pattern Testing add-in still available somewhere? The link in the sig did not cooperate.

 

[VoG]

Hi. I don't think that Firefly visits any more but the link in his sig seems to work.

 

[pbornemeier]

Skydrive was blocked where I was originally trying to access the link. Got the add-in now. Thanks