My problem started out fairly simply, convert a complicated file reader that outputs a spreadsheet into a Excel/VBA application usable by general users. Things never stay that simple for long do they.
My main problem is that I couldn't find any support for Regular Expressions in VBA, none that was sufficient for my needs anyway. So I figured I'd write a dll that did what i needed it to do, import it to Excel and call it within VBA.
So, after reading a few threads on interop and writing dll's to be imported into Excel VBA, I write the following code in C#:
The message I get when I build it is:
Warning 2 "C:\Documents and Settings\s34737\Projects\RegexFunctions\RegexFunctions\bin\Release\RegexFunctions.dll" does not contain any types that can be registered for COM Interop. RegexFunctions
As you can tell i'm not very experienced with this, I simply wanted a dll wrapper that would accept 2 strings, run a Regex on them and return the position of the match as an integer.
Any help would be greatly appreciated!
- Edited by cantpickaname Monday, November 03, 2008 6:16 AM
OK, the examples I found seemed to mark the constructor not the class. I'll move the ComVisible attribute to the class declaration and see if that works.
It didn't work. I still get the same warning whenIi build, and if I try and browse the dll from Excel VBA to add as a reference I get a "Cannot add reference" error.
New code is:
I've marked the build true for "Register for COM Interop".
Any further ideas?
- Edited by cantpickaname Monday, November 03, 2008 11:21 PM
If Excel Smart Tags and VBScript RegExp won't meet your requirements for regular expressions, look at the HelloWorld example in the Interop Forms Toolkit.
Thanks for all the help!
That seems to have fixed my problem.
The reason i want to use a Regex library is that there will be a number of searches done to find values in a rather large text file. The layout of these files may change, so I want to use regex searches to find cell values in the file and pull data based on relatve positions within the file. So you would have a bunch of cell pairs that may look like:
"Transaction Date" 5
The regex will find the text value "Transaction Date" and return its position, add the length of the string then add the value in the second cell and extract a string from that position based on the data type searched for (in this case 10 for a date formatted "10/12/2008"). The VBA code will then translate that to the required data type (or perhaps I'll have the C# add-in do it if thats easier) and place it in a cell.
The goal is to create a customisable import function so if the 'text' markers and positions change, the users can update them in a few cells and modify the import function. I wrote the original translator in C# but when I move on there will be noone else available who could update this tool.