How can I pass variables betweet two Subs in Excel VBA?

CorinneGermany

I know how to pass the variables between two Subs when calling a sub running the action from within the Sub containing the variables set up, but I want to call the variable setup Sub from within the routine macro but can not get the correct place to declare the variables to get it to work. I want to have the Variable set up separate to be called by other Subs. The code below does not work as the two subs

Sub X_Variables(s1, s2, s3, rng1, rng2, rng3)
'

' x - Defined Cell Names  - ANFlightText , AUFlightText , ABFlightText

Dim s1   As Worksheet
Dim s2   As Worksheet
Dim s3   As Worksheet
Dim rng1  As Range
Dim rng2  As Range
Dim rng3  As Range

Set s1 = Sheets("5_Angebot")        ' WorkSheet
Set s2 = Sheets("5_Auftragsb")      ' WorkSheet
Set s3 = Sheets("5_Abschluss")      ' WorkSheet
Set rng1 = s1.Range("B15")          ' End on cell
Set rng2 = s2.Range("B15")          ' End on cell
Set rng3 = s3.Range("B15")          ' End on cell

End Sub

Called by

Sub X_Offer_Hide_Flight_Text()
'
' ***** Hide Flight Text  *****

' x - Defined Cell Names  - ANFlightText , AUFlightText , ABFlightText
' x (s1, s2, s3, rng1, rng2, rng3)


'  ***** Set up veriables *****

Call X_Variables

' *****

' Hide rows
Application.ScreenUpdating = False  ' do not see screen updating

s1.Select
Range("ANFlightText").Select        ' x
Selection.EntireRow.Hidden = True
rng1.Select

s2.Select
Range("AUFlightText").Select        ' x
Selection.EntireRow.Hidden = True
rng2.Select

s3.Select
Range("ABFlightText").Select        ' x
Selection.EntireRow.Hidden = True
rng3.Select

s1.Select

Application.ScreenUpdating = True  ' see screen updating

End Sub

Christoph Corleis

You have to declare the variables as global, outside of the sub.

public s1   As Worksheet
public s2   As Worksheet
public s3   As Worksheet
public rng1  As Range
public rng2  As Range
public rng3  As Range

Sub X_Variables()
'

' x - Defined Cell Names  - ANFlightText , AUFlightText , ABFlightText

Set s1 = Sheets("5_Angebot")        ' WorkSheet
Set s2 = Sheets("5_Auftragsb")      ' WorkSheet
Set s3 = Sheets("5_Abschluss")      ' WorkSheet
Set rng1 = s1.Range("B15")          ' End on cell
Set rng2 = s2.Range("B15")          ' End on cell
Set rng3 = s3.Range("B15")          ' End on cell

End Sub

Collected from the Internet

Please contact [email protected] to delete if infringement.