Call VBA procedures from VB6 DLL

[Paul Oulton]

I know how to call VB6 DLL procedures from VBA, but is it even possible the
other way round, that is, call Excel VBA procedures from a DLL?
Regards, Paul

 

[Wei-Dong XU [MSFT]]

Hi Paul,

Currently we are finding one support engineer for you on this issue. We
will upate this thread with more information as soon as possible.

Best Regards,
Wei-Dong XU
Microsoft Product Support Services
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Tushar Mehta]

Check out the Run method of the Application object.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Paul Oulton]

Hi,

Thanks for the suggestion. However, I am starting from VBA and
Application.Run starts Excel again with the same workbook open.

I am told it is possible to place the VBA procedures into a class module and
pass an instance of the class to the VB6 DLL. Unfortunately, that did not
work either because of the following error message:

A property or method call cannot include a reference to a private object,
either as an argument or as a return value.
The code is as follows:

In VBA:

Dim aProcLib As clsGeneral

Set pConnect = New XWarePayroll.Connect

Set aProcLib = New clsGeneral

pConnect.SetExcelProcedures aProcLib The above line causes the above error.

clsGeneral is a class module with one sub procedure(gStartProc).

XWarePayroll is the DLL with class module Connect.

In VB6:

In class module Connect:

Sub SetExcelProcedures(aProcedures)

Set pExcelProcedures = aProcedures

End Sub

pExcelProcedures is a public variable in a module.

Though it never got to this point, the gStartProc procedure would be called
as follows:

pExcelProcedures.gStartProc True

 

[Peter Huang [MSFT]]

Hi

Thanks for your quickly reply!
Here is my test code for your reference.
[VBA Macro]
Sub MyTestMacro()
Dim oVB6 As New TestVBA.CallVBAObj
Dim oVBA As New MyVBAClass
oVB6.TestCallVBA oVBA
End Sub
[VBA Class]
Public Function Test() As String
Test = "Hello"
End Function


[VB6 Class]
Public Sub TestCallVBA(obj As Object)
MsgBox obj.Test
End Sub



Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Yan-Hong Huang[MSFT]]

Hello Paul,

I am reviewing the issue thread. Does Peter's suggest help you resolve it?
If there is any question, please feel free to reply here and we will follow
up.

Thanks very much.

Best regards,
Yanhong Huang
Microsoft Community Support

Get Secure! ¨C www.microsoft.com/security
Register to Access MSDN Managed Newsgroups!
-http://support.microsoft.com/default.aspx?scid=/servicedesks/msdn/nospam.as
p&SD=msdn

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[RB Smissaert]

You can do it like this:

Dim oXL As Excel.Application
Set oXL = GetObject(, "Excel.Application")

oXL.Run "Test.xla!Module1.RunFromVB"

Test.xla is the workbook, in this case a .xla file, it should work the same
with a .xls file.
Module1 is the module where the Sub is.
RunFromVB is the Sub.
The VB project will need a reference to Excel.

RBS