How to create a class in ExcelDna that wraps PCRE or .Net Regex

[Patrick O'Beirne]

Below is a post on the Excel-L List that I am wondering about.

Is there an easy way to create a class in ExcelDna that wraps the Perl
Compatible Regular Expressions or the .Net regex,
so that in VBA we can
Set oRegex = new RegexFromExceLDna
and then any methods or properties invoked are passed on to the XLL?

TIA
Patrick


On 19/06/2015 10:30, John Nurick wrote:
> I hope (unrealistically I fear) that one day someone will make an add-in to
> call either the PCRE or .Net regex engine from VBA. The VBScript regex
> engine is very good as far as it goes, but it's a long long way behind the
> state of the art.

[Govert van Drimmelen]

Hi Patrick,

There are a few Excel-DNA add-ins that expose a regex worksheet function, and those could be called from VBA with Application.Run(...).

But a proper COM class would be a bit nicer, and shouldn't be hard.
You might need to make a little wrapper - the RegEx class is unlikely to work across the COM boundary automatically.

Then to expose the COM class so that you can reference from VBA, use the instructions from Mikael Katajamäki:

http://mikejuniperhill.blogspot.fi/2014/03/interfacing-c-and-vba-with-exceldna-no.html
http://mikejuniperhill.blogspot.com/2014/03/interfacing-c-and-vba-with-exceldna_16.html

-Govert


-----Original Message-----
From: exce...@googlegroups.com [mailto:exce...@googlegroups.com] On Behalf Of Patrick O'Beirne
Sent: 24 June 2015 15:31
To: exce...@googlegroups.com
Subject: [ExcelDna] How to create a class in ExcelDna that wraps PCRE or .Net Regex

Sorry for the wrong subject title last time.

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Patrick O'Beirne]

Thanks, Govert
The first method - no intellisense - looks doable, so it's study time,
to translate that to vb.net.


Best
Patrick

[James Faix]

Somewhat related, I created a worksheet function to check for regex matches.  It takes a string and a regex pattern as input, and returns TRUE if the string matches the pattern.


        [ExcelFunction]
        public static object RegexMatch(
            [ExcelArgument(Name = "Text")] string text,
            [ExcelArgument(Name = "Regex")] string pattern,
            [ExcelArgument(Name = "[Ignore Case]")] object ignoreCase) {

            object arg;
            if (!Optional.TryGet(ignoreCase, true, out arg)) return arg;
           
            var rOptions = (bool)arg
                 ? RegexOptions.None
                 : RegexOptions.IgnoreCase);
           
            return Regex.IsMatch(text, pattern, rOptions);
        }

The method "Optiontal.TryGet" is similar to the one described in the Optional ExcelDNA sample project.

        public static bool TryGet(object o, bool defaultValue, out object result) {
            if (o is ExcelError) {
                result = o;
                return false;
            }
            else if (o is ExcelMissing) {
                result = defaultValue;
                return true;
            }
            else {
                try {
                    result = Convert.ToBoolean(o);
                    return true;
                }
                catch (FormatException) {
                    result = ExcelError.ExcelErrorValue;
                    return false;
                }
            }
        }

[Patrick O'Beirne]

Thanks, James!
P