Optional passed by reference parameters with C# for VBA COM APIs

Introduction

If you’ve already developed COM APIs with .Net, typically in C# with VBA as the consuming language, you’ve probably leveraged two powerful features:

  • by-reference parameter passing that allows the API to change the input object itself, not only its content.
    This is marked with ref in C#:

    void LoadData(ref string data)
  • optional parameters that allow the caller to omit them when calling into the API:
    void SaveData(string data = "DEFAULT")

Sometimes you want to have both for a parameter: the ability to omit it and to change it.
This is something COM supports but unfortunately this is not supported by C#.
Recently one of my customer needed precisely that for a COM API developed in C# migrated from VB6 where it was plug-and-play.
The original developers of the API had done a quick study and concluded that it was not possible with the new C# API and indeed it was not documented at all.
From experience I know that COM is not used a lot today and that resources are scarce, so, to be sure, I’ve done my due diligence and reexamined the issue, digging a little deeper and testing undocumented stuff.
I’ve finally found a solution that I would describe as a “trick”, because it’s not documented, and that I’ll present in detail in this article.
Continue reading

Leverage the .Net framework classes from VBA

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.

Continue reading