declare & initialize a variable as global using vba

mike08

Board Regular
Joined
Oct 29, 2008
Messages
167
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
My magic 8-ball says:
VBA Help said:
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.
 
Upvote 0
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")
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
if i initialized in the function ExampleSub(), how i can access the values of this array variable to another function?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top