How can a private sub send back a variable to the calling sub?

[Edward Mendelson]

Hello,

For a rather huge macro that I'm writing, a sub calls two private
subroutines, which are performed multiple times as the macro loops through
different story ranges; the private subs attempt to perform various
replacements of text, and I know how to count the number of replacements
that each one makes. When the macro is finished, I want the calling
subroutine to display the total number of replacments made by the private
subs, but I can't figure out how to get the counts back from the private
subs to the calling subs. Is there an FAQ that would provide the answer, or
do I need to use functions instead of subroutines to accomplish this?

Many thanks for any help.

Edward Mendelson

 

[Jezebel]

Simply turn the subs into functions.

Private Function DoReplacement(MyArg as string) as long

..... (whatever you currently do)

DoReplacement = MyCount (whatever variable counts the number of
replacements)

End Function


Private Sub MainMacro()

Dim TotalCount as long

TotalCount = TotalCount + DoReplacement(Word1)
TotalCount = TotalCount + DoReplacement(Word2)
....
End Sub

It's also possible to return values using the arguments themselves. Doesn't
seem appropriate here, but sometimes useful, particularly if you need to
return more than one value. The arguments to be changed have to be passed
ByRef, not ByVal, for this to work.

Private Sub MyFunc(Arg1 as string, Arg2 as long)
....
Arg2 = MyCount
End sub

 

[Peter Hewett]

Hi Edward Mendelson

There are three solutions to this problem:

1. Use variables declared at the module level, ok for classes bad not good practice
otherwise.
2. Use a Function rather than a Sub, this is ok as long as you only want to return one
value.
3. Pass the parameters you want changed by your Sub to your Sub using ByRef rather than
ByVal.

HTH + Cheers - Peter

 

[Edward Mendelson]

Simply turn the subs into functions. ....
It's also possible to return values using the arguments themselves. Doesn't
seem appropriate here, but sometimes useful, particularly if you need to
return more than one value. The arguments to be changed have to be passed
ByRef, not ByVal, for this to work.

Private Sub MyFunc(Arg1 as string, Arg2 as long)
....
Arg2 = MyCount
End sub

Perfect. Thank you!!

Edward Mendelson