COM Interop：我应该如何使一个C＃属性可用作VBA的VARIANT [英] COM Interop: What should I do to make a C# property usable as a VARIANT from VBA

问题描述

说我有一个C＃Nullable DateTime？属性，需要由VBA通过COM使用。

  public DateTime？ TestDate {
 get;组; 
} 
  

不幸的是，Nullables不可见通过COM，所以我想有属性返回一个将被视为VBA变体的东西。

不幸的是，我不确定应该如何写。

$ c> object 和动态而不是 DateTime？：属性值，我无法设置它（我从VBA获取运行时错误424对象需要的错误）。

>注意：我没有使我的库COM可见的问题：一切正常，我可以使用VBA的.Net类型没有问题，除了这个特殊的问题。

$ b http://msdn.microsoft.com/en-us/library/2x07fbw8%28v=VS.100%29.aspx#Y570\">对象的默认编组，但我似乎无法解释为什么我如果声明为 object ，则无法设置我的属性。

我缺少一些东西。

对象属性

  public object MyObject {
 get;组; 
} 
  

使用.Net Object 属性从VBA，读取属性是没有问题，它将正确看作为变体。

不幸的是，尝试设置属性

pre class =lang-cs prettyprint-override> private object _MyObject;

public object GetMyObject（）{
return _MyObject;
}

public void SetMyObject（object value）{
if（value == DbNull.Value）
value = null;
_MyObject = value;
}


检查 DBNull 是为了解决VBA' Null 实际上被编组为 DBNull 到.Net的问题。

DateTime？

现在，为了使nullable DateTime？我们可以这样做：

 private DateTime？ _我的约会; 
 
 public object GetMyDate（）{
 return _MyDate 
} 
 
 public void SetMyDate（object value）{
 if null || value == DbNull.Value）
 _MyDate = null; 
 else 
 _MyDate =（DateTime？）value; 
} 
 在 myclassinstance 中存在我们类的现有实例：
 公共属性Get MyDate（）作为变体
 MyDate = myclassinstance.GetMyDate（）
结束属性
 
公共属性集MyDate （值为Variant）
 myclassinstance.SetMyDate value 
结束属性
  
 
 
 
通用方式
 
 
 
这是一个有点丑，因为我们的C＃类暴露 MyDate 为GetMyDate / SetMyDate方法， 。
 
为了以更通用的方式实现这个机制，我们可以使用 Dictionary 作为后台存储：
  [ClassInterface（ClassInterfaceType.AutoDual）] 
 [ComVisible（true）] 
 public class MyClass {
 
私有字典< 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 =空值; 
 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）; 
} 
} 
} 
  
  
 
 
 
在VBA中，我们声明属性：
 公共属性Get MyDate（）作为变体
 MyDate = myclassinstance.GetPropertyValue（MyDate）
 End Property 
 
公共属性Set MyDate（value as Variant）
 myclassinstance.SetPropertyValueMyDate，value 
结束属性
  
 
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.
解决方案
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


                        
这篇关于COM Interop：我应该如何使一个C＃属性可用作VBA的VARIANT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！