Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there an alternative to Application.Run
I'd like to call sub routines that reside in another workbook. I know about
application.run but if I want to pass arguments byref it doesn't seem to work (i.e. the changed value is not passed back). In the Vb help on calling procedures with the same name there's this example:- Sub Main() [MyProject.vbp].[MyModule].Main End Sub I've tried renaming projects and using this:- Sub Main() [MyProject.xls].[MyModule].Hello End Sub ...but can't make it see my hello procedure. Where am I going wrong and once right will it allow me to do things like this:- Sub Main() a = 1 b = 2 [MyProject.xls].[MyModule].Swap(a,b) msgbox ("Swapped " & a & "," & b) End Sub Any help much appreciated! J.P. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there an alternative to Application.Run
Try it like this:
Application.Run "Workbook1.xls!Module1.Procedure1", Arg1, Arg2 RBS "John Pritchard" wrote in message ... I'd like to call sub routines that reside in another workbook. I know about application.run but if I want to pass arguments byref it doesn't seem to work (i.e. the changed value is not passed back). In the Vb help on calling procedures with the same name there's this example:- Sub Main() [MyProject.vbp].[MyModule].Main End Sub I've tried renaming projects and using this:- Sub Main() [MyProject.xls].[MyModule].Hello End Sub ..but can't make it see my hello procedure. Where am I going wrong and once right will it allow me to do things like this:- Sub Main() a = 1 b = 2 [MyProject.xls].[MyModule].Swap(a,b) msgbox ("Swapped " & a & "," & b) End Sub Any help much appreciated! J.P. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there an alternative to Application.Run
You would need to create a reference from the calling workbook to the
workbook with the procedure. Do this in the VBE under tools=References. Make sure you have given the called workbook a unique project name in properties (rather than the default VBProject). Note that whenever you open the referencing workbook, the referenced workbook will be opened as well. You can not close the referenced workbook until the referencing workbook is closed. Once the reference is established, you can call routines in the referenced workbook as if they were local. You wouldn't need to qualify with the workbook name or module names unless their were duplicate names in scope. -- Regards, Tom Ogilvy "John Pritchard" wrote: I'd like to call sub routines that reside in another workbook. I know about application.run but if I want to pass arguments byref it doesn't seem to work (i.e. the changed value is not passed back). In the Vb help on calling procedures with the same name there's this example:- Sub Main() [MyProject.vbp].[MyModule].Main End Sub I've tried renaming projects and using this:- Sub Main() [MyProject.xls].[MyModule].Hello End Sub ..but can't make it see my hello procedure. Where am I going wrong and once right will it allow me to do things like this:- Sub Main() a = 1 b = 2 [MyProject.xls].[MyModule].Swap(a,b) msgbox ("Swapped " & a & "," & b) End Sub Any help much appreciated! J.P. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there an alternative to Application.Run
Thanks for the reply BUT the problem is Arg1 and Arg2 are passed by value
i.e. I can't see the changes that the called routine made to them. "RB Smissaert" wrote: Try it like this: Application.Run "Workbook1.xls!Module1.Procedure1", Arg1, Arg2 RBS "John Pritchard" wrote in message ... I'd like to call sub routines that reside in another workbook. I know about application.run but if I want to pass arguments byref it doesn't seem to work (i.e. the changed value is not passed back). In the Vb help on calling procedures with the same name there's this example:- Sub Main() [MyProject.vbp].[MyModule].Main End Sub I've tried renaming projects and using this:- Sub Main() [MyProject.xls].[MyModule].Hello End Sub ..but can't make it see my hello procedure. Where am I going wrong and once right will it allow me to do things like this:- Sub Main() a = 1 b = 2 [MyProject.xls].[MyModule].Swap(a,b) msgbox ("Swapped " & a & "," & b) End Sub Any help much appreciated! J.P. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there an alternative to Application.Run
Thanks Tom - Just what I needed!!
"Tom Ogilvy" wrote: You would need to create a reference from the calling workbook to the workbook with the procedure. Do this in the VBE under tools=References. Make sure you have given the called workbook a unique project name in properties (rather than the default VBProject). Note that whenever you open the referencing workbook, the referenced workbook will be opened as well. You can not close the referenced workbook until the referencing workbook is closed. Once the reference is established, you can call routines in the referenced workbook as if they were local. You wouldn't need to qualify with the workbook name or module names unless their were duplicate names in scope. -- Regards, Tom Ogilvy "John Pritchard" wrote: I'd like to call sub routines that reside in another workbook. I know about application.run but if I want to pass arguments byref it doesn't seem to work (i.e. the changed value is not passed back). In the Vb help on calling procedures with the same name there's this example:- Sub Main() [MyProject.vbp].[MyModule].Main End Sub I've tried renaming projects and using this:- Sub Main() [MyProject.xls].[MyModule].Hello End Sub ..but can't make it see my hello procedure. Where am I going wrong and once right will it allow me to do things like this:- Sub Main() a = 1 b = 2 [MyProject.xls].[MyModule].Swap(a,b) msgbox ("Swapped " & a & "," & b) End Sub Any help much appreciated! J.P. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there an alternative to Application.Run
OK, I am sure it can work.
Try to make it a function with return values. You could make it return an array holding the 2 values. RBS "John Pritchard" wrote in message ... Thanks for the reply BUT the problem is Arg1 and Arg2 are passed by value i.e. I can't see the changes that the called routine made to them. "RB Smissaert" wrote: Try it like this: Application.Run "Workbook1.xls!Module1.Procedure1", Arg1, Arg2 RBS "John Pritchard" wrote in message ... I'd like to call sub routines that reside in another workbook. I know about application.run but if I want to pass arguments byref it doesn't seem to work (i.e. the changed value is not passed back). In the Vb help on calling procedures with the same name there's this example:- Sub Main() [MyProject.vbp].[MyModule].Main End Sub I've tried renaming projects and using this:- Sub Main() [MyProject.xls].[MyModule].Hello End Sub ..but can't make it see my hello procedure. Where am I going wrong and once right will it allow me to do things like this:- Sub Main() a = 1 b = 2 [MyProject.xls].[MyModule].Swap(a,b) msgbox ("Swapped " & a & "," & b) End Sub Any help much appreciated! J.P. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there an alternative to Application.Run
In Book1:
Sub test() Dim lVal As Long lVal = 1 MsgBox Application.Run("Book2.xls!Module1.TestFunction", lVal) End Sub In Book2: Function TestFunction(ByRef lVal As Long) As Long lVal = lVal + 1 TestFunction = lVal End Function I find setting references to other workbooks can cause a lot of trouble and I find it is best to avoid it if you can. RBS "John Pritchard" wrote in message ... Thanks for the reply BUT the problem is Arg1 and Arg2 are passed by value i.e. I can't see the changes that the called routine made to them. "RB Smissaert" wrote: Try it like this: Application.Run "Workbook1.xls!Module1.Procedure1", Arg1, Arg2 RBS "John Pritchard" wrote in message ... I'd like to call sub routines that reside in another workbook. I know about application.run but if I want to pass arguments byref it doesn't seem to work (i.e. the changed value is not passed back). In the Vb help on calling procedures with the same name there's this example:- Sub Main() [MyProject.vbp].[MyModule].Main End Sub I've tried renaming projects and using this:- Sub Main() [MyProject.xls].[MyModule].Hello End Sub ..but can't make it see my hello procedure. Where am I going wrong and once right will it allow me to do things like this:- Sub Main() a = 1 b = 2 [MyProject.xls].[MyModule].Swap(a,b) msgbox ("Swapped " & a & "," & b) End Sub Any help much appreciated! J.P. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Alternative add-on application to VLOOKUPS command? | Excel Discussion (Misc queries) | |||
Replace application.RTD property by Application.RTDServers collect | Excel Programming | |||
Alternative to Application.FileDialog (please)? | Excel Programming | |||
Alternative to Application.Match | Excel Programming | |||
Alternative to Application.Match | Excel Programming |