CODEWITHSUNDEEP
excelvbaexcel-2010
bakeson
bakeson

Reputation: 91

Use Variable in multiple subs VBA

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?

Upvotes: 2

Views: 12523

Answers (3)

PaichengWu
PaichengWu

Reputation: 2689

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

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

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()

Upvotes: 3

Maciej Los
Maciej Los

Reputation: 8591

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

Upvotes: 2

Related Questions