Link to home
Start Free TrialLog in
Avatar of Declan Basile
Declan BasileFlag for United States of America

asked on

Passing arguments in VBA to a ParamArray parameter in a DLL.

I created a .dll file in that I reference in VBA (particularly from an Access 2003 application).  I have a method of a class defined as ...

"Public Function X(ByVal ParamArray Y() As Integer) As String" ...

, but I get the error "Class does not support Automation or does not support expected interface" when I call the method from VBA with the syntax ...

"MsgBox Login1.x(1, 2)"                  'Note: Login1 is an instance of the class "Login" with the method "X"

What in particular is the limitation with calling from VBA a method with a ParamArray parameter?  And how can I write the method and/or VBA code to work around the limitation?
Avatar of ste5an
Flag of Germany image

Have you published it as COM+ visible in the project settings?

I'm also not sure, whether you can use a ParamArray of Integers as VBA only know ParamArray of Variants.
Avatar of Declan Basile


Yes, it's COM+ visible.  Other methods and properties work just fine and the method I'm having trouble with comes up in intellisense.  I'll try changing the ParamArray to an array of Variants and report back.
I just found out that the variant data type is not supported in  How can I implement a method with a ParamArray parameter to be called from VBA?  Is there a workaround?
Avatar of Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger)
Flag of Canada image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I tried this VBA code with your function declaration:

"Public Function X ( Y() As Integer ) As String"

but still got the same error.  Any ideas?

Dim A(1) As Long     'Note: I changed Integer to Long

    Dim Login1 As AltekVB.Login
    Set Login1 = New AltekVB.Login

MsgBox Login1.X(A)
I passed in an array from VBA, but also had to accept it in as a generic object and create a procedure in to create an array from the object.  I adapted the code seen in the following link to work for my application.

Thanks Jacques for getting me part of the way there!