Hello, all.
I want to run Macro1 from Book1.xls in Macro2 (Book2.xls).
Macro1 has two arguments ByRef:
Public Sub Macro1 (ByRef X As Integer, ByRef Y As Integer)
.....
X = X * 2
Y = Y * 3
End Sub

How can I execute Macro1 with passing changed value out from Macro1.
Following code doesn't work:
A = 10
B = 20
Application.run('Book1.xls!Macro1', A, B)
I think Excel passes argyments ByVal (only value, but not reference).
--
Alexander

Try dropping the ()'s in your application.run line and add a couple of "'s.

Application.Run "'Book1.xls'!Macro1", A, B



AsIs wrote:

Hello, all.
I want to run Macro1 from Book1.xls in Macro2 (Book2.xls).
Macro1 has two arguments ByRef:
Public Sub Macro1 (ByRef X As Integer, ByRef Y As Integer)
....
X = X * 2
Y = Y * 3
End Sub

How can I execute Macro1 with passing changed value out from Macro1.
Following code doesn't work:
A = 10
B = 20
Application.run('Book1.xls!Macro1', A, B)
I think Excel passes argyments ByVal (only value, but not reference).
--
Alexander

--

Dave Peterson

Same effect. :(
--
Чтобы знать: < http://forums.avalon.ru/forum/


"Dave Peterson" пишет:

Try dropping the ()'s in your application.run line and add a couple of "'s.

Application.Run "'Book1.xls'!Macro1", A, B



AsIs wrote:

Hello, all.
I want to run Macro1 from Book1.xls in Macro2 (Book2.xls).
Macro1 has two arguments ByRef:
Public Sub Macro1 (ByRef X As Integer, ByRef Y As Integer)
....
X = X * 2
Y = Y * 3
End Sub

How can I execute Macro1 with passing changed value out from Macro1.
Following code doesn't work:
A = 10
B = 20
Application.run('Book1.xls!Macro1', A, B)
I think Excel passes argyments ByVal (only value, but not reference).
--
Alexander

--

Dave Peterson

Sorry, I looked at your post and saw the syntax errors and thought that was your
problem.

Can you rename the project in Book1.xls (away from the default name VBAProject),
then in the second workbook, you can add a reference to that project
(tools|references).

Then this worked ok for me:

Inside Book1.xls:

Option Explicit
Public Function myFunction(x As Long, y As Long) As Boolean

x = x * 2
y = y * 2
myFunction = True

End Function

Inside OtherBook.xls:

Option Explicit
Sub testme2()
Dim a As Long
Dim b As Long
Dim c As Boolean

a = 2
b = 4
c = myFunction(a, b)

Debug.Print a & "-" & b
End Sub

I got:
4-8
in the immediate window.

Not quite as general as application.run, but maybe it'll work for
you--especially if you're hardcoding the workbook's name in that application.run
statement.


AsIs wrote:

Same effect. :(
--
Чтобы знать: < http://forums.avalon.ru/forum/

"Dave Peterson" пишет:

Try dropping the ()'s in your application.run line and add a couple of "'s.

Application.Run "'Book1.xls'!Macro1", A, B



AsIs wrote:

Hello, all.
I want to run Macro1 from Book1.xls in Macro2 (Book2.xls).
Macro1 has two arguments ByRef:
Public Sub Macro1 (ByRef X As Integer, ByRef Y As Integer)
....
X = X * 2
Y = Y * 3
End Sub

How can I execute Macro1 with passing changed value out from Macro1.
Following code doesn't work:
A = 10
B = 20
Application.run('Book1.xls!Macro1', A, B)
I think Excel passes argyments ByVal (only value, but not reference).
--
Alexander

--

Dave Peterson


--

Dave Peterson

You are correct, it is ByVal.

A function can return a value, but just one with ByRef constrained.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"AsIs" wrote in message
...
Hello, all.
I want to run Macro1 from Book1.xls in Macro2 (Book2.xls).
Macro1 has two arguments ByRef:
Public Sub Macro1 (ByRef X As Integer, ByRef Y As Integer)
....
X = X * 2
Y = Y * 3
End Sub

How can I execute Macro1 with passing changed value out from Macro1.
Following code doesn't work:
A = 10
B = 20
Application.run('Book1.xls!Macro1', A, B)
I think Excel passes argyments ByVal (only value, but not reference).
--
Alexander

So, should i use user-defined type as funtion return, or create user class?
Any other desicions?
--
Alexander


"Bob Phillips" пишет:

You are correct, it is ByVal.

A function can return a value, but just one with ByRef constrained.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"AsIs" wrote in message
...
Hello, all.
I want to run Macro1 from Book1.xls in Macro2 (Book2.xls).
Macro1 has two arguments ByRef:
Public Sub Macro1 (ByRef X As Integer, ByRef Y As Integer)
....
X = X * 2
Y = Y * 3
End Sub

How can I execute Macro1 with passing changed value out from Macro1.
Following code doesn't work:
A = 10
B = 20
Application.run('Book1.xls!Macro1', A, B)
I think Excel passes argyments ByVal (only value, but not reference).
--
Alexander

I would use a function(s), and capture returned value(s).

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"AsIs" wrote in message
...
So, should i use user-defined type as funtion return, or create user
class?
Any other desicions?
--
Alexander


"Bob Phillips" ?????:

You are correct, it is ByVal.

A function can return a value, but just one with ByRef constrained.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"AsIs" wrote in message
...
Hello, all.
I want to run Macro1 from Book1.xls in Macro2 (Book2.xls).
Macro1 has two arguments ByRef:
Public Sub Macro1 (ByRef X As Integer, ByRef Y As Integer)
....
X = X * 2
Y = Y * 3
End Sub

How can I execute Macro1 with passing changed value out from Macro1.
Following code doesn't work:
A = 10
B = 20
Application.run('Book1.xls!Macro1', A, B)
I think Excel passes argyments ByVal (only value, but not reference).
--
Alexander

Several functions? Hmmm...
Non procedural style.
OK. Nevertheless thanks for your advice.

--
Alexander


"Bob Phillips" пишет:

I would use a function(s), and capture returned value(s).

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"AsIs" wrote in message
...
So, should i use user-defined type as funtion return, or create user
class?
Any other desicions?
--
Alexander


"Bob Phillips" ?????:

You are correct, it is ByVal.

A function can return a value, but just one with ByRef constrained.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"AsIs" wrote in message
...
Hello, all.
I want to run Macro1 from Book1.xls in Macro2 (Book2.xls).
Macro1 has two arguments ByRef:
Public Sub Macro1 (ByRef X As Integer, ByRef Y As Integer)
....
X = X * 2
Y = Y * 3
End Sub

How can I execute Macro1 with passing changed value out from Macro1.
Following code doesn't work:
A = 10
B = 20
Application.run('Book1.xls!Macro1', A, B)
I think Excel passes argyments ByVal (only value, but not reference).
--
Alexander