Type Mismatch error calling C# dll from VBA using com interop

Question

What are the limitations for parameters when calling C# methods from VBA code using COM interop?

I'm finding that if I call a method that takes a single simple type (string, int, etc) it works, but calling a method that takes a custom class as a parameter fails - with a 'Type Mismatch' error while compiling the VBA code.

C# code:

public namespace foo {
    public class XXX { }

    public class Service {
        public setString(String val) { ... }
        public setXXX(XXX val) { ... }
    }
}

VBA Code:

Dim service As New foo.Service
service.setString("this works")

Dim complexClass as New foo.XXX 

Rem This fails with a type mismatch error
service.setXXX(complexClass)

As mentioned above, the VBA compiler chokes on this line: service.setXXX(complexClass)

Any thoughts?

Answer 1

The method signatures for setString and setXXX are incomplete in your sample code since it does not state whether it returns void or any other data type. If setXXX returns void, try removing the parenthesis around the parameter(s) on VBA’s method call to it such as in:

service.setXXX complexClass

Explanation:

In VBA, you should not enclose sub-rotine's arguments in parenthesis. That can be verified easily creating a sub-routine with two parameters and trying to call it wrapping the arguments in parenthesis:

//C#
public void setXXX2(XXX val, XXX val2) { }

.

'VB
service.setXXX2 (complexClass, complexClass) 'Error

Back to your example, when you wrapped your single argument inside parenthesis, you created a Parenthesized Expression, which evaluates to a simple data value - of type String in this case.

You can see that for your self adding a new method - which I called GetParameterType - to your class for testing purposes:

public class Service { 
    public void setString(String val) {  }
    public void setXXX(XXX val) { }
    public void setXXX2(XXX val, XXX val2) { }

    public string GetParameterType(object val) {
        return val.GetType().Name;
    }
} 

Then run the method passing the variable directly and then the variable enclosed in parenthesis.

MsgBox service.GetParameterType(complexClass) ' Returns XXX
MsgBox service.GetParameterType((complexClass)) ' Returns String