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

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" <(E-Mail Removed)> wrote in message
news:94456385-365D-4AAE-9A7C-(E-Mail Removed)...
> 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

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
>

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" <(E-Mail Removed)> wrote in message
> news:94456385-365D-4AAE-9A7C-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
newsBA2318D-3B9C-4F4B-94DD-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > news:94456385-365D-4AAE-9A7C-(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> newsBA2318D-3B9C-4F4B-94DD-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > > news:94456385-365D-4AAE-9A7C-(E-Mail Removed)...
> > > > 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
> > >
> > >
> > >
>
>
>

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