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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Objects from Excel VBA collection to a VB6 DLL
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Objects from Excel VBA collection to a VB6 DLL
Hi Peter,
I stepped through the code as you requested. The error message is coming from Microsoft Excel. When I put the VB6 dll into debug and call it from the Excel Addin the program execution jumps from Excel to the VB6 Editor and steps through the first procedure (startup) and then jumps back to the Excel VBA editor. Then when execution goes to the second procedure (getCustomerName), the program execution jumps immediately to my error handle routine in VBA, it never tries to execute the procedure in the DLL. 'This line will Jump to the DLL and execute during debug. plugins(CInt(tmp(0))).startup CInt(tmp(1)) 'this line works fine, passing an integer 'This line doesn't execute the DLL procedure but rather jumps immediately to the error handling routine in Excel. plugins(CInt(tmp(0))).getCustomerName (Customer) 'this line fails, passing in custom object Thanks Dan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Objects from Excel VBA collection to a VB6 DLL
I'm having difficulty visualising what's in the dll (parameter dec's etc) &
whets in VBA (it's late where I am!). First suggestion is turn off (comment) your error handling in VBA and F8 through, if you actually get into the VB6 routine look at locals. If nothing obvious create a new highly simplified illustrative example and post the full VBA/VB6. Regards, Peter T (signing off) "Dan" wrote in message ups.com... Hi Peter, I stepped through the code as you requested. The error message is coming from Microsoft Excel. When I put the VB6 dll into debug and call it from the Excel Addin the program execution jumps from Excel to the VB6 Editor and steps through the first procedure (startup) and then jumps back to the Excel VBA editor. Then when execution goes to the second procedure (getCustomerName), the program execution jumps immediately to my error handle routine in VBA, it never tries to execute the procedure in the DLL. 'This line will Jump to the DLL and execute during debug. plugins(CInt(tmp(0))).startup CInt(tmp(1)) 'this line works fine, passing an integer 'This line doesn't execute the DLL procedure but rather jumps immediately to the error handling routine in Excel. plugins(CInt(tmp(0))).getCustomerName (Customer) 'this line fails, passing in custom object Thanks Dan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Objects from Excel VBA collection to a VB6 DLL
Hello Peter,
I did as you said and for some reason this works. I created a new simplified class for my customer object called CustomerClass. I created a BuildSQL.dll that has two routines (Startup and GetCustomerName), the GetCustomerName is passed the object created from Excel, the Startup is passed an integer. Then I created a VBA user form in Excel that late binds to the BuildSQL.dll during the form Initialize event. Two buttons on the form call the procedures. The BuildSQL.dll contains a reference to the CustomerClass.dll and the Microsoft Excel workbook contains a reference to the CustomerClass.dll. The procedures just present a message box of the values passed to them. This works! The only thing different between this version and the previous version that wasn't working is in my customer class. The Customer Class declared public variables only; "Public Name As String". The second version uses the LET and GET properties (See below). I wonder if that made the difference? Thanks for your suggestions, Dan ============================== BuildSQL.DLL Option Explicit Sub StartUp(intArg As Integer) MsgBox "Startup " & CStr(App.EXEName) & " Int:=" & CStr(intArg) End Sub Sub GetCustomerName(cCustomer As Customer) MsgBox cCustomer.Name End Sub =============================== CustomerClass.DLL Option Explicit Private m_sName As String Private m_sPhone As String Public Property Get Name() As String Name = m_sName End Property Public Property Get Phone() As String Phone = m_sPhone End Property Public Property Let Name(ByVal sName As String) If m_sName < sName Then m_sName = sName End If End Property Public Property Let Phone(ByVal sPhone As String) If m_sPhone < sPhone Then m_sPhone = sPhone End If End Property ================================= Excel Workbook UserForm Option Explicit Dim MyPlugIn As Object Dim MyCustomer As Customer Private Sub CommandButton1_Click() MyPlugIn.StartUp 0 End Sub Private Sub CommandButton2_Click() Set MyCustomer = New Customer MyCustomer.Name = "Dan" MyCustomer.Phone = "333-333-3333" MyPlugIn.GetCustomerName MyCustomer Set MyCustomer = Nothing End Sub Private Sub UserForm_Initialize() Dim strDLL As String Dim progID As String On Error GoTo Error_Handle strDLL = "C:\Program Files\Microsoft Visual Studio\VB98\BuildSQL\BuildSQLProj.dll" If MsgBox("Did you register the dll file with regsvr32 already?", vbYesNo) = vbNo Then Shell "regsvr32 """ & strDLL & """", vbNormalFocus End If progID = GetBaseName(strDLL) & ".BuildSQL" Set MyPlugIn = CreateObject(progID) Debug.Print progID Exit Sub Error_Handle: MsgBox Err.Description Resume Next End Sub Function GetBaseName(path) As String Dim tmp() As String, ub tmp = Split(path, "\") ub = tmp(UBound(tmp)) If InStr(1, ub, ".") 0 Then GetBaseName = Mid(ub, 1, InStrRev(ub, ".") - 1) Else GetBaseName = ub End If End Function |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Objects from Excel VBA collection to a VB6 DLL
Hi Dan,
I take it you have been able to adapt your original to work same as the simplified. I'm not sure new use of Property is the reason it now works, or something else in the original was generating an error. Did run your VB6 with F5 in the IDE and step into it (would need temporary ref to the project and early binding to the project.class). For curiosity why not change - Private m_sName As String to Public m_sName As String and try both ways, ie direct and with Property. But I'd stick with Property even if the direct way works. In passing, do you need to ask if user has registered your dll. Why not something like On error resume next Set MyPlugIn = CreateObject(progID) if MyPlugIn is nothing then register it try CreateObject again ' (I'd do the CreateObject and Shell Reg stuff in separate functions with each returning a boolean indicating success) Regards, Peter T "Dan" wrote in message oups.com... Hello Peter, I did as you said and for some reason this works. I created a new simplified class for my customer object called CustomerClass. I created a BuildSQL.dll that has two routines (Startup and GetCustomerName), the GetCustomerName is passed the object created from Excel, the Startup is passed an integer. Then I created a VBA user form in Excel that late binds to the BuildSQL.dll during the form Initialize event. Two buttons on the form call the procedures. The BuildSQL.dll contains a reference to the CustomerClass.dll and the Microsoft Excel workbook contains a reference to the CustomerClass.dll. The procedures just present a message box of the values passed to them. This works! The only thing different between this version and the previous version that wasn't working is in my customer class. The Customer Class declared public variables only; "Public Name As String". The second version uses the LET and GET properties (See below). I wonder if that made the difference? Thanks for your suggestions, Dan ============================== BuildSQL.DLL Option Explicit Sub StartUp(intArg As Integer) MsgBox "Startup " & CStr(App.EXEName) & " Int:=" & CStr(intArg) End Sub Sub GetCustomerName(cCustomer As Customer) MsgBox cCustomer.Name End Sub =============================== CustomerClass.DLL Option Explicit Private m_sName As String Private m_sPhone As String Public Property Get Name() As String Name = m_sName End Property Public Property Get Phone() As String Phone = m_sPhone End Property Public Property Let Name(ByVal sName As String) If m_sName < sName Then m_sName = sName End If End Property Public Property Let Phone(ByVal sPhone As String) If m_sPhone < sPhone Then m_sPhone = sPhone End If End Property ================================= Excel Workbook UserForm Option Explicit Dim MyPlugIn As Object Dim MyCustomer As Customer Private Sub CommandButton1_Click() MyPlugIn.StartUp 0 End Sub Private Sub CommandButton2_Click() Set MyCustomer = New Customer MyCustomer.Name = "Dan" MyCustomer.Phone = "333-333-3333" MyPlugIn.GetCustomerName MyCustomer Set MyCustomer = Nothing End Sub Private Sub UserForm_Initialize() Dim strDLL As String Dim progID As String On Error GoTo Error_Handle strDLL = "C:\Program Files\Microsoft Visual Studio\VB98\BuildSQL\BuildSQLProj.dll" If MsgBox("Did you register the dll file with regsvr32 already?", vbYesNo) = vbNo Then Shell "regsvr32 """ & strDLL & """", vbNormalFocus End If progID = GetBaseName(strDLL) & ".BuildSQL" Set MyPlugIn = CreateObject(progID) Debug.Print progID Exit Sub Error_Handle: MsgBox Err.Description Resume Next End Sub Function GetBaseName(path) As String Dim tmp() As String, ub tmp = Split(path, "\") ub = tmp(UBound(tmp)) If InStr(1, ub, ".") 0 Then GetBaseName = Mid(ub, 1, InStrRev(ub, ".") - 1) Else GetBaseName = ub End If End Function |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Objects from Excel VBA collection to a VB6 DLL
Hi Peter,
As you suggested, I tried both ways; direct and with Property. It didn't make a difference. It continues to work either way. I am not sure why it is now working. Now that I know that this is possible and that I have a proof of concept to work from, I will go back to my original to see what the problem is. If I am able to find out, I'll post the resolution here. I agree about not asking the user to register the dll, it was just for testing purposes to remind me. Thanks for your help with this post, I really appreciate it. Dan |
Reply |
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 |