C# property exposed to VBA (COM) : Run-time error '424': Object required

Question

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

C# Code

[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; }
}

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

VBA Code

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

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)?

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: https://web.archive.org/web/20140531181434/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.

A VBA array must have a lower bound of 0 to be compatible with a C# array. — Hans Passant, Oct 19 '12 at 17:53
I've been passing arrays as function parameters from COM to .NET then converting them to zero-based within .NET. Are you saying that the code above is failing because of this issue? — Adam, Oct 19 '12 at 21:32

score 3 · Accepted Answer · answered Oct 19 '12 at 19:46

3

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;
        }
    }
}

answered Oct 19 '12 at 19:46

Daniel Dušek

13,683
5
36
51

This was already said in an answer that I deleted, since the OP did not like this solution. – yms Oct 19 '12 at 19:48
Thank you Daniel. I have no problem passing it as a parameter to a function. But I did ask specifically for properties, hence the title and the note in the end. – Adam Oct 19 '12 at 21:29
Well Adam, I did not notised that you asked for property ... even though you wrote it in the title of your question :-) ... sorry! This is the only way I know how to pass data to VBA from C#. Good luck! – Daniel Dušek Oct 22 '12 at 08:36
While this doesn't solve my problem, and after investigation, I couldn't find a solution. Therefore I will mark this as the right answer. – Adam Oct 22 '12 at 15:07

score 1 · Answer 2 · edited May 23 '17 at 10:34

VBA always passes arrays wrapped in a variant by reference (VT_VARIANT | VT_BYREF), with another variant inside that contains the actual array, so you cannot use arrays in properties while specifing the element types, you need to use a method so that you can specify the parameters as "by reference".

[InterfaceType(ComInterfaceType.InterfaceIsIDispatch)] 
[Guid("22341123-9264-12AB-C1A4-B4F112014C31")] 
public interface IComExposed
{ 
     void setDoubleArray(ref double[] myArray); 
     //(...) 
}

A similar question:
Pass an array from vba to c# using com-interop

An answer to that question mentions the option of using a user-defined collection instead of a array of primitive types, maybe that can also be a solution for your issue.

Relevant references in the documentation:

Marshaling ByRef Variants

VARIANT and VARIANTARG in WinAPI

MarshalAsAttribute class in .Net

Passing Arrays to COM in .Net

user defined collection dosn't help me as the array in this example represents the Range.Value which is a native array. While the solution that you have given is a workaound, I don't think there is another solution. — Adam, Oct 22 '12 at 17:55

C# property exposed to VBA (COM) : Run-time error '424': Object required

C# Code

VBA Code

2 Answers2

Linked