VBA passing variables through a function

[Guest]

I have a macro

Sub TestMacro(X,Y)
....calculations based on X and Y values
End Sub

By putting the "X,Y" in the macro, does this make it a function. Because
when I try to look for the macro using the macro button on the toolbar the
function "TestMacro" does not show up. It shows up when I remove the X,Y.
Do you know what has changed? Also i this a good way to program, I want to
be able to input values for X and Y so I thought this would be good since I
wouldn't have to keep changing the "TestMacro".

Sub macro1()
X = 7
Y=5
Call TestMacro(X,Y)
End sub

 

[Bob Phillips]

I is not in the macro list because there is no way that you can pass the
arguments to a macro called from the macro list, Excel just does not provide
it, so it doesn't list.

It isn't a function. A function use Function instead of Sub.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

I used this subroutine.

Option Explicit
Sub TestMacro(X As Long, Y As Long)
MsgBox X + Y
End Sub

In excel, I hit tools|macro|macros and I typed:

'testmacro 3,5'

And I saw a message box with 8 in it.

But I think you may want to just ask for the values...

Option Explicit
Sub TestMacro()

Dim X As Double
Dim Y As Double

X = Application.InputBox("Please enter 1st number", Type:=1)
Y = Application.InputBox("please enter 2nd nubmer", Type:=1)

MsgBox X + Y

End Sub

You could also use the builtin Inputbox to get the values, too.

And once you decide that you don't want to go through 45 inputboxes, you may
want to make a userform to collect the information.

Debra Dalgleish has some get started instructions at:
http://contextures.com/xlUserForm01.html