Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable retaining value between subroutins question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable retaining value between subroutins question
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable retaining value between subroutins question
Thank you very much, it worked well
"TroyW" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable calculation question | Excel Worksheet Functions | |||
Variable name question | Excel Discussion (Misc queries) | |||
Inserting and retaining an input variable | Excel Discussion (Misc queries) | |||
variable question | Excel Discussion (Misc queries) | |||
I Need VBA Assistance for global variable question | Excel Discussion (Misc queries) |