variable retaining value between subroutins question

[Barmaley]

Is there a way for the variable to retain it's value to use it in another
Sub?
See example. I need for Cell_Transfer to keep it's value to use it in
Static_Test or elsewhere

please help



Sub Static_Test()
Call Find_Next_Cell
MsgBox Cell_Transfer
End Sub

Sub Find_Next_Cell()
Dim My_Cell As Range
Static Cell_Transfer

For Each My_Cell In Range("D6:IV6")
If My_Cell.Value = "" Then GoTo when_blank
Next
when_blank:

Cell_Transfer = My_Cell.Address
End Sub

 

[TroyW]

See "scoping" in the VBA Help for a detailed explanation.

A variable declared inside a procedure will not be seen by another
procedure. If you declare the variable at the module level, using "Private",
it can be seen by all procedures in the given module. If you declare the
variable as "Public" it will be seen by all procedures on all module pages
within the project.

IMPORTANT: Remove any declarations of Cell_Transfer from within a procedure.
Declaring a variable at the project level AND within a procedure can be
quite maddening. They are essentially two different variables that happen to
share the same name. Your code will appear to not do what you want.

You can also remove the line of code: "Static Cell_Transfer"

Troy


----At the VERY TOP of the module page:

Option Explicit
Public Cell_Transfer As String '''<=== This is the largest scope
possible.


Sub Static_Test()
Call Find_Next_Cell
MsgBox Cell_Transfer
End Sub

Sub Find_Next_Cell()
Dim My_Cell As Range
Static Cell_Transfer '''<=== REMOVE THIS LINE OF CODE

For Each My_Cell In Range("D6:IV6")
If My_Cell.Value = "" Then GoTo when_blank
Next
when_blank:

Cell_Transfer = My_Cell.Address
End Sub

 

[barmaley]

Thank you very much, it worked well

See "scoping" in the VBA Help for a detailed explanation.

A variable declared inside a procedure will not be seen by another
procedure. If you declare the variable at the module level, using "Private",
it can be seen by all procedures in the given module. If you declare the
variable as "Public" it will be seen by all procedures on all module pages
within the project.

IMPORTANT: Remove any declarations of Cell_Transfer from within a procedure.
Declaring a variable at the project level AND within a procedure can be
quite maddening. They are essentially two different variables that happen to
share the same name. Your code will appear to not do what you want.

You can also remove the line of code: "Static Cell_Transfer"

Troy


----At the VERY TOP of the module page:

Option Explicit
Public Cell_Transfer As String '''<=== This is the largest scope
possible.


Sub Static_Test()
Call Find_Next_Cell
MsgBox Cell_Transfer
End Sub

Sub Find_Next_Cell()
Dim My_Cell As Range
Static Cell_Transfer '''<=== REMOVE THIS LINE OF CODE

For Each My_Cell In Range("D6:IV6")
If My_Cell.Value = "" Then GoTo when_blank
Next
when_blank:

Cell_Transfer = My_Cell.Address
End Sub