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.
