Run macro in another workbook

[nuked]

Hi All,

I currently have a routine (Excel 2007) that opens another workbook (Excel 2003) and runs a routine within it:

Application.Run "MyWorkbook.xls!MyRoutine"

Is it possible to pass parameters to MyRoutine, and, be able to get a return value using application.run method? I'm struggling with the syntax.

If it's not possible, is there an alternate way to do this? Perhaps a class module? Can a workbook instantiate an object in it's scope that is defined in another workbook?

Thanks

 

[Zakkala]

I've just asked a very similar thing - I can pass parameters forward but can't seem to get them on the return leg.

This works for the first part for me:

Application.Run "'WorkbookName'!MacroName", Variable1, Variable2, etc

Will be watching with interest for advice on getting them to return the other way!

 

[nuked]

I had the idea of creating my own Class Module with one read/write boolean, and then passing this object (byref) as a parameter to the routine in the other workbook. Nice idea, but the help file says that all object variables are converted to values when passed using Application.Run

I think actually this is a bigger problem of scope. Whilst programme execution is passed to the second workbook, it is not done so within the scope of the initial routine. In effect (I think) control is being passed upwards from our initial workbook to the application, and then downwards to the second workbook. I haven't as yet tried seeing whether Public declared variables are accessible between workbooks but I'll give it a go and report back.

Of course, there's a lot of work-arounds to this - storing parameters and return values in a worksheet in the second workbook and read/write them from the initial workbook etc.

 

[nuked]

Just tried using global variables in a module but these are not accessible from other workbooks. However, this is not true for objects, such as instantiated Class Modules, and, interestingly, Sheets. So I declared a public variable in Sheet1, and placed my code within it that runs the other workbook's routine. I then got the other routine to change the value of the public variable in the first sheet.

Any betters options welcome though...



First WB, in sheet code.

Option Explicit
Public test As Boolean
Sub ExampleRoutine()
Application.Run "Book11.xlsm!Example"
Debug.Print test
End Sub

Second WB, module code

Option Explicit
Sub Example()
Workbooks(2).Sheets("Sheet1").test = True
End Sub