Application.Run with multiple workbooks

[Guest]

I have a problem with the Application.Run method. If you have two workbooks open, each with a code module, Application.Run seems to only work with one of the workbooks at a time and Application.Activate does not change which module's procedures will work

For example, open Excel 2000 (Book1) go to the VBA editor and type workbooks.add in the debug window. Add a module to each of the VBAprojects. Put Sub A containing msgbox("A") in one module and Sub B containing Msgbox("B") in the other.

Type Call A in the immediate pane and you get "Sub or function not defined". Workbooks(1).activate does not help. Using the syntax project.module.procedure does not help either

I need to call a procedure in the second Project from a procedure in the first using Application.Run(ProcName) - how do I do it?

 

[Patrick Molloy]

in my test, book2 has a sub called 'HelloWorld'
from another open workbook

Application.Run "book2!HelloWorld"


The sub must be public (which is the default) and must be
in a standard code module

Patrick Molloy
Microsoft Excel MVP
-------------------
I Feel Great

-----Original Message-----
I have a problem with the Application.Run method. If you

have two workbooks open, each with a code module,
Application.Run seems to only work with one of the
workbooks at a time and Application.Activate does not
change which module's procedures will work.

For example, open Excel 2000 (Book1) go to the VBA

editor and type workbooks.add in the debug window. Add a
module to each of the VBAprojects. Put Sub A containing
msgbox("A") in one module and Sub B containing Msgbox
("B") in the other.

Type Call A in the immediate pane and you get "Sub or

function not defined". Workbooks(1).activate does not
help. Using the syntax project.module.procedure does not
help either.

I need to call a procedure in the second Project from a

procedure in the first using Application.Run(ProcName) -
how do I do it?

 

[Peter Huang]

Hi,

What do you mean by compiling the code into dll?
Do you mean make a com addin dll, and invoke the macro from the com addin?

I have tried to make an addin in VB6 and call a similar macro with optional
parameter, it seems to run smoothly on my side.
Here is the code I run from the addin
Private Sub AddinInstance_OnConnection(ByVal Application As Object, ByVal
ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As
Object, custom() As Variant)
On Error GoTo error_handler
Set exApp = Application
exApp.Visible = True
exApp.Workbooks.Open "c:\book1.xls"
exApp.Workbooks.Open "c:\book2.xls"
exApp.Run "Book1.xls!Test", "LocalPrinter", 1
exApp.Run "Book2.xls!Test", "LocalPrinter", 2
Exit Sub
error_handler:
MsgBox Err.Description
End Sub

If I have any misunderstanding, please feel free to let me know.

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.

 

[Guest]

The original app is written in VB6 and uses remote auomation to produce about 600 large spreadsheets. Each one involves opening an existing workbook, inserting data and calling a macro to print it. The problem is that the inter-process communication is very slow. In an attempt to speed things up, I compiled a chunk of my VB6 app into an active x dll. I then created a reference to the DLL in an excel workbook and added a procedure to the workbook to call the DLL, passing it a reference to to the workbook. My code is now running in the Excel stack and the spreadsheets are created about 15 times faster. Since the spreadsheets are based on an existing spreadsheet format, the dll uses the workbook's application object to work with the second workbook. The second workbook is opened/filled with data/printed/closed

The printing phase has to be performed by a macro in the second workbook. It is calling the macro in the second workbook from the compiled DLL invoked in a procedure call in the first workbook that causes the "Method '~' of object '~' " error message. Calling the second workbook's print procedure from VBA in the first workbook is OK, and calling it via remote automation from VB is OK as well. The frustrating thing is that at the point that the error occurs, all of the work has been done! Furthermore, the use of the active x dll call has speeded up the spreadsheet production by a factor of 15.

 

[Peter Huang]

Hi John,

I am sorry I can not reproduce the behavior on my side.
Here is my test steps.
1.Make a VB6 dll
Public Sub Test(ByRef exApp As Application)
exApp.Run "Book1.xls!Test", "LocalPrinter", 2
exApp.Run "Book2.xls!Test", "LocalPrinter", 2
End Sub

Public Sub Test2(ByRef exWk As Workbook)
exWk.Application.Run "Book1.xls!Test", "LocalPrinter", 2
exWk.Application.Run "Book2.xls!Test", "LocalPrinter", 2
End Sub

2.Add a module to the Book1.xls
Sub Test(Optional PrinterName As String = "", Optional PrintCopies As
Integer = 1)
MsgBox "book1" & PrinterName & CStr(PrintCopies)
End Sub
Sub fs()
Dim o As New Project1.Class1
o.Test Application
o.Test2 ThisWorkbook
o.Test2 Application.Workbooks(1)
o.Test2 Application.Workbooks(2)
End Sub

3.Add a module to the Book2.xls
Sub Test(Optional PrinterName As String = "", Optional PrintCopies As
Integer = 1)
MsgBox "book2" & PrinterName & CStr(PrintCopies)
End Sub
Sub fds()
Dim o As New Project1.Class1
o.Test Application
o.Test2 Application.Workbooks(2)
o.Test2 Application.Workbooks(1)
End Sub


You may have a try and let me know the result.
Also can you send me a simple reproduce sample(excel xls file and the vb6
dll with the source code) by removing the "online" from my email address.

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.

 

[Guest]

Hi Peter

I will try to make a simplified version to identify the problem more closely. The Active X DLL that fills the woorkbooks runs fine if created within VB and passed a workbook created with remote automation, but I get problems when the DLL is called directly from Excel. Any Excel call containing named parameters (ie Paste:=xlValues) or with missing optional parameters (such as worksheet.move ,Worksheets("G0") ) causes "Method '~' of object '~' error messages). I will let you know when I have made a simpler example. When I tested the use of the DLL in Excel, simply loading data, everything worked fine.