VBA functions not accepting arrays as parameters

[Karim SOYAH]

Dear all,

I followed this tutorial to create VBA functions :

http://mikejuniperhill.blogspot.co.uk/2014/03/interfacing-c-and-vba-with-exceldna_16.html?_sm_au_=iVVH1qVkTPtqMR4Q

I wanted to create a function that takes an array as a parameter and a integer, here is the C# code

using System;

using ExcelDna.Integration;

using ExcelDna.ComInterop;

using System.Runtime.InteropServices;

namespace XLServer

{

    [ComVisible(true)]

    [ClassInterface(ClassInterfaceType.AutoDual)]

    public class COMLibrary

    {

        public double add(double x, double y)

        {

            return x + y;

        }

        public double test(double[] x)

        {

            return x[0];

        } 

    }

    //

    [ComVisible(false)]

    class ExcelAddin : IExcelAddIn

    {

        public void AutoOpen()

        {

            ComServer.DllRegisterServer();

        }

        public void AutoClose()

        {

            ComServer.DllUnregisterServer();

        }

    }

}

This code compile, tlb generated w/o any issues

But When using this test function in VBA, here is the code:

Sub FunctionTest()

Dim lib As New COMLibrary

Dim arr(0 To 1) As Double

arr(0) = 1

arr(1) = 10

Dim res As Variant

res = lib.test(arr)

End Sub

1 - Intelisense won't work with that function (still working with the add function)

2 - I get the following compile error message:

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

Please let me know if you need further information regarding this isses

Many thanks in advance

Best regards

[Govert van Drimmelen]

Hi Karim,

 

It’s quite possible that double[] arrays are not supported by the .NET / COM / VBA interop.

You might try to change it to object[] on the C# side and Variant() on the VBA side, to see if that works.

 

-Govert

 

 

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Karim SOYAH]

Hi Govert,

Thanks for your help. 

Unfortunately, I have just tried that and It was unsucessfull.

I am getting the same compile error message on VBA side.

The odd thing is that it works fine if the C# return type is a double[]:

 For instance , this will work:

 public double[] test2(double x, double y)

        {

            double[] result = new double[2];

            result[0] = x;

            result[1] = y;

            return result;

        }

[Karim SOYAH]

Dear all ,

I found the problem

Starting from that post http://stackoverflow.com/questions/13185159/how-to-pass-byte-arrays-as-udt-properties-from-vb6-vba-to-c-sharp-com-dll

In VBA, arrays need to be passed by reference 

I made a few changes in my code. I replaced the test function in my C# code:

        public double test(ref double[] input)

        {

            return input[0];

        }

It worked !

thanks 

[Govert van Drimmelen]

Excellent! Thanks for posting back.

 

-Govert

 

--