Global Array Constant

[Guest]

Using Excel 2003.

I have an array of button captions that I am using to loop through some of
the buttons on a pop-up control on a custom toolbar. The array is required in
several different procedures, so I would like a solution to avoid having to
declare it in each procedure. Normally, I would just loop through all of the
controls contained in the pop-up, but there are three buttons that need to be
handled different from the rest, so that solution doesn't work in this case.
My current code that I am using in each procedure is:

Dim ButtonNames() As Variant

ButtonNames = Array("Next Level", "All Levels", "Bottom Level", "Use
Siblings", _
"Same Level", "Same Generation", "Calc Level", "Include Selected", _
"Within Selected Groups", "Remove Unselected Groups")

My first thought was to try and declare it as a global constant as follows,
but that didn't work out so well:

Public Const ButtonNames as Variant = Array("Next Level", "All Levels", _
"Bottom Level", "Use Siblings", "Same Level", "Same Generation", _
"Calc Level", "Include Selected", "Within Selected Groups", _
"Remove Unselected Groups")

Any helpfully solutions that you could provide would be greatly appreciated.

-Cory

 

[Dave Peterson]

Public ButtonNames as variant

Sub Auto_Open()

ButtonNames = Array("Next Level", "All Levels", "Bottom Level", _
"Use Siblings", _
"Same Level", "Same Generation", "Calc Level", "Include Selected", _
"Within Selected Groups", "Remove Unselected Groups")
End sub

As soon as the workbook is opened, the assignment will be done.

 

[Guest]

Thanks for your help, Dave. Since that works, does that mean that global
variables are persistent and retain their values between procedure calls?

-Cory

 

[Dave Peterson]

They're persistent until you do something bad--hit the End key (while
debugging)--or have a line like:

End
(not "end sub", "end function", "end if"--just plain old End.)

You may want to create a dedicated routine that initializes these global
variables.

Then add one more:

Public VariablesAreInitialized as boolean

Then later you can use:

if variablesareinitialized then
'keep going
else
call dedicatedroutinetoinitializevariables
end if

sub dedicatedroutinetoinitializevariables()
buttonnames = array(...)
...all you need
variablesareinitialized = true
end sub

just in case something unexpected goes wrong.

 

[Bob Phillips]

Yes, they do.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Good idea. Thanks again for all your help.

They're persistent until you do something bad--hit the End key (while
debugging)--or have a line like:

End
(not "end sub", "end function", "end if"--just plain old End.)

You may want to create a dedicated routine that initializes these global
variables.

Then add one more:

Public VariablesAreInitialized as boolean

Then later you can use:

if variablesareinitialized then
'keep going
else
call dedicatedroutinetoinitializevariables
end if

sub dedicatedroutinetoinitializevariables()
buttonnames = array(...)
...all you need
variablesareinitialized = true
end sub

just in case something unexpected goes wrong.