Taras wrote:
What is your opinion on declaring the variables in a sub
that is executed when the spreadsheet is opened so that
they would be available to any macro following.
If you really mean *declaring* the variables, it won't work.
Variables declared within a procedure are available only within that
procedure.
Because module-level variables lose their values when the project is
recompiled (e.g. after you click the Reset button in the
VB editor or
the End button in a
VB error message, or execute an End statement etc),
I try to avoid relying on their values between one macro run and the
next. If I need to have such module-level variables I will use a
procedure to set them all, including a Boolean ValidVars:
Sub CheckVars()
If ValidVars Then Exit Sub
' set all module level variables
ValidVars = True
End Sub
and I call CheckVars from the start of each procedure that can be run
as the result of a user action.
Hope this helps
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup