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

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.



Cory wrote:

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

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" wrote:

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.



Cory wrote:

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

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 mo

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.



Cory wrote:

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" wrote:

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.



Cory wrote:

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


--

Dave Peterson

Yes, they do.

--
HTH

Bob

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

"Cory" wrote in message
...
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" wrote:

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.



Cory wrote:

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

Good idea. Thanks again for all your help.

"Dave Peterson" wrote:

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 mo

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.



Cory wrote:

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" wrote:

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.



Cory wrote:

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


--

Dave Peterson