Sending an array of doubles from Excel VBA to C# (using COM interop)

• Download demo project - 17 Kb

Introduction

Excel VBA methods can invoke C# object methods via COM interop. The basic starting point is to expose the .Net object via COM interop (discussed below). Then you can call the .net object methods and pass it arguments -- BUT, if one of the arguments is a double[], VBA will complain when you try to run it:

"Function or interface marked as restricted, or the function uses an Automation type not supported in Visual Basic"

This article is for you if you want a technique and some code snippets that will help you send an array of data from a VBA function to a C# method.

Background - general info on how to set up a VBA to use a C# object

There may be other (and perhaps better/simpler) techniques but this is what I found I have to do. The C# class I want to call (callableClass) must implement an interface (interfaceExposer). interfaceExposer must expose the methods that I want to access in VBA.

//interfaceExposer.cs
using System; 
 
namespace blah
{ 
         public interface interfaceExposer 
         { 
                 int callableMethodSimple(double a); 
         }
} 

Your class should implement this method:

//cssClass.cs
using System;
namespace blah
{
         public class callableClass : interfaceExposer
         {
                 public int callableMethodSimple(double a)
                 {
                          return (int)a;
                 }
         }
}

Important: specify that you want to register this project for COM Interop. Go to the Project Menu/ProjectProperties and select Configration Properties/Build and then set “Register for COM Interop” to TRUE.
Compile when you are ready to try calling callableClass.callableMethodSimple()

In VBA: Pick Tools/References and select the COM object (SendArray in my case) To instantiate the class:

'VBA code
Public cssObject As New SendArray.callableClass
'SendArray is the name of the project in which callableClass resides.
Dim iClass As interfaceExposer
 
Sub MyRoutine()
         Set iClass = cssObject
         Dim result As Integer
         result = IClass.callableMethodSimple(5.0)
End Sub

But when you want to send an array to your cssObject method…

You get an error if you simply specify something like:

...
public int callableMethodArray(double[] inputArray)
// this will not work at run time.
{
    …
}

"Function or interface marked as restricted, or the function uses an Automation type not supported in Visual Basic" Here’s what you need to do. First work on the interfaceExposer

//interfaceExposer.cs
using System; 
 
namespace blah
{ 
    public interface interfaceExposer 
    { 
         int callableMethodSimple(double a); 
         int callableMethodArray(object a);
         // notice you are using object not a double[]
    }
} 

Then fix the class code:

//cssClass.cs
using System;
namespace blah
{
    public class callableClass : interfaceExposer
    {
        .. .
 
        public int callableMethodArray(object a)
        {
            return 0;
        }
    }

Now if you were to single step through the code when it enters the callableMethodArray(), you will see that object a is an array that has the array elements.

Actually using the elements of the array

You might think of casting the object into a double[] and then use your data as usual. But type casting will not work:

//cssClass.cs
using System;
namespace blah
{
    public class callableClass : interfaceExposer
    {
        .. .
 
        public int callableMethodArray(object a)
        {
            double[] receiverArray = new double[10];
            receiverArray = (double[])a;
            // will bomb at run time

            return 0;
        }
    }

You can use Reflection to extract the data from the generic object ‘a’ to double[]. Happily here is a method you can use if you are planning to receive a 1 dimension double[]. You can make more methods as you please for arrays of other formats using this as a prototype.

//cssClass.cs
using System;
using System.Reflection; // ADD THIS.
 
namespace blah
{
    public class callableClass : interfaceExposer
    {
        .. .
 
        public int callableMethodArray(object a)
        {
            double[] thisVect = 
              LoadComObjectIntoDoubleArray(a);                  
                          return 0;
        }
 
        private double[] LoadComObjectIntoDoubleArray(object comObject)
        {
            Type thisType = comObject.GetType();
            Type dblType = Type.GetType("System.Double[*]");
            double[] doubleArray = new double[1];
            // temporary allocation to keep compiler happy.
            if(thisType == dblType)
            {
                object[] args = new object[1];
                int numEntries = (int)thisType.InvokeMember("Length", 
                                         BindingFlags.GetProperty, 
                                         null, comObject, null);
                doubleArray = new double[numEntries];
                for(int j1=0; j1 < numEntries; j1++)
                {
                    args[0] = j1+1; // since VB arrays index from 1
                    doubleArray[j1] = 
                        (double)thisType.InvokeMember("GetValue", 
                                       BindingFlags.InvokeMethod, 
                                          null, comObject, args);
                }
             } // End if(thisType == dblType)
             return doubleArray;
         } // End LoadComObjectIntoDoubleArray()
     }
}

This will successfully import your array data from VBA to the C# method. To call this method from VBA look at the code sample below.

'VBA code
Public cssObject As New SendArray.callableClass
'SendArray is the name of the project 
'in which callableClass resides.
 
Dim iClass As interfaceExposer
 
Sub MyRoutine()
    Set iClass = cssObject
    Dim result As Integer
    Dim SendArray(1 To 2) As Double
    SendArray(1) = 5.2
    SendArray(2) = 7.5
    result = iClass.callableMethodArray(SendArray)
End Sub

Contact

Please write me for clarifications or suggestions on the discussion forum below. Enjoy.