Passing an array argument from Excel VBA to a WCF service

Question

I'm trying to pass an array as an argument to my WCF service. To test this using Damian's sample code, I modified GetData it to try to pass an array of ints instead of a single int as an argument:

using System;
using System.ServiceModel;

namespace WcfService1
{
    [ServiceContract]
    public interface IService1
    {
        [OperationContract]
        string GetData(int[] value);
    }
}

using System;
namespace WcfService1
{
    public class Service1 : IService1
    {
        public string GetData(int[] value)
        {
            return string.Format("You entered: {0}", value[0]);
        }
    }
}

Excel VBA code:

Dim addr As String
addr = "service:mexAddress=""net.tcp://localhost:7891/Test/WcfService1/Service1/Mex"","
addr = addr + "address=""net.tcp://localhost:7891/Test/WcfService1/Service1/"","
addr = addr + "contract=""IService1"", contractNamespace=""http://tempuri.org/"","
addr = addr + "binding=""NetTcpBinding_IService1"", bindingNamespace=""http://tempuri.org/"""

Dim service1 As Object
Set service1 = GetObject(addr)

Dim Sectors( 0 to 2 ) as Integer
Sectors(0) = 10
Sectors(1) = 20

MsgBox service1.GetData(Sectors)

This code works fine with the WCF Test Client, but when I try to use it from Excel, I have this problem. When Excel gets to the service1.GetData call, it reports the following error:

>Run-time error '-2147467261 (80004003)'
>
>Automation error
>Invalid Pointer

It looks like there is some incompatibility between the interface specification and the VBA call.

Have you ever tried to pass an array from VBA into WCF? Am I doing something wrong or is this not supported using the Service moniker?

Answer 1

There are a couple of things that you could try / check:

1. From your code, it looks like only the first element in your array is set to a value, try setting them all.
2. Instead of passing an array of int, try passing an object that contains a single property which is an array of int.

Answer 2

a hack solution could be to join your array as a single string using some kind of delimiter, and pass that instead of the array. Then in the service, split the string back to an array. It seems to work, but I really want the correct solution. If anyone can figure this out, please post!