none
Create a C# dll to use in Excel VBA

    Question

  • Hello all!

    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#:

    using System;  
    using System.Collections.Generic;  
    using System.Runtime.CompilerServices;  
    using System.Runtime.InteropServices;  
    using System.Text;  
    using System.Text.RegularExpressions;  
     
    namespace RegexFunctions  
    {  
        [ComSourceInterfaces(typeof(IMyControlCOMEvents))]  
        public class RegexFunctionsMain  
        {  
            [ComVisible(true)]  
            private string myString;  
            private string MyPattern;  
     
            public string mypattern  
            {  
                get { return MyPattern; }  
                set { MyPattern = value; }  
            }  
          
            public string mystring  
            {  
                get { return myString; }  
                set { myString = value; }  
            }  
     
            RegexFunctionsMain(string source, string pattern)  
            {  
                mystring = source;  
                mypattern = pattern;  
            }  
            [InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]  
            public interface IMyControlCOMEvents  
            {  
                [DispId(0x60020000)]  
                int ReturnPosition();  
            }  
            public int ReturnPostion()  
            {  
                Regex reg = new Regex(mypattern);  
                int pos = reg.Match(mystring).Index;  
                return pos;  
            }  
        }  
    }  
     


    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!

    Cheers
    Brian
    Monday, November 03, 2008 6:15 AM

Answers

  •  
    using System;  
    using System.Collections.Generic;  
    using System.Runtime.CompilerServices;  
    using System.Runtime.InteropServices;  
    using System.Text;  
    using System.Text.RegularExpressions;  
     
    namespace RegexFunctions  
    {  
        public interface IRegexFunctionsMain  
        {  
            int ReturnPostion(string myString, string myPattern);  
        }  
     
        public class RegexFunctionsMain : IRegexFunctionsMain  
        {  
            public RegexFunctionsMain()  
            {  
            }  
     
            public int ReturnPostion(string myString, string myPattern)  
            {  
                Regex reg = new Regex(myPattern);  
                int pos = reg.Match(myString).Index;  
                return pos;  
            }  
        }  

    Sub TestIt()  
        Dim rgx As RegexFunctionsMain.RegexFunctionsMain  
          
        Set rgx = New RegexFunctionsMain.RegexFunctionsMain  
          
        Debug.Print rgx.ReturnPostion("a""b")  
    End Sub 
     

    VSTO Rocks!
    • Marked as answer by cantpickaname Wednesday, November 05, 2008 12:38 AM
    Tuesday, November 04, 2008 4:23 AM

All replies

  • forgot to mark your RegexFunctionsMain class as "ComVisible"?
    VSTO Rocks!
    Monday, November 03, 2008 8:46 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.
    [EDIT]

    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:

    using System;  
    using System.Collections.Generic;  
    using System.Runtime.CompilerServices;  
    using System.Runtime.InteropServices;  
    using System.Text;  
    using System.Text.RegularExpressions;  
     
    namespace RegexFunctions  
    {  
        [ComSourceInterfaces(typeof(IMyControlCOMEvents))]  
        [ComVisible(true)]  
        public class RegexFunctionsMain  
        {    
            private string myString;  
            private string MyPattern;  
            public string mypattern  
            {  
                get { return MyPattern; }  
                set { MyPattern = value; }  
            }  
            public string mystring  
            {  
                get { return myString; }  
                set { myString = value; }  
            }  
     
            RegexFunctionsMain(string source, string pattern)  
            {  
                mystring = source;  
                mypattern = pattern;  
            }  
            [InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]  
            public interface IMyControlCOMEvents  
            {  
                [DispId(0x60020000)]  
                int ReturnPosition();  
            }  
            public int ReturnPostion()  
            {  
                Regex reg = new Regex(mypattern);  
                int pos = reg.Match(mystring).Index;  
                return pos;  
            }  
        }  

    I've marked the build true for "Register for COM Interop".

    Any further ideas?

    Cheers
    Brian
    Monday, November 03, 2008 11:07 PM
  •  
    using System;  
    using System.Collections.Generic;  
    using System.Runtime.CompilerServices;  
    using System.Runtime.InteropServices;  
    using System.Text;  
    using System.Text.RegularExpressions;  
     
    namespace RegexFunctions  
    {  
        public interface IRegexFunctionsMain  
        {  
            int ReturnPostion(string myString, string myPattern);  
        }  
     
        public class RegexFunctionsMain : IRegexFunctionsMain  
        {  
            public RegexFunctionsMain()  
            {  
            }  
     
            public int ReturnPostion(string myString, string myPattern)  
            {  
                Regex reg = new Regex(myPattern);  
                int pos = reg.Match(myString).Index;  
                return pos;  
            }  
        }  

    Sub TestIt()  
        Dim rgx As RegexFunctionsMain.RegexFunctionsMain  
          
        Set rgx = New RegexFunctionsMain.RegexFunctionsMain  
          
        Debug.Print rgx.ReturnPostion("a""b")  
    End Sub 
     

    VSTO Rocks!
    • Marked as answer by cantpickaname Wednesday, November 05, 2008 12:38 AM
    Tuesday, November 04, 2008 4:23 AM
  • 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.
    • Marked as answer by cantpickaname Wednesday, November 05, 2008 12:37 AM
    • Unmarked as answer by cantpickaname Wednesday, November 05, 2008 12:37 AM
    Tuesday, November 04, 2008 4:47 PM
  • 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.
    Wednesday, November 05, 2008 12:37 AM