Regular Expression Pattern Add-In
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Regular Expression Pattern Add-In
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Regular Expression Pattern Add-In

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

    https://skydrive.live.com/?cid=F5B94...770FB324%21105

    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
    Last edited by Firefly2012; Feb 13th, 2012 at 12:32 PM.
    For testing your Regular Expression patterns, try my Regular Expression Pattern Testing add-in!

    For notes on how to use it - see here.

  2. #2
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Regular Expression Pattern Add-In

    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!
    For testing your Regular Expression patterns, try my Regular Expression Pattern Testing add-in!

    For notes on how to use it - see here.

  3. #3
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Regular Expression Pattern Add-In

    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.
    For testing your Regular Expression patterns, try my Regular Expression Pattern Testing add-in!

    For notes on how to use it - see here.

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,060
    Post Thanks / Like
    Mentioned
    82 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Regular Expression Pattern Add-In

    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.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Regular Expression Pattern Add-In

    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.
    For testing your Regular Expression patterns, try my Regular Expression Pattern Testing add-in!

    For notes on how to use it - see here.

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,060
    Post Thanks / Like
    Mentioned
    82 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Regular Expression Pattern Add-In

    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.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #7
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Regular Expression Pattern Add-In

    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!
    For testing your Regular Expression patterns, try my Regular Expression Pattern Testing add-in!

    For notes on how to use it - see here.

  8. #8
    Board Regular Hermanito's Avatar
    Join Date
    Apr 2007
    Posts
    1,238
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Regular Expression Pattern Add-In

    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!
    It is better to remain silent and be thought a fool, than to speak and remove all doubt. (Abraham Lincoln)
    Real programmers don't comment their code. If it was hard to write, it should be hard to understand and even harder to modify. (unknown source)
    My crystal ball broke and my ouija-board is on back-order, so please explain your problem as clear as possible, I don't want to have a seance with my ancestors to know what's not working for you!

  9. #9
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Regular Expression Pattern Add-In

    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
    For testing your Regular Expression patterns, try my Regular Expression Pattern Testing add-in!

    For notes on how to use it - see here.

  10. #10
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Regular Expression Pattern Add-In

    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.
    For testing your Regular Expression patterns, try my Regular Expression Pattern Testing add-in!

    For notes on how to use it - see here.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •