Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set module-wide objects?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set module-wide objects?
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 -- Regards, Tom Ogilvy "Ed" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set module-wide objects?
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" wrote in message ... 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 -- Regards, Tom Ogilvy "Ed" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set module-wide objects?
You should declare the public variable in a general module. You can set it
in the Workbook_Open Event. -- Regards, Tom Ogilvy "Ed" wrote in message ... 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" wrote in message ... 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 -- Regards, Tom Ogilvy "Ed" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set module-wide objects?
Thanks, Tom. I appreciate the boost.
Ed "Tom Ogilvy" wrote in message ... You should declare the public variable in a general module. You can set it in the Workbook_Open Event. -- Regards, Tom Ogilvy "Ed" wrote in message ... 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" wrote in message ... 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 -- Regards, Tom Ogilvy "Ed" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CLASS MODULE & SIMPLE MODULE | Excel Discussion (Misc queries) | |||
code in module A to not execute a Worksheet_SelectionChange sub of another module | Excel Discussion (Misc queries) | |||
Dynamically Assign Objects to Form Objects. | Excel Programming | |||
Unable to remove Sheet objects in the Microsoft Excel Objects | Excel Programming | |||
Variable from a sheet module in a class module in XL XP | Excel Programming |