Application.run and ByRef arguments

A

AsIs

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).
 
D

Dave Peterson

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

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

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)
 
A

AsIs

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

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)
 
A

AsIs

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

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.

Same effect. :(
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top