Sometimes my mind is overactive and I can't sleep until I nail what's bugging me. Tonight it's this Add-in I'm creating for Microsoft Excel.
I have an SQL server pre-processing datasets and a script that converts them into Advanced Table Datagrams (classic ADO ATDG format, which is a binary form of an XML file).
My add-in reads those files from a local hard drive (so that it doesn't have to work over a wide-area network) and scans cells in the workbook for three different types of items of interest. There may be multiple items of interest in a single cell, so it has to look at each one of them, and check for their existence in these "persisted data sets".
Well..I need a way to determine only what is unique in each cell when I scan it. Validate one item in a cell, and all the other copies of it are still the same item.
I'm using the VBScript Regular Expression 5.5 library to do this in just a few lines of code. I'm not using a hash table or building a keyed Collection object and discarding objects that collide on attempted insertion. My RegEx pattern simply matches all items of interest, but returns only the last of every unique item. In the spreadsheet, I change the color of the *first occurance* of each item and ignore the rest (I use a different color for type of item).
Fields from the associated datasets on disk are referenced to print a plain-text report to the copy buffer, which can then be pasted into a document (e.g. email) by pressing CTRL-V.
This saves me the overhead of creating yet another object in memory and enumerating the members in it for validation (= performance boost).
Here's the string I'm using for those who are interested (the key is that I'm assigning the 'Worm' value to my pattern, and then using the ?!.+ operators to inform the RegEx engine that a given word followed later by another instance of the same word is to be ignored. This means that only the last unique words of all that match in the string will be returned! Walla!
(?b(?=w)[a-zA-Z0-9_]{6}b(?!w)|b(?=w)[a-zA-Z0-9_]{9}b(?!w))(?!.+bkb)
