Results 1 to 6 of 6

Global Variable?

This is a discussion on Global Variable? within the Excel Questions forums, part of the Question Forums category; Hello I wonder is there a way to create a variable in a Workbook, and then access this variable in ...

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    11

    Default Global Variable?

    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.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,948

    Default Re: Global Variable?

    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.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    New Member
    Join Date
    May 2002
    Posts
    11

    Default

    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.

  4. #4
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259

    Default Re: Global Variable?

    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.

    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

  5. #5
    New Member
    Join Date
    Nov 2008
    Posts
    9

    Default Re: Global Variable?

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

  6. #6
    New Member
    Join Date
    Oct 2011
    Posts
    7

    Default Re: Global Variable?

    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com