Can a variant array be passed to C# using com interop?

Question

I try to transfer some data between Excel and C#. For this I wrote a simple C# class with a property to set and get the data.

[Guid("xxx")]
[ClassInterface(ClassInterfaceType.AutoDual)]
[ComVisible(true)]
public class Vector
{
    private object[,] _values;

    public object[,] ExcelValues
    {
        get { ... }
        set { ... }
    }
}

Getting the ExcelValues property in VBA works well, but it is not possible to set it in VBA. The VBA code does not compile if I try to set the property:

    Dim values As Variant
    With myRange
        ' typo: Set values = Range(.Offset(0, 0), .Offset(100, 0))
        values = Range(.Offset(0, 0), .Offset(100, 0))
    End With

    Dim data As New Vector

    ' this doesn't compile
    data.ExcelValues = values      

    ' this works
    values = data.ExcelValues

Any suggestions how I can acomplish this, without setting each value from the variant Array one at a time?

BenMorel · Accepted Answer · 2013-11-08 13:16:00Z

I found a solution based on code that was posted here. A variant array has to be passed from VBA to C# as an object (not object[,]). Then it can be converted to something that is more handy by using reflection:

[Guid("xxx")]
[ComVisible(true)]
[ClassInterface(ClassInterfaceType.AutoDual)]
public class Vector
{        
    [ComVisible(false)]
    public IList<double> Values { get; set; }

    public object[,] GetExcelValues()
    {
        // own extension method
        return Values.ConvertToExcelColumn();
    }

    public void SetExcelValues(object comArray)
    {
        IEnumerable<object> values = ConvertExcelCloumnToEnumerable(comArray);
        Values = new List<double>(values.Select(Convert.ToDouble));
    }

    private static IEnumerable<object> ConvertExcelCloumnToEnumerable(object comObject)
    {
        Type comObjectType = comObject.GetType();

        if (comObjectType != typeof(object[,]))
            return new object[0];

        int count = (int)comObjectType.InvokeMember("Length", BindingFlags.GetProperty, null, comObject, null);
        var result = new List<object>(count);

        var indexArgs = new object[2];
        for (int i = 1; i <= count; i++)
        {
            indexArgs[0] = i;
            indexArgs[1] = 1;
            object valueAtIndex = comObjectType.InvokeMember("GetValue", BindingFlags.InvokeMethod, null, comObject, indexArgs);
            result.Add(valueAtIndex);
        }

        return result;
    }
}

The other way - from C# to VBA - it can be passed more comfortable as object[,] or double[,].
Hope there are no syntax typos :).

Tim Williams · Accepted Answer · 2011-05-10 16:26:17Z

0

By using Set you're telling VBA that "values" is an object (in this case a Range), but then you're not using set when assigning that to ExcelValues. Try dropping the Set when you're reading the values.

With myRange         
    values = Range(.Offset(0, 0), .Offset(100, 0)).Value     
End With

answered May 10, 2011 at 16:26

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

1 Comment

al-bex Over a year ago

You are right. It was a typo, but it is not the solution for the problem.

Collectives™ on Stack Overflow

Can a variant array be passed to C# using com interop?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related