So I'm using excel 2010, and right now, I'm trying to use calculate the value of a variable in one sub, and want it to be used in multiple subs, except I can't figure out how to do this. Here is an example of what I'm using in its most basic form
Public Sub Calc()
Dim i As Integer
i = 10 - 5
End Sub
'Other sub that will use the value for i calculated in the Calc sub
Sub Macro()
y = i +5
End Sub
How am I suppose to be able to use/pass this value for "i" in/into the sub macro?
Move the Dim above the subs:
Public i As Integer
Public Sub Calc()
i = 10 - 5
End Sub
Sub Macro()
y = i + 5
MsgBox y
End Sub
Sub MAIN()
Call Calc
Call Macro
End Sub
Try running MAIN()
Insert new module and define i as:
Public i As Integer
Then you'll be able to use it whenever you want.
For further information, please see: Scope of variables in Visual Basic for Applications
I would like to use Function instead of Sub.
Sub Main()
Msgbox Macro(calc)
end Sub
Function Calc() as Integer
Dim i As Integer
i = 10 - 5
Calc = i
End Function
Function Macro(i as Integer) as Integer
Macro = i + 5
End Function
