Setting Variable Questions

[sgfromny]

How Do I set a Cell Value in an Excel Worksheet to a VBA Variable that is accessable to all my code in a workbook?

 

[PHV]

Simply use a Public variable.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sgfromny]

Thanks - Im a newbie with this - could you please post some code on how to pull the value from the cell?

 

[GVF]

Public MyVariable as Variant

Goes in the declarations area at the top of a module above your first procedure or function. Once you have declared a Public variable you should not have to use a Dim statement anywhere for that particular variable. The Value of MyV will then be accessible to any procedure or function within the VBA project regardless of the module the procedure or function resides in.

Use the variant variable type when you are unsure of whether the cell will contain text or a number or an error. The Variant type has a high system overhead. If you can use a specific type of variable (String, Long, Integer etc) then it is good practice to do so.

MyVariable = Activecell.Value 'sets the variable to whatever the currently selected cell is equal to.

MyVariable = activecell.offset(0,1) 'sets the variable to the value of the cell in the same row and 1 column to the right of the active cell.

MyVariable = cells(2,3).value 'implicitly sets the variable to the value of the contents of cell R2C3.

MyVariable = cells(activecell.row + 1, 10) 'sets the value of the variable to the cell 1 row down from the active cell and always column 10

If you set MyVariable = to the contents of an empty cell then:
If you dim MyVariable as a string then
MyVariable = "" (a 0 length string)
if you dim MyVariable as one of the number types then
MyVariable = 0
if you dim MyVariable as a Variant then
MyVariable = Empty

Public MyV as Variant

Sub MyProcedure()
Cells(2,3).Select
MyV = ActiveCell.Value
MsgBox "The MyV variable = " & MyV
End Sub

or

Public MyV as Integer

Sub MyProcedure()
Cells(2,3).Select
Activecell.value = "Stuff"
MyV = ActiveCell.Value 'Will cause an error because MyV was declared as a number type of variable and the cell contains text. Your code would stop here and the debugger would start unless you declare how to handle an error in the code (On Error Resume Next or other error handler) prior to setting the value of MyV.

End Sub


Greg

 

[MajP]

The whole story

Scope refers to the availability of a variable, constant, or procedure for use by another procedure. There are three scoping levels: procedure-level, private module-level, and public module-level.
You determine the scope of a variable when you declare it. It's a good idea to declare all variables explicitly to avoid naming-conflict errors between variables with different scopes.

Defining Procedure-Level Scope

A variable or constant defined within a procedure is not visible outside that procedure. Only the procedure that contains the variable declaration can use it. In the following example, the first procedure displays a message box that contains a string. The second procedure displays a blank message box because the variable strMsg is local to the first procedure.

Sub LocalVariable()
Dim strMsg As String
strMsg = "This variable can't be used outside this procedure."
MsgBox strMsg
End Sub

Sub OutsideScope()
MsgBox strMsg
End Sub

Defining Private Module-Level Scope

You can define module-level variables and constants in the Declarations section of a module. Module-level variables can be either public or private. Public variables are available to all procedures in all modules in a project; private variables are available only to procedures in that module. By default, variables declared with the Dim statement in the Declarations section are scoped as private. However, by preceding the variable with the Private keyword, the scope is obvious in your code.

In the following example, the string variable strMsg is available to any procedures defined in the module. When the second procedure is called, it displays the contents of the string variable strMsg in a dialog box.

' Add following to Declarations section of module.
Private strMsg sAs String

Sub InitializePrivateVariable()
strMsg = "This variable can't be used outside this module."
End Sub

Sub UsePrivateVariable()
MsgBox strMsg
End Sub

Note Public procedures in a standard module or class module are available to any referencing project. To limit the scope of all procedures in a module to the current project, add an Option Private Module statement to the Declarations section of the module. Public variables and procedures will still be available to other procedures in the current project, but not to referencing projects.

Defining Public Module-Level Scope

If you declare a module-level variable as public, it's available to all procedures in the project. In the following example, the string variable strMsg can be used by any procedure in any module in the project.

' Include in Declarations section of module.
Public strMsg As String

All procedures are public by default, except for event procedures. When Visual Basic creates an event procedure, the Private keyword is automatically inserted before the procedure declaration. For all other procedures, you must explicitly declare the procedure with the Private keyword if you do not want it to be public.

You can use public procedures, variables, and constants defined in standard modules or class modules from referencing projects. However, you must first set a reference to the project in which they are defined.

Public procedures, variables, and constants defined in other than standard or class modules, such as form module
s or report modules, are not available to referencing projects, because these modules are private to the project in which they reside

 

[SkipVought]

Hi,

A short answer is

Public MyVariable

Sub MySub()
...
  MyVariable = ActiveWorkbook.Worksheets("SomeSheetName").Range("A1").Value
...
End Sub

Become familiar with the Excel Object Model.

Cells are Range Objects.

Range Objects are Children of a Worksheet Object.

Worksheet Objects are Children of a Workbook Object.

Skip,
[sub]
[red]Be Advised![/red] Dyslexic poets...
write inverse! [/sub]

 

[sgfromny]

WOW! Thanks for all your time to explain that!

With Gratitude - Steve