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.

Some background

From source languages to .Net

When you develop on the .Net platform all your source code (be it C#, VB.Net, C++/CLI, F#…) is compiled by your language compiler to a common binary intermediate language, a byte-code, called CIL, which is itself compiled at runtime (or before but that’s another story) to native binary code by the virtual machine, the CLR (more precisely by its own native compiler the JITter).
So each language structure, like optional parameters, is mapped to a common format.
So when you use C# as your source language and define optional parameters with a default value, the C# compiler (CSC) will translate them into something usable from other .Net components that may not have been developed in C#: some metadata expressed as what is called .Net attributes.
More concretely when you write this in C#:

void SaveData(string data = "DEFAULT")

the C# compiler will generate this:

.method public hidebysig instance void
        SaveData([opt] string data) cil managed
{
  .param [1] = "DEFAULT"

(more exactly the binary form of this)
The nature of the data parameter is specified through 2 metadata:
[opt] that marks the parameter as optional,
.param that stores the default value used when the parameter is not provided
These metadata can be interpreted by many tools, like compilers.

Directly specifying the metadata into the source code

The .Net class library allows us to directly specify these metadata without using specific language construct:
[opt] via the [OptionalAttribute] attribute
.param via the [DefaultParameterValueAttribute] attribute
Both these attributes are in the System.Runtime.InteropServices namespace.
Using them we can obtain exactly the same .Net CIL code as before without using C# constructs:

void SaveData2([Optional] [DefaultParameterValue("DEFAULT")] string data)

The result is the same as above:

.method public hidebysig instance void
        SaveData2([opt] string data) cil managed
{
  .param [1] = "DEFAULT"

This is more verbose and less readable so you would not use it in standard situations where your source language provides syntactic sugar like C# does.

Tricking C#

Where it becomes interesting is that as you don’t use the C# language constructs you are no more constrained by them.
In our case we can get rid of the C# limitation concerning optional passed by reference parameters and write something like:

public static string MirrorOrDefault([Optional] [DefaultParameterValue("DEFAULT")] ref string data)
{
    return data;
}

which is equivalent to:

public static string MirrorOrDefault(ref string data = "DEFAULT")

which is illegal in C# (at least in current versions).
If this method is called from C# you’ll always have to pass the parameter explicitly, defeating the purpose of the trick.
But if called from another environment it will allow you to have optional passed-by-reference parameters.
Here is a sample in VB.Net that illustrates this ability:

Imports System

Class Program
	Shared Sub Main()
		Dim result As String
		result = OptionalByRefInCS.MirrorOrDefault()
		
		Console.WriteLine(result)
	End Sub
End Class

The calling code doesn’t specify the data parameter.
Executing this produces this output:

DEFAULT

So the once C# code has been called with the default parameter value, “DEFAULT”, it has returned to the caller.
We’ve bypassed the C# limitations by working directly at the .Net level with attributes.

Application to VBA COM APIs

COM and .Net

The above paragraph has illustrated a direct .Net component (written in VB.Net) to .Net component (written in C#) communication that only used the .Net plumbing.
So the .Net metadata generated by the C# compiler were consumed by the VB.Net compiler.
But when calling into .Net from outside .Net, like from VBA with a shared addin, we have another layer of infrastructure to consider: COM.
Ans as COM comes with its own way of representing types, their methods and their parameters, including optional ones, we need to speak its language.
Fortunately the tools provided by .Net to work with COM, like RegAsm and TLBExp, are able to interpret the .Net metadata to convert them into COM metadata that will themselves be consumed by the VBA infrastructure.
All these COM metadata are stored in dedicated files: type libraries a.k.a. TLBs.

A C# COM API

To illustrate all these points we’ll build a simple C# API that will be consumed by VBA through COM.

For a detailed introduction to COM addins in .Net read this article: Extend your VBA code with C#, VB.Net or C++/CLI.



Here is the C# source code:

using System; // Random
using System.Runtime.InteropServices; // Guid, ClassInterface, ClassInterfaceType

[Guid("88FF845D-5283-4612-9F4B-1B8C5286A5DF")]
public interface IRainbowSource
{
    string NextColor([Optional] [DefaultParameterValue("Red")] ref string color);
	
    int CodeOf(string color);
}

[Guid("891CCD91-00F6-4C7C-86C0-8B8AF6C599B0")]
[ClassInterface(ClassInterfaceType.None)]
public class RainbowSource : IRainbowSource
{
	private static string[] colors = { "Red", "Orange", "Yellow", "Green", "Blue", "Indigo", "Violet" };
	
	private static int[] codes = { 3, 45, 6, 10, 41, 55, 13 };
	
	public string NextColor(ref string color)
	{
		string originalColor = color;
		
		int currentColorIndex = Array.IndexOf(colors, color);
		
		int nextColorIndex = (currentColorIndex + 1) % colors.Length;
		
		color = colors[nextColorIndex];
		
		return originalColor;
	}
	
	public int CodeOf(string color)
	{
		int colorIndex = Array.IndexOf(colors, color);
		
		return codes[colorIndex];
	}
}

Don’t be distracted by the COM metadata and refer to the article mentioned above to fully understand them.
The important part concerning our point is this method:

string NextColor([Optional] [DefaultParameterValue("Red")] ref string color);

It uses the trick demonstrated above, but this time in an interface instead of a class.

Let’s compile it:

csc /target:library UnicornAPI.cs
Microsoft (R) Visual C# Compiler version 4.0.30319.34209
for Microsoft (R) .NET Framework 4.5
Copyright (C) Microsoft Corporation. All rights reserved.

And register it for COM interop:

regasm /tlb /codebase UnicornAPI.dll
Microsoft .NET Framework Assembly Registration Utility version 4.0.30319.34209
for Microsoft .NET Framework version 4.0.30319.34209
Copyright (C) Microsoft Corporation.  All rights reserved.
...

Now let’s check the COM metadata generated by RegAsm (I’ve used OleView):

[id(0x60020000)]
HRESULT NextColor([in, out, optional, defaultvalue("Red")] BSTR* color, [out, retval] BSTR* pRetVal);

A bit verbose but we can see the essential for the color parameter:

  • it is optional,
  • its default value is “Red”.

Using the API from VBA

Here is what the VBE object browser has to say about the NextColor method:

Function NextColor([color As String = "Red"]) As String
    Member of UnicornAPI.RainbowSource

So the color parameter is correctly identified as optional with a default value of “Red” which is the value VBA will pass if we don’t provide any.
And this is confirmed by VBE auto-completion if we start to write the code to call it:

VBA completion

VBE completion


Finally here is a VBA sample code that leverages the API to demonstrate the behavior of the NextColor method:

Sub MakeRainbows()
 Dim color As String
 Dim source As New UnicornAPI.RainbowSource
 color = source.NextColor
 Dim i As Long
 For i = 1 To 20
   Range("A1").Offset(i - 1).Interior.ColorIndex = source.CodeOf(color)
   Call source.NextColor(color)
 Next i
End Sub

We get the expected behavior:

  • color = source.NextColor: if we don’t specify the parameter we get the default color “Red”,
  • Call source.NextColor(color): if we pass a color it is updated with the next color.

Some words of caution

Be cautious when your code relies on the default value of the type.
Indeed the default value you get if not specified in your C# source code through the [DefaultParameterValue] attribute may not be what you expect.
e.g. with a DateTime you won’t get the .Net DateTime default value 0001-01-01 but the VBA default value 1899-12-30 because, as in C#, this is the caller that sends the default value.
And AFAIK there is no way to get around it because you can’t specify the default value for a DateTime and can’t use a nullable DateTime (a.k.a. DateTime? in C#, a.k.a. Nullable<DateTime> in .Net) because it is a generic type, which is not supported by COM.
So you must take care of mapping the VBA default value to a meaningful value for the rest of your code down the pipeline:

public DateTime NextDate(ref DateTime date)
{
    DateTime originalDate = date != new DateTime(1899, 12, 30) ? (DateTime)date : DateTime.Today;
		
    date = originalDate.AddDays(+1);
		
    return originalDate;
}

The problem is if the default VBA value can be a meaningful value for your function, so that you are not able to tell if the user has not passed anything or has passed the VBA default.
AFAIK there is only one simple solution (without tweaking the IDL): using a Variant/Object instead of a DateTime.
In the function you can check for the special value provided when the user has not provided the value: Type.Missing (a.k.a System.Reflection.Missing.Value):

public DateTime NextDate(ref object date)
{
    DateTime originalDate = date != Type.Missing ? (DateTime)date : DateTime.Today;
		
    date = originalDate.AddDays(+1);
		
    return originalDate;
}

This makes the API less self-documenting because the expected type is not explicit but the name of the parameter should be enough to make clear what is expected.

Conclusion

As demonstrated in the last section this solution is not perfect but even if it’s not documented it does the job and is robust.
You just have to be cautious and to do your due diligence by testing it with your real use-case to ensure it works seamlessly.

Recently I’ve again done my due diligence and shared this with the community on StackOverflow: Possible nasty side effects of tricking .Net to have optional ref parameters for COM.
I was pleased to get an answer from a COM guru, Hans Passant which I’ve found comforting as it did not point any nasty possible side effect but confirmed that default values may not be obvious.

As always if you catch any typo or mistake, encounter any issue or have additional questions feel free to let a comment, I’ll do my best to answer in a timely manner.

Leave a Reply

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

Prove me you\'re human :) *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>