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)

, 20 Dec 2005
Rate this:
Please Sign up or sign in to vote.
Tips on sending an array of data from an Excel VBA to C#.

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

Share

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 9:37 
GeneralWhy cross the creek for water? [modified] PinmemberAbonet6-Sep-06 16:18 
Hi,
 
The proverb in my message heading may have lost something in translation (from Swedish) - if so, here is what it means: I think you go about this in a very roundabout sort of way, even if your solution works.
 
Two very simple solutions are available, if you'll just refrain from declaring your VBA arrays as (1 to 2). Simpler interop requires 0-based arrays. Here goes:
 
Type-safe solution:
 
Simply change this:
public int callableMethodArray(double[] inputArray)
into this:
public int callableMethodArray(ref double[] inputArray)
 
The reason for the "Function or interface marked as restricted, or the function uses an Automation type not supported in Visual Basic" error message is simply that VB6 and VBA requires arrays to be passed by reference. So adding "ref" gets rid of that problem.
 
However, this doesn't work if you try to call the method with a variant variable. It must be a variable declared as a double array in VB6/VBA. So what do you do if you want VBScript (that only have variants) to work. Like so:
 
Variant solution:
 
Change the definition into this:
public int callableMethodArray(object o)
 
And yes, casting DOES work. Can't figure out why it didn't work for you. This version can be called with either a strongly typed double array or a double array stored in a variant. However, it probably WON'T work with a variant array of variants. So what to do?
 
Your solution should work with a variant array of variants, and any other solution would probably be equally complex. Variants are evil!
 
Caveats:
 
1. I've only tested this with VS2005. It may be the case that my solutions didn't work in earlier versions, and if so, I apologize for nagging...
 
2. None of my solutions work when you declare the variable as a 1-based array. But if you stick with the default and only declare the upper bound, it will be 0-based and you're home free. The only point of confusion is that "Dim myarray(10) as Double" in VB is equivalent to "double[] myarray = new double[11]" in C#, as VB talks upper bound and C# talks number of elements.
 
This is what the doc's say about non-default arrays:
 
Multidimensional, or nonzero-bound safe arrays, can be marshaled into managed code if the method signature produced by Tlbimp.exe is modified to indicate an element type of ELEMENT_TYPE_ARRAY instead of ELEMENT_TYPE_SZARRAY. Alternatively, you can use the /sysarray switch with Tlbimp.exe to import all arrays as System.Array objects. In cases where the array being passed is known to be multidimensional, you can edit the Microsoft intermediate language (MSIL) code produced by Tlbimp.exe and then recompile it. For details about how to modify MSIL code, see Customizing Runtime Callable Wrappers.
 
Later,
Peter, programmer in Sweden
 
-- modified at 21:41 Wednesday 6th September, 2006
GeneralRe: Why cross the creek for water? PinmemberFablepongiste22-Jun-09 8:22 
GeneralSome feedback would help Pinmembersmurfy3423-Dec-05 18:20 
GeneralRe: Some feedback would help Pinmembertooom3120-Feb-06 0:55 
GeneralRe: Some feedback would help Pinmembersmurfy3420-Feb-06 14:47 
GeneralRe: Some feedback would help Pinmembertooom3120-Feb-06 22:47 
GeneralProblem in filling string[] of C# struct from VB6 PinmemberAmritanshu11-Aug-09 7: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 | Terms of Use | Mobile
Web03 | 2.8.1411023.1 | Last Updated 20 Dec 2005
Article Copyright 2005 by smurfy34
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid