Calling a WCF function (with parameters by reference) from VBA code in Excel

0
votes

I have developed a WCF service which is successfully working. Then I have some VBA code in Excel that I wrote following this guide - http://damianblog.com/2009/07/05/excel-wcf/

It is working, I have tested it with simple functions that have parameters and return values - it returns results correctly. What I now need is to pass several parameters by reference, set their values in the function, then return. For that I created this test function:

// interface...
[OperationContract]
int Read(ref int val1, ref int val2);

// implementation...
public void Read(ref int val1, ref int val2)
{
    val1 = 10;
    val2 = 20;
}

I call it from the VBA module like this:

val1 = 0
val2 = 0

Call service1.Read(val1, val2)
MsgBox val1
MsgBox val2

The values I get in the end are 20 and 0, instead of 10 and 20.

Is it not supported to have more than 1 'byref' parameter or am I doing something wrong here?

P.S. Another interesting thing is that I can't declare val1 as integer or long because then service1.Read() call returns with exception "type mismatch". It seems to only work with Variant type.

Edit: Ok, I worked around the problem by returning an array of objects from the function. Something like this:

// interface...
[OperationContract]
object[] Read();

// implementation...
public object[] Read()
{
    return new object[4] { 10, 20, "hello world", DateTime.Now };
}

And in VBA:

Dim val() As Variant

val = service1.Read()
MsgBox val(0)
MsgBox val(1)
MsgBox val(2)
MsgBox val(3)

Worked like a charm

1
wcfexcelpass-by-referencevba

1 Answers

0
votes

Can you add your structure and return it back from wcf method? I mean, instead of 

int Read(ref int val1, ref int val2);

Do something like 

[DataContract]
struct MyDataStructure {

[DataMember]
int val0;

[DataMember]
int val1;

[DataMember]
int val2;

}

...

MyDataStructure Read();