Click here to Skip to main content
Click here to Skip to main content

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

By , 20 Dec 2005
Rate this:
Please Sign up or sign in to vote.

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

smurfy34
Web Developer
United States United States
No Biography provided

Comments and Discussions

 
QuestionHow to retrieve two dimensional string array stored in the object of Object type PinmemberSachin Shaha24-Mar-08 8:37 
GeneralWhy cross the creek for water? [modified] PinmemberAbonet6-Sep-06 15:18 
GeneralRe: Why cross the creek for water? PinmemberFablepongiste22-Jun-09 7:22 
GeneralSome feedback would help Pinmembersmurfy3423-Dec-05 17:20 
GeneralRe: Some feedback would help Pinmembertooom3119-Feb-06 23:55 
GeneralRe: Some feedback would help Pinmembersmurfy3420-Feb-06 13:47 
GeneralRe: Some feedback would help Pinmembertooom3120-Feb-06 21:47 
GeneralProblem in filling string[] of C# struct from VB6 PinmemberAmritanshu11-Aug-09 6:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140415.2 | Last Updated 20 Dec 2005
Article Copyright 2005 by smurfy34
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid