Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Objects from Excel VBA collection to a VB6 DLL
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error with passing collection as parameter | Excel Programming | |||
Range objects in a collection | Excel Programming | |||
Passing Excel Objects As Parameters | Excel Programming | |||
Passing Excel Objects As Parameters | Excel Programming | |||
Calling Procedures from another excel sheet by passing objects | Excel Programming |