Option Explicit to Declare Variables on Worksheet Code

[bisel]

Hello All,

When using Option Explicit at the top of a module, is the use restricted to public modules? Or is it supposed to work for modules that are specific a worksheet. For example, if I place this at the top of a Worksheet module ...

Option Explicit
Public shtprotect As Boolean
Public endprotect As Boolean

Are those variables supposed to then be declared for all subs in that module, even though those subs are Private Sub ... ?

I get variable not declared errors doing this.

 

[Fluff]

Option Explicit only works for the module it's in, so you really need it in every module.
Public Variables declared in a standard module are available to all modules in that workbook, but public variables declared in a sheet module are only directly available to that sheet.

 

[bisel]

Option Explicit only works for the module it's in, so you really need it in every module.
Public Variables declared in a standard module are available to all modules in that workbook, but public variables declared in a sheet module are only directly available to that sheet.

Thanks for reply. I only want the variable to be available in the sheet. Don't need it anywhere else. Do you see anything wrong with this at the top of the sheet module ...

Option Explicit
Public shtprotect As Boolean
Public endprotect As Boolean

In this usage, at the top of the sheet module, I get the Variable Not Declared error when I attempt to run the other subs on the sheet module. Those other subs are private subs as they are on the sheet and in a public module ...

Thanks,

Steve

 

[Fluff]

If you only want it available for that sheet I would replace Public with Dim, but I don't think it should make any difference.
However this is confusing, if not contradictory.

Those other subs are private subs as they are on the sheet and in a public module

Are the subs in a sheet module, or a standard module?

 

[bisel]

If you only want it available for that sheet I would replace Public with Dim, but I don't think it should make any difference.
However this is confusing, if not contradictory.

Are the subs in a sheet module, or a standard module?

I should be more clear on what I am trying to do ...

On the sheet module, I have a sub that runs on sheet activation, Private Sub Worksheet_Activate(). In that sub, I want to set a variable depending on if the sheet is in a protected or unprotected state. If protected, I set the variable called "shtprotect" to TRUE otherwise set it to FALSE.

In another sub on the same sheet module I have a sub called Private Static Sub Worksheet_SelectionChange(ByVal Target As Range). I would like this sub to take an action depending on if the variable, shtprotect is TRUE or FALSE.

I want the variable, shtprotect, to be available to all the subs on the same sheet module. But, I do not want to pass the variable value as one would do in a call routine. I just want to set the variable when the sheet activates and have it available for use.

I think I am probably going about this incorrectly and would appreciate any help. Perhaps I could set a range on the sheet equal to either TRUE or FALSE depending on the state of protection for the sheet. Then use the activate sub to set that range accordingly. Then test the range in subsequent subs. I think that would work, but doesn't sound very elegant.

Steve

 

[Fluff]

For that scenario, what you are doing is fine.
One word of warning, if that sheet is the active when you open the workbook then the variable will be false, which may cause problems with the selectionchange event.