Declan Basile
asked on
Passing arguments in VBA to a ParamArray parameter in a VB.net DLL.
I created a .dll file in VB.net 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 VB.net method with a ParamArray parameter? And how can I write the method and/or VBA code to work around the limitation?
"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 VB.net method with a ParamArray parameter? And how can I write the method and/or VBA code to work around the limitation?
ASKER
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.
ASKER
I just found out that the variant data type is not supported in VB.net. How can I implement a VB.net method with a ParamArray parameter to be called from VBA? Is there a workaround?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried this VBA code with your VB.net function declaration:
"Public Function X ( Y() As Integer ) As String"
but still got the same error. Any ideas?
VBA:
Dim A(1) As Long 'Note: I changed Integer to Long
A(0)=1
A(1)=2
Dim Login1 As AltekVB.Login
Set Login1 = New AltekVB.Login
MsgBox Login1.X(A)
"Public Function X ( Y() As Integer ) As String"
but still got the same error. Any ideas?
VBA:
Dim A(1) As Long 'Note: I changed Integer to Long
A(0)=1
A(1)=2
Dim Login1 As AltekVB.Login
Set Login1 = New AltekVB.Login
MsgBox Login1.X(A)
ASKER
I passed in an array from VBA, but also had to accept it in VB.net as a generic object and create a procedure in VB.net to create an array from the object. I adapted the code seen in the following link to work for my application.
http://stackoverflow.com/questions/17552107/passing-arrays-from-vba-to-vb-net
Thanks Jacques for getting me part of the way there!
http://stackoverflow.com/questions/17552107/passing-arrays-from-vba-to-vb-net
Thanks Jacques for getting me part of the way there!
I'm also not sure, whether you can use a ParamArray of Integers as VBA only know ParamArray of Variants.