CODEWITHSUNDEEP
c#vbavsto
Patrick
Patrick

Reputation: 702

Object Type Error calling C# from VBA

I have a an Excel addin written in C# I am trying to call from VBA in Excel. In VBA I generate several arrays to pass:

Dim Identifiers() As Variant
Dim Variables() As Variant
Dim Times() As Variant
...
Dim QaddIn As COMAddIn
Dim QTool As Object
Dim results As Variant
Set QaddIn = Application.COMAddIns("QTool")
QaddIn.Connect = True
Set QTool = QaddIn.Object

results = QTool.GetQData(datasetName, Identifiers, Variables, Times, timeString)

GetQData is defined in C# as:

string[] GetQData(string DatasetName, object[] Identifiers, object[] Variables, object[] TimeCodes,
                                string TimeString);

But when I run the code, VBA throws the error object of type 'system.object[*]' cannot be converted to object of type 'system.object[]'. This same code worked fine passing variant arrays defined with a static length, as in Dim Identifiers(3) As Variant. What is the difference between what I am trying to pass now?

Upvotes: 0

Views: 556

Answers (2)

Patrick
Patrick

Reputation: 702

The problem was that the array I was attempting to pass to C# was indexed from 1, that is, it was an Array(1 to 5) rather than an Array(0 to 4). It's unfortunate that passing an array indexed from 1 fails, because Range.Value and all of the WorksheetFunction methods return arrays indexed from 1.

I wrote the following code in VBA to reindex my arrays from zero before passing them:

Function ShiftArray(ThisArray() As Variant) As Variant    
    Dim lb As Long, ub As Long
    Dim NewArray() As Variant
    Dim i As Long
    lb = LBound(ThisArray)
    ub = UBound(ThisArray)

    ReDim NewArray(0 To (ub - lb))

    For i = 0 To (ub - lb)
        NewArray(i) = ThisArray(i + lb)
    Next i

    ShiftArray = NewArray    
End Function

Upvotes: 1

ForbesLindesay
ForbesLindesay

Reputation: 10712

The problem is that variable length arrays and fixed length arrays are not the same. vba in general will be pretty forgiving so you generally get away with it, but C# isn't. It can't be sure that you mean to convert the array to a fixed length array. You should look for a method on the Identifiers object to convert it to a fixed length array. This will probably then look something like:

results = CIQTool.GetCIQData(datasetName, Identifiers.ToArray(), Variables.ToArray(), Times.ToArray(), timeString)

I'm not sure the exact naming of that method though so use the code completion to look for it.

Upvotes: 0

Related Questions