Passing a Variant Array from Excel VBA to a WCF Function

Question

I'm trying desperately to do this.

I've been able to replicate the behavior found on this post.

http://damianblog.com/2009/07/05/excel-wcf/comment-page-1/#comment-64232

however, I am unable to pass an array to an exposed wcf function.

My WCF Service works like this (I have also tried to use arrays of int)

public object[] GetSomeArray()
    {
        return  new object[] { 1, 2, 3, 4};
    }

    public object[] ReturnSomeArray(object someArray)
    {
        object[] temp = (object[]) someArray;
        for (int i = 0; i < temp.Length; i++)
        {
            temp[i] = (int)temp[i] + 1;
        }

        return temp;
    }

my VBA code looks like this.

Dim addr As String
...


Dim service1 As Object
Set service1 = GetObject(addr)

Dim columnsVar
columnsVar = Array(1, 2, 3)


Dim anotherArray As Variant
anotherArray = service1.ReturnSomeArray(columnsVar)

I always have problems on the last line above. I don't understand why if I'm able to return an array from my WCF service that I'm not able pass that same array as a parameter to another WCF function.
current error message

I am getting a serialization error.

Any help would be appreciated.

I've tried the following stackoverflow.com/questions/15200110/… stackoverflow.com/questions/2729245/… stackoverflow.com/questions/4376169/… and stackoverflow.com/questions/2729245/… — priehl
– priehl, Commented Aug 26, 2013 at 14:50
Try to define the function as this public object[] ReturnSomeArray(object someArray) and then cast someArray to object[] or see what's the type really is — Simon Mourier
– Simon Mourier, Commented Aug 26, 2013 at 16:46

Kazimierz Jawor · Accepted Answer · 2013-08-28 19:27:29Z

2

+50

I have similar problems with Type mismatch error only if I declare array variable in VBA in this way:

Dim anotherArray() As Variant

but the error disappears if the variable is defined in this way:

Dim anotherArray As Variant

Some other differences between your and my similar solutions are:

//C#- my solution- without array[] definition:
public object[] ReturnSomeArray(object someArray)

//VBA- my solution -without array() definition:
Dim someArray As Variant

EDIT: 2013-08-28

Working with C#-Excel-Interop I prefer try&test way of searching solution. If anything works then I stick to it and sometime I miss to indicate the source of the solution or logic.

Below you will find code which includes LINQ to operate with arrays. These code snippets works in both direction- get data from C# to VBA >> pass it back to C# for sorting >> return to VBA. I hope it will help you more to finally solve your problems.

First: some C# code

    public object[] GetSomeArray()
    {
        return new object[] { 5, 2, 1, 7, 9, 1, 5, 7 };
    }

    public double[] ArraySorted(object tablica)
    {
        object[] obj = (object[])tablica;
        var filtr = from i in obj
                    orderby Convert.ToDouble(i)
                    select Convert.ToDouble(i);

        double[] wynik = (double[])filtr.ToArray();
        return wynik;
    }

Second: some VBA code

Sub qTest_second_attempt()

'declare array variable
    Dim tmp()
'and other variables
    Dim addr As String
        addr = "UDFArrayLinqTest.ArrayLinq"
'get references
    Dim service1 As Object
    Set service1 = CreateObject(addr)

'get array from C#
    tmp = service1.GetSomeArray()

'pass this array to C# back to sort it
    Dim someArray As Variant
    someArray = service1.ArraySorted(tmp)

'check the result in Immediate window
    Debug.Print Join(WorksheetFunction.Transpose(WorksheetFunction.Transpose(someArray)))
    'result: 1 1 2 5 5 7 7 9

End Sub

edited Aug 28, 2013 at 19:27

answered Aug 26, 2013 at 21:14

Kazimierz Jawor

19.1k7 gold badges38 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

RBarryYoung Over a year ago

IIRC this is the key trick to getting arrays from VBA to .Net-based code and vice-versa (that is, DIM .. As Variant without the array declaration).

Kazimierz Jawor Over a year ago

I don't know why it works and if there is any logic explanation. AFAIR, I did some test once to find out the solution to similar problem as yours. How do I set items in SomeArray in C#? In fact I do some casting like: public double[] ArraySorted(object tablica) >> object[] obj = (object[])tablica;. Is it what you are asking?

priehl Over a year ago

No I mean on the VBA side.

Kazimierz Jawor Over a year ago

Either in this way: Dim tmp >> tmp = Array(1,2,3,4,5) or I did a test similar to your code and retrieved array using GetSomeArray() C# method

priehl Over a year ago

Updated Original Post.

|

Collectives™ on Stack Overflow

Passing a Variant Array from Excel VBA to a WCF Function

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related