Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Leverage the .NET framework classes from VBA

, 24 Apr 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
Introduction Following my previous article on a similar subject, Extend your VBA code with C#, VB.Net or C++/CLI, I’ve received an interesting feedback from a VBA developer who wanted to leverage the advanced support of the .Net framework for regular … Continue reading →

Introduction

Following my previous article on a similar subject, Extend your VBA code with C#, VB.Net or C++/CLI, I’ve received an interesting feedback from a VBA developer who wanted to leverage the advanced support of the .Net framework for regular expressions.

This is clearly another interesting use-case for Excel addins and in this article I’ll quickly demonstrate how to build a wrapper which will close the gap between your VBA code and the .NET framework.

The .NET regex support

Inside .NET regular expressions are handled by a set of types, the entry-point to this API being the Regex class.

It exposes two main methods:

  • IsMatch that checks whether or not a string matches a pattern,
  • Matches that find all the matches of a pattern in a string.

Wrapping the .NET API

Wrapping the IsMatch method

Wrapping the IsMatch method is quite straightforward as its signature is simple: two strings as input and a boolean as output.

Here is the resulting code:

public bool IsMatch(string input, string pattern)
{
    return Regex.IsMatch(input, pattern);
}

This is method call forwarding in its purest form:

  • take the inputs and transfer them to the underlying component,
  • get the output and send it back to the caller without any transformation

Wrapping the Matches method

Wrapping Matches is not that simple because its output uses a composite type, MatchCollection, which is not COM Visible.

So in order to wrap Matches we’ll first need to wrap the MatchCollection type. And continuing the unwinding process we find that MatchCollection itself uses another non COM type: the Match class, and we will have to wrap it too.

This can look scary at first glance but this is actually a simple process because the deeper you go in the types graph the simpler the underlying types are.

And indeed the Match type exposes the data we are interested in as two simple properties: Value as a string and Success as a boolean.

To be best-practices compliant, for each COM class I’ve created a COM interface that reify the functional contract.

Wrapping the Match class

For the Match‘s COM wrapper the interface is IComMatch:

[ComVisible(true)]
[Guid("A2FD82A6-FC74-45F4-89B8-DF64B1B592B3")]
public interface IComMatch
{
    string Value { get; }

    bool Success { get; }
}

And the implementation of this interface is:

[ComVisible(true)]
[Guid("BF7E950A-83D9-49F0-800A-A9EAC14CFE20")]
[ClassInterface(ClassInterfaceType.None)]
public class ComMatch : IComMatch
{
    public string Value { get; private set; }

    public bool Success { get; private set; }

    public ComMatch(string value, bool success)
    {
        Value = value;
        Success = success;
    }
}

This is a basic wrapper around two values: the match’s value and status.

Wrapping the MatchCollection class

So now that we have wrapped the Match class we can describe our match collection functional contract with the IComMatchCollection interface:

[ComVisible(true)]
[Guid("AF6D05D4-E601-4577-894C-4B1F31A729EC")]
public interface IComMatchCollection
{
    int Count { get; }

    IComMatch this[int i] { get; }
}

I’ve only retained the Count property and the indexer to access the matches.

The implementation is ComMatchCollection:

[ComVisible(true)]
[Guid("6D83BBF0-3429-4DD0-8F84-E89D0DA8AC42")]
[ClassInterface(ClassInterfaceType.None)]
public class ComMatchCollection : IComMatchCollection
{
    public int Count
    {
        get
        {
            return matches == null ? -1 : matches.Length;
        }
    }

    public IComMatch this[int i]
    {
        get
        {
            return matches[i];
        }
    }

    private readonly IComMatch[] matches = null;

    public ComMatchCollection(IComMatch[] matches)
    {
        this.matches = matches;
    }
}

Not rocket science here, but a wrapper around an array of matches.

Putting it all together

Finally we can wrap the whole Matches method:

public IComMatchCollection Matches(string input, string pattern)
{
    MatchCollection matchCollection = Regex.Matches(input, pattern);

    IComMatch[] COMMatches = matchCollection.Cast<Match>()
                                            .Select(m => new ComMatch(m.Value, m.Success))
                                            .ToArray();

    return new ComMatchCollection(COMMatches);
}

It deserves some explanations:

  • the first line is a simple transfer as we’ve done with the IsMatch method, but the output is not COM visible so we’ll transform it
  • the second line uses some LINQ "wizardry" to:
    • transform the MatchCollection instance in an IEnumerable<Match> using the Cast extension method
    • project each Match object to generate a ComMatch object, to obtain an IEnumerable<ComMatch>
    • execute the LINQ request and get an array of ComMatch which is the form used by the ComMatchCollection class
  • the last line creates and returns an instance of ComMatchCollection which can be used from VBA

The wrapper

We finally have all the tools necessary to build the wrapper. We just need to gather them in a COM class which will be the entry-point of the API.

Its functional contract is described with the IDotNetRegex COM interface:

[ComVisible(true)]
[Guid("AE88C353-AA86-4A63-A855-2EF2C1952CA0")]
public interface IDotNetRegex
{
    bool IsMatch(string input, string pattern);

    IComMatchCollection Matches(string input, string pattern);
}

And its implementation is the DotNetRegex class:

[ComVisible(true)]
[Guid("C63DDA96-B8A0-4896-AFAF-FD143274952D")]
[ClassInterface(ClassInterfaceType.None)]
public class DotNetRegex : IDotNetRegex
{
    public bool IsMatch(string input, string pattern)
    {
        return Regex.IsMatch(input, pattern);
    }

    public IComMatchCollection Matches(string input, string pattern)
    {
        MatchCollection matchCollection = Regex.Matches(input, pattern);

        IComMatch[] COMMatches = matchCollection.Cast<Match>()
                                                .Select(m => new ComMatch(m.Value, m.Success))
                                                .ToArray();

        return new ComMatchCollection(COMMatches);
    }
}

All the code

To offer you a global view of the code, also particularly useful if you want to copy-paste it in your own project:

using System;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text.RegularExpressions;

namespace DotNetRegexVBAWrapper
{
    [ComVisible(true)]
    [Guid("A2FD82A6-FC74-45F4-89B8-DF64B1B592B3")]
    public interface IComMatch
    {
        string Value { get; }

        bool Success { get; }
    }

    [ComVisible(true)]
    [Guid("AF6D05D4-E601-4577-894C-4B1F31A729EC")]
    public interface IComMatchCollection
    {
        int Count { get; }

        IComMatch this[int i] { get; }
    }

    [ComVisible(true)]
    [Guid("AE88C353-AA86-4A63-A855-2EF2C1952CA0")]
    public interface IDotNetRegex
    {
        bool IsMatch(string input, string pattern);

        IComMatchCollection Matches(string input, string pattern);
    }

    [ComVisible(true)]
    [Guid("BF7E950A-83D9-49F0-800A-A9EAC14CFE20")]
    [ClassInterface(ClassInterfaceType.None)]
    public class ComMatch : IComMatch
    {
        public string Value { get; private set; }

        public bool Success { get; private set; }

        public ComMatch(string value, bool success)
        {
            Value = value;
            Success = success;
        }
    }

    [ComVisible(true)]
    [Guid("6D83BBF0-3429-4DD0-8F84-E89D0DA8AC42")]
    [ClassInterface(ClassInterfaceType.None)]
    public class ComMatchCollection : IComMatchCollection
    {
        public int Count
        {
            get
            {
                return matches == null ? -1 : matches.Length;
            }
        }

        public IComMatch this[int i]
        {
            get
            {
                return matches[i];
            }
        }

        private readonly IComMatch[] matches = null;

        public ComMatchCollection(IComMatch[] matches)
        {
            this.matches = matches;
        }
    }

    [ComVisible(true)]
    [Guid("C63DDA96-B8A0-4896-AFAF-FD143274952D")]
    [ClassInterface(ClassInterfaceType.None)]
    public class DotNetRegex : IDotNetRegex
    {
        public bool IsMatch(string input, string pattern)
        {
            return Regex.IsMatch(input, pattern);
        }

        public IComMatchCollection Matches(string input, string pattern)
        {
            MatchCollection matchCollection = Regex.Matches(input, pattern);

            IComMatch[] COMMatches = matchCollection.Cast<Match>()
                                                    .Select(m => new ComMatch(m.Value, m.Success))
                                                    .ToArray();

            return new ComMatchCollection(COMMatches);
        }
    }
}

Quite light, but this is expected for a wrapper, which is just an intermediate plumbing.

To register it, e.g. when rebuilding the project from Visual Studio, you’ll need to be administrator so start Visual Studio accordingly, or if you don’t use Visual Studio but the command line run regasm in an elevated command prompt.

Using it from VBA

As for any COM addin the first step is to reference it in your VBA project: you can follow the instructions of the previous article but this time searching for an addin named "DotNetRegexVBAWrapper" instead of "FinanceCPP", "FinanceCS" or "FinanceVBNet".

To demonstrate the use of the wrapper and check that it works as expected I’ve written a small "unit test" in VBA:

Sub CanUseDotNetRegexWrapper()
    Dim regex As DotNetRegexVBAWrapper.DotNetRegex
    Set regex = New DotNetRegexVBAWrapper.DotNetRegex
    
    Dim isMatching As Boolean
    isMatching = regex.IsMatch("123", "^\d\d\d$")
    
    Debug.Assert isMatching
    
    isMatching = regex.IsMatch("1234", "^\d\d\d$")
    
    Debug.Assert Not isMatching
    
    Dim matches As DotNetRegexVBAWrapper.ComMatchCollection
    Set matches = regex.matches("abc1def2ghi3", "[a-z]{3}")
    
    Debug.Assert matches.Count = 3
    
    Debug.Assert matches(0).Success
    Debug.Assert matches(0).Value = "abc"
    
    Debug.Assert matches(1).Success
    Debug.Assert matches(1).Value = "def"
    
    Debug.Assert matches(2).Success
    Debug.Assert matches(2).Value = "ghi"
End Sub

It should run without any issue.

Conclusion

As you see wrapping some .Net components to make them available to your VBA code is a simple process, though not trivial. And often building a wrapper will cause you less trouble than finding an existing VBA component, understanding how it works and integrating it, without having the assurance that it works as advertised.

If you’re not a developper, but a power-user that build complex VBA macros I highly encourage you to take the plunge and consider incorporating .Net in your development processes. You’ll have access to a great world, and the effort is definitely worth the pain.

If you catch any typo or mistake, have additional questions, some remarks, feel free to let a comment.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Pragmateek
Instructor / Trainer Pragmateek
France (Metropolitan) France (Metropolitan)
To make it short I'm an IT trainer specialized in the .Net ecosystem (framework, C#, WPF, Excel addins...).
(I'm available in France and bordering countries, and I only teach in french.)
 
I like to learn new things, particularly to understand what happens under the hood, and I do my best to share my humble knowledge with others by direct teaching, by posting articles on my blog (pragmateek.com), or by answering questions on forums.
Follow on   Twitter   LinkedIn

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.141022.2 | Last Updated 24 Apr 2014
Article Copyright 2014 by Pragmateek
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid