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.