Passing Variables to another macro

[Guest]

I am trying to modularize my code, but I'm having trouble with one aspect.
How do I make a variable such as a counter available within a macro that I
just called?

 

[Tom Ogilvy]

Option Explicit
Sub Main()
Dim icntr As Long
Dim adjNum As Long
For icntr = 1 To 10
MySub icntr
adjNum = MyFunc(icntr)
MsgBox "Adjusted Counter is " & adjNum
Next
End Sub

Sub MySub(num As Long)
If num < 5 Then
MsgBox "num: " & num & ", is less than 5"
Else
MsgBox "num: " & num & ", is greater" & _
" than or equal to 5"
End If
End Sub

Function MyFunc(myNum As Long)
Dim MyAdjustedNum As Long
MyAdjustedNum = myNum * myNum
MyFunc = MyAdjustedNum
End Function

 

[Rob van Gelder]

Here is an example:

Sub FirstProcedure()
Dim lngCounter As Long

lngCounter = 0

SecondProcedure lngCounter

MsgBox lngCounter

SecondProcedure lngCounter
SecondProcedure lngCounter
SecondProcedure lngCounter

MsgBox lngCounter
End Sub

Sub SecondProcedure(Counter As Long)
Counter = Counter + 1
End Sub

 

[Guest]

To retrieve the value of a variable from another routine you'll need to do
two simple things:

1) Ensure the routine which has to retrun the variable value is a Function
not a Sub. The primary difference between the two is that Functions are
capable of returning a value to a calling routine while Subs are not.

2) At the end of the function assign the value of the variable to the name
of the function.

Here's an example:


Sub Test()
MsgBox LoopAndCount()
End Sub



Function LoopAndCount() As Long
Dim i As Long
Dim Total As Long

For i = 1 To 100
Total = Total + i
Next i

LoopAndCount = Total

End Function


Note a couple of things. The function has a return type of Long which is the
same data type as the variable I want to get the value of. The moral here is:
whatever data type the variable is that you want to get the value of, make
the function return the same data type.

Let me know if you need a more specific example.

Good Coding!

OfficeHacker