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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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