# Regular Expression Pattern Add-In



## Firefly2012 (Feb 13, 2012)

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 (Feb 13, 2012)

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 (Feb 13, 2012)

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 (Feb 14, 2012)

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 (Feb 14, 2012)

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 (Feb 18, 2012)

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 (Feb 19, 2012)

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 (Feb 19, 2012)

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 (Feb 19, 2012)

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 (Feb 19, 2012)

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.


----------



## Firefly2012 (Feb 13, 2012)

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 (Feb 19, 2012)

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 (Feb 19, 2012)

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 (Mar 16, 2012)

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 (Mar 16, 2012)

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 (Mar 16, 2012)

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.



JP2112 said:


> 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 (Apr 1, 2012)

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 (Aug 27, 2014)

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


----------



## VoG (Aug 27, 2014)

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


----------



## pbornemeier (Aug 29, 2014)

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


----------

