暴露给 VBA (COM) 的 C# 属性:运行时错误“424":需要对象 [英] C# property exposed to VBA (COM) : Run-time error '424': Object required

问题描述

此 C# 代码位于 .NET 4.5 ComVisible 程序集中:

This C# code is in a .NET 4.5 ComVisible assembly:

[InterfaceType(ComInterfaceType.InterfaceIsDual)]
[Guid("22341123-9264-12AB-C1A4-B4F112014C31")]
public interface IComExposed
{
    double[] DoubleArray { get; set; }
    object[] ObjectArray { get; set; }
    object PlainObject { get; set; }
    double ScalarDouble { get; set; }
}

[ClassInterface(ClassInterfaceType.None)]
[Guid("E4F27EA4-1932-2186-1234-111CF2722C42")]
[ProgId("ComExposed")]
public class ComExposed : IComExposed
{
    public double[] DoubleArray { get; set; }
    public object[] ObjectArray { get; set; }
    public object PlainObject { get; set; }
    public double ScalarDouble { get; set; }
}

从 Excel 2010 32 位 VBA，我有以下行为:

From Excel 2010 32bit VBA, I've got the following behavior:

Dim VBArray(1 To 3) As Double
VBArray(1) = 1
VBArray(2) = 2
VBArray(3) = 3

Dim oComExposedEarlyBinding As New ComExposed

' Works
oComExposedEarlyBinding.ScalarDouble = 5

' Compile Error: Function or interface marked as restricted,
' or the function uses an Automation type not supported in Visual Basic
oComExposedEarlyBinding.DoubleArray = VBArray

' Compile Error: Function or interface marked as restricted,
' or the function uses an Automation type not supported in Visual Basic
oComExposedEarlyBinding.ObjectArray = VBArray

' Run-time error '424': Object required
oComExposedEarlyBinding.PlainObject = VBArray

' Run-time error '424': Object required
oComExposedEarlyBinding.PlainObject = 5

Dim oComExposedLateBinding As Variant
Set oComExposedLateBinding = New ComExposed

' Works
oComExposedLateBinding.ScalarDouble = 5

' Run-time error '5': Invalid procedure call or argument
oComExposedLateBinding.DoubleArray = VBArray

' Run-time error '13':  Type mismatch
oComExposedLateBinding.ObjectArray = VBArray

' Works
oComExposedLateBinding.PlainObject = VBArray

' Works
oComExposedLateBinding.PlainObject = 5

您已经注意到 PlainObject 正在后期绑定模式下工作，但显然，以丢失输入为代价，因此在 VBA 中丢失自动完成 (IntelliSense)，这在我的场景中是不可接受的.

As you've noticed the PlainObject is working in late binding mode but, obviously, on the expense of losing typing and therefore losing auto complete (IntelliSense) in VBA which is not acceptable in my scenario.

我在示例中关注的行是以下行:

The lines that I care for in my example are the following lines:

oComExposedEarlyBinding.DoubleArray = VBArray
oComExposedEarlyBinding.ObjectArray = VBArray
oComExposedEarlyBinding.PlainObject = VBArray

让上述三行中的任何一行都可以满足我的需求，那么您是否有任何解决方法或解决方案可以使这项工作(请注意，我对将数组作为参数传递给函数不感兴趣)?

Getting any of the three lines above working would satisfy my need, so do you have any workaround or a solution that would make this work (note that I am not interested in passing the array as a parameter to a function)?

更新:将此问题提交给 Microsoft 的支持并等待近三周后.他们确认这是一个错误，这是 KB:http://support.microsoft.com/kb/327084 并且 C# 中唯一的解决方法是标记为下面的解决方案.但是，如果用 C++/CLI 编写，我可以确认此代码可以按预期工作.

Update: After submitting this issue to Microsoft's support and waiting for almost three weeks. They confirmed that it is a bug and this is the KB: http://support.microsoft.com/kb/327084 and the only workaround within C# is what is marked as the solution below. However, I am able to confirm that this code works as expected if written in C++/CLI.

推荐答案

VBA 数组必须从零开始，在 c# 中使用 ref 参数，示例:

VBA array must be zero based and in c# use ref parameter, sample:

Option Explicit

Sub test()
    Dim VBArray(0 To 2) As Double
    VBArray(0) = 1
    VBArray(1) = 2
    VBArray(2) = 3

    Dim oComExposedEarlyBinding As New ComExposed
    oComExposedEarlyBinding.SetDoubleArray VBArray

End Sub

<小时>

using System.Runtime.InteropServices;

namespace COMVisibleTest
{
    [InterfaceType(ComInterfaceType.InterfaceIsDual)]
    [Guid("22341123-9264-12AB-C1A4-B4F112014C31")]
    public interface IComExposed
    {
        void SetDoubleArray(ref double[] doubleArray);
    }

    [ClassInterface(ClassInterfaceType.None)]
    [Guid("E4F27EA4-1932-2186-1234-111CF2722C42")]
    [ProgId("ComExposed")]
    public class ComExposed : IComExposed
    {
        private double[] _doubleArray;

        public void SetDoubleArray(ref double[] doubleArray)
        {
            _doubleArray = doubleArray;
        }
    }
}

这篇关于暴露给 VBA (COM) 的 C# 属性:运行时错误“424":需要对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！