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

 

[Firefly2012]

I should have said: I would appreciate any and all feedback from you guys. It has only had a limited development cycle and there are no doubt loads of bugs that need clearing.

Thanks!

 

[Firefly2012]

Right, there's definitely an issue with line feeds and carriage returns. The InkEdit control appears to parse either of these as vbCrLf (ie two characters rather than just one). This has some annoying consequences. The SelStart property of the InkEdit appears to ignore embedded vbCrLf. Any thoughts on why this might be the case would be appreciated.

 

[Peter_SSs]

I certainly have an interest in regular expressions, though definitely do not consider myself an expert. I have installed the Add-In and will give it a more of a try in due course.

I like the concept because I am not great at quickly developing the correct pattern and this should quickly show me if I am on the right/wrong track.

Having not even tried it to any extent yet, maybe I'm a bit early early for feedback but here goes:

I can imagine a benefit to me in being able to select a range of say 3(?) cells and have the contents of all 3 loaded into 3 different 'Text to match against' boxes so you could test your pattern against a few different strings at the same time.

Anyway, thanks for sharing it.

 

[Firefly2012]

Hi Peter

Thanks for the feedback. Having multiple boxes is an interesting idea (and should be possible on the fly) and don't think it should hit performance too much.

I do need to resolve the linefeeds/carriage returns issues. It is beginning to irritate me.

 

[Peter_SSs]

Sort of related to my previous point. I generally want to test my pattern against several strings (to check both matches and non-matches).

So I have followed steps 1-3 in your OP and I'm happy with my pattern testing on that cell so I now click on another cell I want to check my pattern against. That cell's contents is loaded into the bottom box, but my pattern doesn't seem to be automatically tested on that new string. I seem to have to go in to my pattern, change it and then change it back to what it was to see the test results. If that is so, it makes it quite cumbersome to test a pattern on a series of cells.

 

[Firefly2012]

Thanks for keeping the feedback coming Peter - I have uploaded a new add-in (with a _2 appended on the end - but it is the only file available on the skydrive account).

This deals with some of the issues (specifically the one you brought up in post #6). I have changed the way the add-in works to remove most of the coding in InkEdit1_Change (it kept repeatedly firing and after a rethink I decided it just wasn't required).

Making changes directly to the InkEdit now doesn't update the formatting - this is updated when the inkedit is Exited or when the mouse cursor moves off of the inkedit. This definitely improves the user experience as far as I can see.

I think it now works fine so long as there are no carriage returns/line feeds in the text. I need to figure out how to deal with these now.

Please let me know if the update doesn't work for you or has thrown up additional issues - thanks!

 

[Hermanito]

Hey there FireFly,

just tested it in xl2007 and that seems to work just fine.
I like the concept, and I can sure use it sometimes, but what would really make it great (imho), is that it could either
- auto-create a module in a certain VBA project, containing all necessary code to implement the pattern you just tested (ambitious full option version of my request )
- place a basic codesnippet in the copybuffer, that can then manually be pasted anywhere in a VBA-project.

As codesnippet I would like a function that takes a string as parameter and returns the (first) match of the tested pattern. That way I will no longer need to look up the syntax every time I use regexp in VBA. You could even provide two versions: an early-binding version (like you use in your own code), or a late-binding one (which doesn't need the reference being set).

Keep up the good work!

 

[Firefly2012]

Hi Hermanito

I really like those suggestions! The first one is obviously more complex and, personally, i think placing the code within the clipboard is the option I would rather include (avoids any issues with trusting access to the VB project). That definitely sounds like a future build - I could include baisc function options to either identify a pattern (simple boolean result), return first match (as you suggested), or perform a substitution.

Excellent - thank you very much

 

[Firefly2012]

Minor further update - the inkedit text was wholly selected upon leaving the inkedit if there was a blank pattern in the pattern textbox. This has now been prevented from happening. Now up to version 3 on the skydrive account.