Hi Dan,

I may not have absorbed all of what you explained but

I am having trouble passing a custom object from an Excel Add-in
collection to the VB6 dll. I get the error message: "Object
doesn't support this property or method".

Is that message in VBA or in the DLL. If the latter it would suggest the
object is being received in the dll but something your code is doing with it
gives an error.

Can you step through your code between VBA & VB6, pass your object and see
what happens.

Regards,
Peter T



"Dan" wrote in message
ups.com...
Hello All,

I have an Excel Add-in that is comprised of several classes and
collections. The application also includes several UDF's that
connect with databases, perform queries and return the data within the
Excel worksheet. I want to update the application so that I can
abstract the SQL that is required when accessing one of many
third-party databases. To do this, I have created VB6 dlls that are
late bound at runtime. When the function needs to get specific SQL, it
will pass the request to the appropriate DLL. Abstracting the
different SQL sets allows me to support unlimited third party databases
by simply writing a small dll that the Add-in will use at runtime
without having to modify the lion's share of the application, the
Add-in.

The Add-in looks into a folder and finds all of the dlls in that
folder. It then creates an array of available dlls to call at runtime
by storing their program ids.

The concept works fine except that:

I am having trouble passing a custom object from an Excel Add-in
collection to the VB6 dll. I get the error message: "Object
doesn't support this property or method". I know that the sub I am
calling is the right one, it does exist. I have even changed the
argument to pass a string instead of my custom object and it works just
fine when it is passed a string. So the problem must be that I am
passing my custom object.

At first I thought I could get away with copying the class module from
my Excel Add-in to my VB6 dll and both would understand the kind of
object that is being passed. That didn't work. So, after hours of
looking through various news groups, I have changed the class for the
custom object to reside in its own dll, set its Instancing to be
GlobalMultiUse and put a reference to this class in my Excel Add-in and
also in my VB6 DLL. However, I get the same error.

I could just get all of the properties of the custom object and store
them in individual strings to be passed to the VB6 dll, but I would
really like to keep it clean and pass the object from the Excel Add-In
to the VB6 dll and then operate on the individual properties of the
object within the dll and pass back the SQL to the Excel Add-in via a
string.


Here is the calling code:
'reference set to the dll that contains this class
Public Customer as clsCustomer
Private Sub CommandButton2_Click()
Dim tmp() As String
On Error GoTo EHandle

Set Customer = New clsCustomer
Customer.Name = "Golden Springs Camp Ground"

'this objets tag property is set earlier to contain program id of
the dll to make calls to.
tmp = Split(Me.CommandButton2.Tag, ".")

plugins(CInt(tmp(0))).startup CInt(tmp(1)) 'this line works fine,
passing an integer

plugins(CInt(tmp(0))).getCustomerName (Customer) 'this line
fails, passing in custom object

Exit Sub
EHandle: MsgBox Err.Description

End Sub

Here are the subs I am calling in the VB6 dll:

'this one is the one that fails when passing the custom object.
'reference set to the dll that contains this class
Sub getCustomerName(cCustomer As clsCustomer)

MsgBox cCustomer.Name

End Sub

'This one works just fine!
Sub StartUp(intArg As Integer)

MsgBox "Vb Plugin - Argument =" & intArg
MsgBox "FrmMain.Caption = " & frmMain.Caption

End Sub

Does anybody have any ideas on how to accomplish this?

Many thanks,
Dan