Declaring Variables

[chris brunt]

I have a project with a couple of userforms and other modules. One of the
userforms applies a filter to a data list in Excel. Under certain criteria I
wish to remove the filter (ActiveSheet.ShowAllData) and to avoid this code
being executed when the fiter has not been applied (which produces an
error), I want to declare an integer variable which will be given one value
when the filter is applied and another value when removed. The value of this
integer would then determine whether to execute the ShowAllData code. My
questions are: a) How and where do I declare this integer so that it is
available in all procedures applicable to the userform & b) what other
(easier?) way could this check be accomplished.

Any help much appreciated.
Regards
Chris Brunt

 

[Tom Ogilvy]

Just trap the error

On Error Resume Next
ActiveSheet.ShowAllData
On Error goto 0

or check if the filter is applied

if ActiveSheet.AutofilterMode then _
ActiveSheet.ShowAllData


Regards,
Tom Ogilvy

 

[Bob Kilmer]

It is usually best to use an existing property or test the state of the
specific object in question when possible, as Tom has pointed out in his
response. To answer your first question, in cases where you need or want to
implement a "global" variable, declare such variable Public at the top of a
(standard) Module. It is in your own best interest to limit the use of
global variables and to limit the scope of variables to the minimum
necessary generally. A Boolean data type is the logical choice where the
variable will represent one of two states exclusively.

Example:
(at the top of a Module, not ClassModule, UserForm, etc., above any
procedure.)

Public gIsOn As Boolean