Returning multi dimensional array data from C# web

Question

I have a C# webservice that returns a 2D array data. Since we cannot have webservices return multi dimensional data, i made it to return a jagged array.

[OperationContract]
object[][] WSGetData();

I have a COM Visible C# class library. This is a thin layer which consumes this service and provides it as it is to Excel VBA clients. (For certain reasons, we chose not go via VSTO, or Web Services References Toolkit routes.)

class Wrapper
{
    public object[][] GetData()
    {
        return WSproxy.WSGetData(); //Calling the webservice method
    }
}

I call the method in VBA as below.

Dim data as Variant
data = wrapperObj.GetData();

I get a Type Mismatch error.

When I changed the Wrapper class to convert the webservice's 'jagged array' output to a multi dimensional output (ie. object[,]) before returning to VBA, it works fine. But I do not want to do this, because it would affect performance as we will be passing around huge data.

What is the best way to achieve this, please. Thanks for any directions..

Answer 1

Jagged Arrays are possible. I don't think VBA likes the way you've declared your variant. You probably need to declare it as a Variant array. See the below example:

Sub Test()
    Dim oneToTen(9) As String 'Array 1
    Dim tenTo21(10) As String  'Array 2
    Dim twentyTwoTo23(1) As String  'Array 3
    Dim vArray() As Variant   'Jagged Array (array of arrays)

    'Fill test data in the three arrays
    Dim iCount As Integer
    For iCount = 0 To 9
        oneToTen(iCount) = iCount + 1
    Next iCount
    For iCount = 0 To 10
        tenTo21(iCount) = iCount + 11
    Next iCount
    For iCount = 0 To 1
        twentyTwoTo23(iCount) = iCount + 22
    Next iCount

    'If you uncomment the code below, you will get a type mismatch (probably for the same reason you get it in your webservice)
    'vArray1(0) = oneToTen

    'However, if you REDIM the variant array, you can then set each array into the variant
    Const JAGGED_ARRAY_SIZE = 2 'This will probably require another property on your webservice to see how big your Jagged Array is (e.g.  wrapperObj.GetJaggedArraySize())
    ReDim vArray(JAGGED_ARRAY_SIZE)

    vArray(0) = oneToTen
    vArray(1) = tenTo21
    vArray(2) = twentyTwoTo23

    'Now loop through the jagged array:
    Dim outerLoop As Integer
    Dim innerLoop As Integer
    Dim vCurrentArray As Variant

    'Loop through the arrays in the array and print out the data
    For outerLoop = 0 To JAGGED_ARRAY_SIZE
        For innerLoop = 0 To UBound(vArray(outerLoop))
            Debug.Print "Outer Loop:  " & outerLoop & "  Inner Loop:  " & innerLoop & "  Array Value:  " & vArray(outerLoop)(innerLoop)
        Next innerLoop
    Next outerLoop

End Sub