Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public Declerations
I have a VBA Module named MasterFormulas. In it will be several
sub-routines. The first one starts like this: Option Explicit Public Sub MRR() 'Begin General Declerations Dim WSDSD As Worksheet Set WSDSD = Worksheets("Data SD") Dim WSRep As Worksheet Set WSRep = Worksheets("Report") Dim WSCri As Worksheet In every sub-routine in the module MasterFormulas, I want Excel to remember these declerations. How can I avoid having to re-declare these in every sub-routine? -- Thanks Shawn |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public Declerations
Hi Shawn,
Declare the variables at module level. You can initialise them and then use them throughout the module, e.g.: Option Explicit Dim WSDSD As Worksheet Dim WSRep As Worksheet Dim WSCri As Worksheet Sub Initialise() Set WSDSD = Worksheets(1) '("Data SD") Set WSRep = Worksheets(2) '("Report") Set WSCri = Worksheets(3) '("YourSheet") End Sub Sub Tester() Debug.Print WSDSD.Name, WSRep.Name, WSCri.Name, End Sub --- Regards, Norman "Shawn" wrote in message ... I have a VBA Module named MasterFormulas. In it will be several sub-routines. The first one starts like this: Option Explicit Public Sub MRR() 'Begin General Declerations Dim WSDSD As Worksheet Set WSDSD = Worksheets("Data SD") Dim WSRep As Worksheet Set WSRep = Worksheets("Report") Dim WSCri As Worksheet In every sub-routine in the module MasterFormulas, I want Excel to remember these declerations. How can I avoid having to re-declare these in every sub-routine? -- Thanks Shawn |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public Declerations
So...I can put the Dim statements at the module level but have to put the:
Set WSDSD = Worksheets(1) '("Data SD") Set WSRep = Worksheets(2) '("Report") Set WSCri = Worksheets(3) '("YourSheet") statements at the begining of each sub routine? -- Thanks Shawn "Norman Jones" wrote: Hi Shawn, Declare the variables at module level. You can initialise them and then use them throughout the module, e.g.: Option Explicit Dim WSDSD As Worksheet Dim WSRep As Worksheet Dim WSCri As Worksheet Sub Initialise() Set WSDSD = Worksheets(1) '("Data SD") Set WSRep = Worksheets(2) '("Report") Set WSCri = Worksheets(3) '("YourSheet") End Sub Sub Tester() Debug.Print WSDSD.Name, WSRep.Name, WSCri.Name, End Sub --- Regards, Norman "Shawn" wrote in message ... I have a VBA Module named MasterFormulas. In it will be several sub-routines. The first one starts like this: Option Explicit Public Sub MRR() 'Begin General Declerations Dim WSDSD As Worksheet Set WSDSD = Worksheets("Data SD") Dim WSRep As Worksheet Set WSRep = Worksheets("Report") Dim WSCri As Worksheet In every sub-routine in the module MasterFormulas, I want Excel to remember these declerations. How can I avoid having to re-declare these in every sub-routine? -- Thanks Shawn |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public Declerations
Hi Shawn,
So...I can put the Dim statements at the module level Yes. but have to put the: Set WSDSD = Worksheets(1) '("Data SD") Set WSRep = Worksheets(2) '("Report") Set WSCri = Worksheets(3) '("YourSheet") statements at the begining of each sub routine? No. You can initialise them once (as in my example Sub Initialise) and then use them anywhere in the module - as in the demo Tester sub that I showed. --- Regards, Norman "Shawn" wrote in message ... So...I can put the Dim statements at the module level but have to put the: Set WSDSD = Worksheets(1) '("Data SD") Set WSRep = Worksheets(2) '("Report") Set WSCri = Worksheets(3) '("YourSheet") statements at the begining of each sub routine? -- Thanks Shawn "Norman Jones" wrote: Hi Shawn, Declare the variables at module level. You can initialise them and then use them throughout the module, e.g.: Option Explicit Dim WSDSD As Worksheet Dim WSRep As Worksheet Dim WSCri As Worksheet Sub Initialise() Set WSDSD = Worksheets(1) '("Data SD") Set WSRep = Worksheets(2) '("Report") Set WSCri = Worksheets(3) '("YourSheet") End Sub Sub Tester() Debug.Print WSDSD.Name, WSRep.Name, WSCri.Name, End Sub --- Regards, Norman "Shawn" wrote in message ... I have a VBA Module named MasterFormulas. In it will be several sub-routines. The first one starts like this: Option Explicit Public Sub MRR() 'Begin General Declerations Dim WSDSD As Worksheet Set WSDSD = Worksheets("Data SD") Dim WSRep As Worksheet Set WSRep = Worksheets("Report") Dim WSCri As Worksheet In every sub-routine in the module MasterFormulas, I want Excel to remember these declerations. How can I avoid having to re-declare these in every sub-routine? -- Thanks Shawn |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public Declerations
Shawn,
From Help: Variables declared with Dim at the module level are available to all procedures within the module. At the procedure level, variables are available only within the procedure. Variables declared using the Public statement are available to all procedures in all modules in all applications unless Option Private Module is in effect; in which case, the variables are public only within the project in which they reside. Since you are talking about within a module - you probably want to Dim the variable at the top of the module. -- steveB Remove "AYN" from email to respond "Shawn" wrote in message ... I have a VBA Module named MasterFormulas. In it will be several sub-routines. The first one starts like this: Option Explicit Public Sub MRR() 'Begin General Declerations Dim WSDSD As Worksheet Set WSDSD = Worksheets("Data SD") Dim WSRep As Worksheet Set WSRep = Worksheets("Report") Dim WSCri As Worksheet In every sub-routine in the module MasterFormulas, I want Excel to remember these declerations. How can I avoid having to re-declare these in every sub-routine? -- Thanks Shawn |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public Declerations
That worked. Thanks.
-- Thanks Shawn "Norman Jones" wrote: Hi Shawn, So...I can put the Dim statements at the module level Yes. but have to put the: Set WSDSD = Worksheets(1) '("Data SD") Set WSRep = Worksheets(2) '("Report") Set WSCri = Worksheets(3) '("YourSheet") statements at the begining of each sub routine? No. You can initialise them once (as in my example Sub Initialise) and then use them anywhere in the module - as in the demo Tester sub that I showed. --- Regards, Norman "Shawn" wrote in message ... So...I can put the Dim statements at the module level but have to put the: Set WSDSD = Worksheets(1) '("Data SD") Set WSRep = Worksheets(2) '("Report") Set WSCri = Worksheets(3) '("YourSheet") statements at the begining of each sub routine? -- Thanks Shawn "Norman Jones" wrote: Hi Shawn, Declare the variables at module level. You can initialise them and then use them throughout the module, e.g.: Option Explicit Dim WSDSD As Worksheet Dim WSRep As Worksheet Dim WSCri As Worksheet Sub Initialise() Set WSDSD = Worksheets(1) '("Data SD") Set WSRep = Worksheets(2) '("Report") Set WSCri = Worksheets(3) '("YourSheet") End Sub Sub Tester() Debug.Print WSDSD.Name, WSRep.Name, WSCri.Name, End Sub --- Regards, Norman "Shawn" wrote in message ... I have a VBA Module named MasterFormulas. In it will be several sub-routines. The first one starts like this: Option Explicit Public Sub MRR() 'Begin General Declerations Dim WSDSD As Worksheet Set WSDSD = Worksheets("Data SD") Dim WSRep As Worksheet Set WSRep = Worksheets("Report") Dim WSCri As Worksheet In every sub-routine in the module MasterFormulas, I want Excel to remember these declerations. How can I avoid having to re-declare these in every sub-routine? -- Thanks Shawn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Public Password | Excel Discussion (Misc queries) | |||
Dim vs. Public | Excel Programming | |||
Public declaration | Excel Programming | |||
public sub | Excel Programming | |||
Public Sub Help | Excel Programming |