Global Variable?

[megaxoom]

Hello

I wonder is there a way to create a variable in a Workbook, and then access this variable in the Module? Thanks for any help.

 

[Juan Pablo González]

I guess the answer is Yes.. but can you give more details ? because I'm not sure how you want to use/assign this variable.

 

[megaxoom]

Here is what I plan to do:

Whenever a user opens my excel program, the program will ask user for some information. These information will be stored in the Workbook. In the Module, I have written many functions that will use these information to do some work. So what do I to access these information stored in the Workbook.

 

[Nimrod]

If you have several Visual Basic modules in your workbook and want to use the same variable in all the modules in the workbook, declare it with the word Public or Global instead of Dim. This declaration must be at the very top of your module, above any macros or functions you have, but below option statements like Option Explicit. For example:

Public GLB_TestVariable As Integer

This allows GLB_TestVariable to be used in other modules. Note that using GLB as part of the variable name helps to identify it as a global variable. Although not required, such a naming approach will insure that you know when you are working with a global variable. Public variables will retain their values after a macro runs, just like a Static variable. However, they will be reset if the Visual Basic code is edited, an End statement is used to halt a macro, or the workbook is closed.

Please note that if a variable in a macro is declared with the same name as a global variable, references to this variable in that macro will refer to the local or macro declared variable instead of the global variable. Such a situation may cause errors that are difficult to debug.

 

[Elbpf]

Thank you. I really appreciate all you have helped me with!

 

[paul-aug]

Hello,
Because the transfer of variables from one module/user form causes me so many
problems I put the variable value on a worksheet. This has never failed me yet.

Example..
Sub Macro1() ' Whatever module you want
Dim a As Double
' whatever code generates "a" here, for example I generate "a" by (a = 10.86)
a = 10.86
Cells(1, 1).Value = a
'Code
'Code
End Sub

Rem the following code can be in a user form or any other module
Sub Macro2()
Dim b As Double
'Code code code
b = Cells(1, 1).value
'Code code code
End Sub

' Hope this is of use