How do I pass a value from one macro to another? For example (simplified code)

sub aa()
mynum = 12
Application.Run "bb" ' pass the value of mynum to "bb"
' get the value of myvalue back from "bb"
'do some other stuff in "aa"
end Sub
----------------------------
Sub bb()
counter = 1
do while counter <= mynum
' code to execute in loop
counter = counter + 1
loop
myvalue = 66 ' pass the value of myvalue back to "aa"
end sub

Any help much appreciated, thanks, Brett

On Nov 6, 10:28 pm, Brettjg wrote:
How do I pass a value from one macro to another? For example (simplified code)

sub aa()
mynum = 12
Application.Run "bb" ' pass the value of mynum to "bb"
' get the value of myvalue back from "bb"
'do some other stuff in "aa"
end Sub
----------------------------
Sub bb()
counter = 1
do while counter <= mynum
' code to execute in loop
counter = counter + 1
loop
myvalue = 66 ' pass the value of myvalue back to "aa"
end sub

Any help much appreciated, thanks, Brett


You need to do three things:
1. Change Sub bb() to Function bb(mynum). "End Sub" will
automatically change to "End Function".
2. At the end of Function bb, just before "End Function", insert the
statement bb = myvalue. This statement assigns a value that the
function will return to the caller upon completion of the called
function.
3. From within Sub aa, instead of calling Application.Run "bb", use
this: myvalue = bb(mynum). That will call bb and pass mynum as the
argument to it, and at the same time return a value and assign it to
myvalue.

Having said that... Now, a word to the wise: you are strongly
discouraged from naming input arguments the same as function
parameters. To the computer it makes no difference here, but it
creates chaos and confusion for you a year from now when you revisit
your code, as well as for anyone else who will try to read it. In
other words, "mynum" is Sub aa and "mynum" in Function bb do not need
to have the same names, nor should they be named the same. For
example, you could change mynum to inputNum within Function bb, and
things would still work just as smoothly (and the rest of us will
thank you!).

All the above will still work even if Sub aa is in one module, and
Function bb is in another within a VBA project, as long as they are
both Public (which is true in your example). I humbly suggest reading
up Help on Functions and Subs for more information.

Cheers,
-Basilisk96

Sorry for my typo:
"mynum" is Sub aa and "mynum" in Function bb do not need <<<

that should be:
"mynum" in Sub aa and "mynum" in Function bb do not need <<<

;-)

Thankyou very much, I'll put that in and run it, Regards Brett

"Basilisk96" wrote:

On Nov 6, 10:28 pm, Brettjg wrote:
How do I pass a value from one macro to another? For example (simplified code)

sub aa()
mynum = 12
Application.Run "bb" ' pass the value of mynum to "bb"
' get the value of myvalue back from "bb"
'do some other stuff in "aa"
end Sub
----------------------------
Sub bb()
counter = 1
do while counter <= mynum
' code to execute in loop
counter = counter + 1
loop
myvalue = 66 ' pass the value of myvalue back to "aa"
end sub

Any help much appreciated, thanks, Brett


You need to do three things:
1. Change Sub bb() to Function bb(mynum). "End Sub" will
automatically change to "End Function".
2. At the end of Function bb, just before "End Function", insert the
statement bb = myvalue. This statement assigns a value that the
function will return to the caller upon completion of the called
function.
3. From within Sub aa, instead of calling Application.Run "bb", use
this: myvalue = bb(mynum). That will call bb and pass mynum as the
argument to it, and at the same time return a value and assign it to
myvalue.

Having said that... Now, a word to the wise: you are strongly
discouraged from naming input arguments the same as function
parameters. To the computer it makes no difference here, but it
creates chaos and confusion for you a year from now when you revisit
your code, as well as for anyone else who will try to read it. In
other words, "mynum" is Sub aa and "mynum" in Function bb do not need
to have the same names, nor should they be named the same. For
example, you could change mynum to inputNum within Function bb, and
things would still work just as smoothly (and the rest of us will
thank you!).

All the above will still work even if Sub aa is in one module, and
Function bb is in another within a VBA project, as long as they are
both Public (which is true in your example). I humbly suggest reading
up Help on Functions and Subs for more information.

Cheers,
-Basilisk96

Hi Basil, well, it worked first time! The code is of course much more
complicated than I had submitted on here. I was previously doing the same
thing with a series of GOTO statements, which I hate doing because it creates
spagetti. Thanks again.

"Basilisk96" wrote:

On Nov 6, 10:28 pm, Brettjg wrote:
How do I pass a value from one macro to another? For example (simplified code)

sub aa()
mynum = 12
Application.Run "bb" ' pass the value of mynum to "bb"
' get the value of myvalue back from "bb"
'do some other stuff in "aa"
end Sub
----------------------------
Sub bb()
counter = 1
do while counter <= mynum
' code to execute in loop
counter = counter + 1
loop
myvalue = 66 ' pass the value of myvalue back to "aa"
end sub

Any help much appreciated, thanks, Brett


You need to do three things:
1. Change Sub bb() to Function bb(mynum). "End Sub" will
automatically change to "End Function".
2. At the end of Function bb, just before "End Function", insert the
statement bb = myvalue. This statement assigns a value that the
function will return to the caller upon completion of the called
function.
3. From within Sub aa, instead of calling Application.Run "bb", use
this: myvalue = bb(mynum). That will call bb and pass mynum as the
argument to it, and at the same time return a value and assign it to
myvalue.

Having said that... Now, a word to the wise: you are strongly
discouraged from naming input arguments the same as function
parameters. To the computer it makes no difference here, but it
creates chaos and confusion for you a year from now when you revisit
your code, as well as for anyone else who will try to read it. In
other words, "mynum" is Sub aa and "mynum" in Function bb do not need
to have the same names, nor should they be named the same. For
example, you could change mynum to inputNum within Function bb, and
things would still work just as smoothly (and the rest of us will
thank you!).

All the above will still work even if Sub aa is in one module, and
Function bb is in another within a VBA project, as long as they are
both Public (which is true in your example). I humbly suggest reading
up Help on Functions and Subs for more information.

Cheers,
-Basilisk96