COM Interop: what should I do to use the C # property like VARIANT from VBA

Question

Say I have a C # Nullable DateTime? property DateTime? which you need to use VBA through COM.

  public DateTime? TestDate { get ; set; } 

Unfortunately, Nullables are not visible through COM, so I would like the property to return what will be considered as an option from VBA.

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

I tried using object and dynamic instead of DateTime? : while I can get the value of the property, I can’t set it (I get the runtime error "424". Necessary object errors from VBA).

Note. I have no problem creating my COM Visible library: everything works fine, and I can use .Net types from VBA without any problems, except for this specific problem.

Thanks for any pointers.

EDIT . I found an interesting page that describes the default sorting for objects , but I cannot seem to explain why I cannot set my property if it is declared as object .
Did I miss something.

Answer 1

Here is what I did to solve the problem.

Object Properties

 public object MyObject { get ; set; } 

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

However, using simple 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; } 

Checking for DBNull is to work around the problem that VBA ' Null actually ordered as DBNull .Net.

DateTime?

Now, to do work with a null DateTime? 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 properties in the 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 

More general way

This is a bit ugly since our C # class exposes MyDate to GetMyDate / SetMyDate methods instead of properties.
To implement this in a more general way, so the mechanism can be used for all properties in our class, we can use Dictionary as a backup storage:

 [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 properties are not visible from VBA.

And in VBA, we declare 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 

Answer 2

Create your own NullableDateTime class using Nullable functions such as the HasValue and Value and GetValueOrDefault properties.