COM Interop: What should I do to make a C# property usable as a VARIANT from VBA

Question

Say I have a C# Nullable DateTime? property that needs to be consumed by VBA through COM.

    public DateTime? TestDate {
        get ; set;
    }

Unfortunately, Nullables are not visible through COM, so I would like to have the property return a something that will be seen as a Variant from VBA.

Unfortunately, I'm not sure how this should be written.

I tried using an object and a dynamic instead of DateTime?: while I can get the property value, I can't set it (I get Run-Time error '424' Object required errors from VBA).

Note: I don't have issues with making my library COM Visible: everything works fine and I'm able to use .Net types from VBA without problem, except for this particular issue.

Thanks for any pointers.

EDIT: I found an interesting page describing the default marshalling for objects, but I can't seem to explain why I can't set my property if it's declared as object.
I'm missing something.

Answer 1

Here what I did to go around the issue.

Object properties

public object MyObject {
    get ; set;
}

When using a .Net Object property from VBA, reading the property is no problem and it will be correctly seen as a Variant.
Unfortunately, trying to set the property from VBA will fail.

However, using plain methods will work fine:

private object _MyObject;

public object GetMyObject() {
    return _MyObject;
}

public void SetMyObject(object value) {
    if (value == DbNull.Value)
        value = null;   
    _MyObject = value;
}

The check for DBNull is to get around the problem that VBA' Null is actually marshalled as DBNull to .Net.

DateTime?

Now, to make nullable DateTime? work, we can do something like:

private DateTime? _MyDate;

public object GetMyDate() {
    return _MyDate
}

public void SetMyDate(object value) {
    if (value == null || value == DbNull.Value)
        _MyDate = null;   
    else
        _MyDate = (DateTime?)value;
}

And in VBA, we can hide these get/set in properties (assuming we have an existing instance of our class in myclassinstance):

Public Property Get MyDate() As Variant
    MyDate = myclassinstance.GetMyDate()
End Property

Public Property Set MyDate(value as Variant)
    myclassinstance.SetMyDate value
End Property

A more generic way

This is a bit ugly since our C# class is exposing MyDate as GetMyDate/SetMyDate methods instead of properties.
To implement this in a more generic way so the mechanism is usable for all properties in our class, we can use a Dictionary as a backing store:

[ClassInterface(ClassInterfaceType.AutoDual)]
[ComVisible(true)]
public class MyClass {

    private Dictionary<string,object> backingStore = new Dictionary<string,object>();

    public object GetPropertyValue(string propertyName) {
        if (backingStore.ContainsKey(propertyName))
            return backingStore[propertyName];
        else
            return null
    }

    public void SetPropertyValue(string propertyName, object value) {
        if (value == DBNull.Value) value = null;
        if (backingStore.ContainsKey(propertyName))
            backingStore[propertyName] = value;
        else
            backingStore.Add(propertyName, value);
    }

    [ComVisible(false)]
    public DateTime? MyDate {
        get {
            return GetPropertyValue(@"MyDate") ?? default(DateTime?);
        }
        set {
            SetPropertyValue(@"MyDate", value);
        }            
    }
}

The ComVisible(false) attribute ensures that the properties are not visible from VBA.

And in VBA, we declare the properties:

Public Property Get MyDate() As Variant
    MyDate = myclassinstance.GetPropertyValue("MyDate")
End Property

Public Property Set MyDate(value as Variant)
    myclassinstance.SetPropertyValue "MyDate", value
End Property