Application.run and ByRef arguments

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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).
 
Try dropping the ()'s in your application.run line and add a couple of "'s.

Application.Run "'Book1.xls'!Macro1", A, B
 
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)
 
So, should i use user-defined type as funtion return, or create user class?
Any other desicions?
 
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)
 
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. :(
Click to expand...
 
Back
Top