Option Explicit to Declare Variables on Worksheet Code

bisel

Board Regular
Joined
Jan 4, 2010
Messages
152
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 ...

VBA Code:
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.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,960
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 4, 2010
Messages
152
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 ...

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,960
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 4, 2010
Messages
152
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,960
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,743
Messages
5,543,950
Members
410,586
Latest member
acadavid86
Top