Leverage the .Net framework classes from VBA

Posted on by

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.

9 thoughts on “Leverage the .Net framework classes from VBA”

  1. An alternative to creating a COM wrapper for the .NET regex classes is to use the VBScript regular expression library from VBA (add a reference to Microsoft VBScript Regular Expressions 5.5).

    Reply ↓

    • Thanks for the pointer, it may help VBA users for this particular case.
      But regular expressions were just a pretext to demonstrate the wrapper approach: the .Net Regex API is not trivial but not too complicated either. 🙂

      Reply ↓

    • VBScript Regular Expressions 5.5 is a very poor implementation of regular expressions. It’s missing several common features (lookback springs to mind), but worst of all, it doesn’t handle extended ASCII correctly, never mind Unicode, one result of which is boundaries (\b) are useless in anything but basic ASCII 0–127.

      I was looking exactly for what Pragmateek has laid out here, so a great thank you for saving me from doing it all.

      Reply ↓

  2. As you just mentioned in your last paragraph, I’m one of those who have been doing complex VBA macros for a long time and now wants to leverage processes to .NET, mostly for code reuse and code share with my colleagues. I’m planning to implement libraries with common functionality, but how can I deploy this libraries to my colleagues or other clients? Like, building an .exe I can share with the latests .dlls that will register these dlls in their registers.

    Thanks for your very interesting articles!

    Reply ↓

    • Hello Nelson,
      if your users are on the same network you can simply write a “.bat” script that will:
      – XCopy the binaries from a shared folder to the local machine,
      – Regasm the libraries, as demonstrated in Extend your VBA code with C#, VB.Net or C++/CLI.
      Otherwise you can use a dedicated setup project and define these 2 steps.
      Note that if your users are not administrators of their machine you might have to build the equivalent registration file “yourself” in order to target the “Current User” registry branch.

      Reply ↓

  3. Greetings – thanks for providing this top-notch outline on how to use .Net regex in VBA! For years at work I’ve been limping along in VBScript Regex 5.5, and then a few years back I picked up the Regular Expression Cookbook and learned that .Net had the best lookbehind (positive & negative) speed & functionality – but at that time, it seemed to me to be a rather long runway to learn VS, C#, etc. As you said: “is a simple process, though not trivial.”

    Well today from the C# and VBA code you provided, I have it compiled, registered, and all working nicely on matches, but just now I came to across the need to find and return capture groups along with the matches. I then fired up Claude and after several attempts, it was unable to generate a working dll.

    Being a non-programmer and new to C# & VS, I’m unsure of how to properly wrap the Capture Groups and whether to fold the new group cature code into your code, or proceed with a separate standalone class/dll for the Capture Groups?

    Reply ↓

    • Hello
      My pleasure if I can help.
      Capture groups can be wrapped in a similar manner as the matches:
      using System;
      using System.Linq;
      using System.Runtime.InteropServices;
      using System.Text.RegularExpressions;

      namespace DotNetRegexVBAWrapper
      {
      [ComVisible(true)]
      [Guid("7E7D0933-D832-406A-8632-7FF52B904EB0")]
      public interface IComGroup
      {
      string Value { get; }

      bool Success { get; }
      }

      [ComVisible(true)]
      [Guid("6C25807E-5579-456F-BA24-DA43847ACC04")]
      public interface IComGroupCollection
      {
      int Count { get; }

      IComGroup this[int i] { get; }
      }

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

      bool Success { get; }

      IComGroupCollection Groups { 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("A1FEC6FF-8307-41FC-B896-05F6C72FD3B9")]
      [ClassInterface(ClassInterfaceType.None)]
      public class ComGroup : IComGroup
      {
      public string Value { get; private set; }

      public bool Success { get; private set; }

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

      [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 IComGroupCollection Groups { get; private set; }

      public ComMatch(string value, bool success, GroupCollection groups)
      {
      Value = value;
      Success = success;
      Groups = new ComGroupCollection(groups);
      }
      }

      [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("B3EF9F4F-C8F8-41B4-AF01-44427D6DE3F2")]
      [ClassInterface(ClassInterfaceType.None)]
      public class ComGroupCollection : IComGroupCollection
      {
      public int Count
      {
      get
      {
      return groups == null ? -1 : groups.Length;
      }
      }

      public IComGroup this[int i]
      {
      get
      {
      return groups[i];
      }
      }

      private readonly IComGroup[] groups = null;

      public ComGroupCollection(GroupCollection groups)
      {
      this.groups = groups.Cast()
      .Select(g => new ComGroup(g.Value, g.Success))
      .ToArray();
      }
      }

      [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()
      .Select(m => new ComMatch(m.Value, m.Success, m.Groups))
      .ToArray();

      return new ComMatchCollection(COMMatches);
      }
      }
      }
      Here is a new VBA unit test:
      Sub CanUseDotNetRegexWrapperWithGroups()
      Dim regex As DotNetRegexVBAWrapper.DotNetRegex
      Set regex = New DotNetRegexVBAWrapper.DotNetRegex

      Dim matches As DotNetRegexVBAWrapper.ComMatchCollection
      Set matches = regex.matches("123 456 789", "^(\d+) (\d+) (\d+)$")

      Debug.Assert matches.Count = 1

      Debug.Assert matches(0).Success
      Debug.Assert matches(0).Value = "123 456 789"

      Debug.Assert matches(0).Groups.Count = 4

      Debug.Assert matches(0).Groups(0).Success
      Debug.Assert matches(0).Groups(0).Value = "123 456 789"
      Debug.Assert matches(0).Groups(1).Success
      Debug.Assert matches(0).Groups(1).Value = "123"
      Debug.Assert matches(0).Groups(2).Success
      Debug.Assert matches(0).Groups(2).Value = "456"
      Debug.Assert matches(0).Groups(3).Success
      Debug.Assert matches(0).Groups(3).Value = "789"
      End Sub
      Hope it is working as you expect.

      Reply ↓

      • And a more modern C# version would be:
        using System;
        using System.Linq;
        using System.Runtime.InteropServices;
        using System.Text.RegularExpressions;

        namespace DotNetRegexVBAWrapper
        {
        [ComVisible(true)]
        [Guid("7E7D0933-D832-406A-8632-7FF52B904EB0")]
        public interface IComGroup
        {
        string Value { get; }

        bool Success { get; }
        }

        [ComVisible(true)]
        [Guid("6C25807E-5579-456F-BA24-DA43847ACC04")]
        public interface IComGroupCollection
        {
        int Count { get; }

        IComGroup this[int i] { get; }
        }

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

        bool Success { get; }

        IComGroupCollection Groups { 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("A1FEC6FF-8307-41FC-B896-05F6C72FD3B9")]
        [ClassInterface(ClassInterfaceType.None)]
        public class ComGroup : IComGroup
        {
        public string Value { get; }

        public bool Success { get; }

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

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

        public bool Success { get; }

        public IComGroupCollection Groups { get; }

        public ComMatch(string value, bool success, GroupCollection groups)
        {
        Value = value;
        Success = success;
        Groups = new ComGroupCollection(groups);
        }
        }

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

        public IComMatch this[int i] => matches[i];

        private readonly IComMatch[] matches = null;

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

        [ComVisible(true)]
        [Guid("B3EF9F4F-C8F8-41B4-AF01-44427D6DE3F2")]
        [ClassInterface(ClassInterfaceType.None)]
        public class ComGroupCollection : IComGroupCollection
        {
        public int Count => groups?.Length ?? -1;

        public IComGroup this[int i] => groups[i];

        private readonly IComGroup[] groups = null;

        public ComGroupCollection(GroupCollection groups) =>
        this.groups = groups.Cast()
        .Select(g => new ComGroup(g.Value, g.Success))
        .ToArray();
        }

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

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

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

        return new ComMatchCollection(COMMatches);
        }
        }
        }

        Reply ↓

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove me you\'re human :) *