Page 1 of 2 12 LastLast
Results 1 to 10 of 11

declare & initialize a variable as global using vba

This is a discussion on declare & initialize a variable as global using vba within the Excel Questions forums, part of the Question Forums category; Hi experts, Would u kindly let me know , how to declare&initialize a variable as Global in vba ? I ...

  1. #1
    Board Regular
    Join Date
    Oct 2008
    Posts
    167

    Question declare & initialize a variable as global using vba

    Hi experts,

    Would u kindly let me know , how to declare&initialize a variable as Global in vba?
    I have a variable ,
    listGroup=Array("aaa","bbb","ccc")
    now i am using this variable for 3 different functions. so what i am doing is wrote the same code to 3 functions, so how i can declare&initialize this variable as global and access to all functions.

    Thanks for any help

  2. #2
    Board Regular
    Join Date
    Oct 2006
    Location
    日本
    Posts
    2,457

    Default Re: declare & initialize a variable as global using vba

    My magic 8-ball says:
    Quote Originally Posted by VBA Help
    To enter global declarations, go to the Declarations section of a module and use the Public statement for constants and variables. You can also use the Dim, Static, and Private keywords to make declarations.
    Download an HTML Maker to show your data, and please wrap all code in [code][/code] tags so we can read it. My mind-reading add-in is on my other computer.

  3. #3
    Board Regular
    Join Date
    Oct 2008
    Posts
    167

    Default Re: declare & initialize a variable as global using vba

    Thanks for ur response
    i used ,
    dim listGroup as variant, in General declaration section.
    But how to initialize this variable globally like ,
    listGlistGroup=Array("aaa","bbb","ccc")

  4. #4
    Board Regular
    Join Date
    Oct 2008
    Posts
    167

    Default Re: declare & initialize a variable as global using vba

    Hi Guys,

    I hope somebody can help me this one,

    How to initialize an Array variable globally?, ie can access all functions this vble.
    eg: the vble is listGroup=Array(("aaa","bbb","ccc")
    Any help would be greatly appreciated

  5. #5
    Board Regular
    Join Date
    Oct 2006
    Location
    日本
    Posts
    2,457

    Default Re: declare & initialize a variable as global using vba

    In the 'ThisWorkbook' module set your worksheet OnOpen event to Dim the array however you want it dimmed after declaring the variable name/type globally.
    Download an HTML Maker to show your data, and please wrap all code in [code][/code] tags so we can read it. My mind-reading add-in is on my other computer.

  6. #6
    Board Regular
    Join Date
    Oct 2008
    Posts
    167

    Default Re: declare & initialize a variable as global using vba

    still confusing about the initialization of Array vble. can u clear this one?
    if i initialized Array using Dim in the workbook open event, how my code area will identify this variable

  7. #7
    Board Regular
    Join Date
    Apr 2008
    Posts
    404

    Default Re: declare & initialize a variable as global using vba

    Its quite simple. In the declarations section of your module (before any functions or subs)

    Code:
    Public ListGroup()
    Then to initialize it in a sub

    Code:
    Sub ExampleSub()
    
    ListGroup = Array("A", "B", "C")
    
    End Sub
    Use another sub to show that the data is preserved

    Code:
    sub ReturnFirst()
    
    msgbox ListGroup(0)
    
    end Sub

  8. #8
    Board Regular
    Join Date
    Oct 2008
    Posts
    167

    Default Re: declare & initialize a variable as global using vba

    if i initialized in the function ExampleSub(), how i can access the values of this array variable to another function?

  9. #9
    Board Regular
    Join Date
    Apr 2008
    Posts
    404

    Default Re: declare & initialize a variable as global using vba

    It will always be available (until any errors occur or VBA is recompiled when changes are made) to any sub or function in any module. Just access it as if you had just defined it in that sub.

    The key is to run the initializing sub routine at some convenient time (via a Book opening as someone suggested for example).

    Its hard to be more specific without a more specific requirement.

  10. #10
    Board Regular
    Join Date
    Oct 2008
    Location
    India
    Posts
    77

    Default Re: declare & initialize a variable as global using vba

    Quote Originally Posted by mike08 View Post
    Hi experts,

    Would u kindly let me know , how to declare&initialize a variable as Global in vba?
    I have a variable ,
    listGroup=Array("aaa","bbb","ccc")
    now i am using this variable for 3 different functions. so what i am doing is wrote the same code to 3 functions, so how i can declare&initialize this variable as global and access to all functions.

    Thanks for any help
    You can use the declaration
    public listGroup() in any module
    and then u can initialise it during the Thisworkbook's "workbook_open" event using this

    Private Sub Workbook_Open()

    listGroup=Array("aaa","bbb","ccc")

    End Sub

    After writing the above code save and close the workbook and then reopen and you will be able to use this array
    Nitin Thanks in advance

    When the going gets tough ,the tough gets going.

Page 1 of 2 12 LastLast

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