Object Type Error calling C# from VBA

Question

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?

Can you please provide more code for example the GETCIQData Method also keep in mind that C# does not use Variants.. — MethodMan
– MethodMan, Commented Feb 16, 2012 at 16:12
since you are trying to call it that way ..are you missing some Invoke Call or method..? — MethodMan
– MethodMan, Commented Feb 16, 2012 at 16:14
C# does not use variants - it reads the passed arrays as Object arrays. Like I said, it works fine with a fixed-length array. — Patrick
– Patrick, Commented Feb 16, 2012 at 16:53

Patrick · Accepted Answer · 2012-02-16 20:52:36Z

1

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

edited Feb 16, 2012 at 20:52

answered Feb 16, 2012 at 20:39

Patrick

7221 gold badge6 silver badges17 bronze badges

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

Comments

ForbesLindesay · Accepted Answer · 2012-02-16 16:36:36Z

0

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.

answered Feb 16, 2012 at 16:36

ForbesLindesay

10.8k3 gold badges52 silver badges76 bronze badges

1 Comment

Patrick Over a year ago

I was unable to find a method like that - though that could be because of VBA's poor documentation. But I figured out my problem, as described in my answer. Thanks for your help.

Collectives™ on Stack Overflow

Object Type Error calling C# from VBA

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related