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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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.
Declan_BasileITAuthor Commented:
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.
Declan_BasileITAuthor Commented:
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?
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Jacques Bourgeois (James Burger)PresidentCommented:
I got that same problem a few years ago and was never able to make it work.

I read in a few places that ParamArrays were not compatible between .NET and VBA, but could never find and official statement to that effect.

I ended up defining the parameter as a dynamic array:

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

And passed it an array sized as needed:

    Dim A(1) As Integer

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Declan_BasileITAuthor Commented:
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)
Declan_BasileITAuthor Commented:
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.