Set module-wide objects?

[Ed]

Is it possible to set a worksheet object the will be available to every
macro in a single module, versus having to re-set the same object in each
macro? I can declare the variable across the entire module, but the objects
seem to vanish ate each individual End Sub.

Ed

 

[Tom Ogilvy]

The first sub you run must set the variable to the worksheet. The code must
be in a general module, not in a worksheet or the thisworkbook module. At
the top of the module:

Public mySheet as Worksheet

Sub Macro1()
set mySheet = Worksheets(1)
End Sub

Sub Macro2()
if mysheet is nothing then macro1
msgbox mysheet.Name
End Sub

Sub Macro3()
if mysheet is nothing then macro1
msgbox mysheet.Range("A1").Value
End Sub

 

[Ed]

Tom: Would I be able to fire this from a Workbook_Open macro in
ThisWorkbook? Or would that destroy the object when it left that module?

Ed

 

[Tom Ogilvy]

You should declare the public variable in a general module. You can set it
in the Workbook_Open Event.

 

[Ed]

Thanks, Tom. I appreciate the boost.

Ed

You should declare the public variable in a general module. You can set it
in the Workbook_Open Event.