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

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

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

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

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

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