Not just declare variable but set names etc for entire workbook????

[cizzett]

Ok, So I have a lot going on in this workbook and just trying to clean up all the script etc in this workbook and wanted to know if it is possible to declare everything below ALONG WITH Setting everything so when I get to the codes I dont have to "Set" anything just use the code like "Ls.Activate" or whatever

Here is everything I want made available across all modules

Public Wb As WorkbookPublic df As Workbook   ' Defined in Script
Public Pd As Workbook   ' Defined in Script
Public mf As Workbook   ' Defined in Script
Public Wbk As Workbook  ' Defined in Script


Public Ws As Worksheet
Public Ls As Worksheet
Public Fl As Worksheet
Public Ps As Worksheet
Public Es As Worksheet
Public SOS As Worksheet
Public Piv As Worksheet
Public Inst As Worksheet
Public ds As Worksheet   ' Defined in Script
Public Pds As Worksheet  ' Defined in Script
Public ms As Worksheet   ' Defined in Script
Public Nws As Worksheet  'Defined in Script
Public RDPiv As Worksheet ' Defined in Script


Public TT As ListObject
Public PDPT As ListObject
Public PDT As ListObject 'Defined in Script
Public SOST As ListObject  ' Defined in Script
Public SOSDT As ListObject
Public LT As ListObject


Public IPCB As CheckBox
Public SOSCB As CheckBox
Public LPCB As CheckBox
Public RFCB As CheckBox
Public ASCB As CheckBox
Public SAPCB As CheckBox


Public SOSRunCB As CheckBox
Public JDARunCB As CheckBox
Public PDRunCB As CheckBox
Public PLWSRunCB As CheckBox
Public ExPRunCB As CheckBox
Public ASRunCB As CheckBox
Public SAPRunCB As CheckBox


Set Wb = ThisWorkbook
Set Ws = Wb.Sheets("Today")
Set Ls = Wb.Sheets("L.W.S.")
Set Fl = Wb.Sheets("File Locs")
Set Ps = Wb.Sheets("Prev Day")
Set Es = Wb.Sheets("E-mail")
Set SOS = Wb.Sheets("SOS")
Set Piv = Wb.Sheets("Pivot")
Set Inst = Wb.Sheets("Instruction Sheet")


Set PDT = Ps.ListObjects("PD_Table")
Set TT = Ws.ListObjects("TodayTable")
Set SOSDT = SOS.ListObjects("SOSDT")
Set LT = Ls.ListObjects("LotTable")


Set IPCB = Fl.Shapes("IPCB").OLEFormat.Object
Set SOSCB = Fl.Shapes("SOSCB").OLEFormat.Object
Set LPCB = Ws.Shapes("LPCB").OLEFormat.Object
Set RFCB = Fl.Shapes("RFCB").OLEFormat.Object
Set ASCB = Fl.Shapes("ASCB").OLEFormat.Object
Set SAPCB = Fl.Shapes("SAPCB").OLEFormat.Object


Set SOSRunCB = Ws.Shapes("SOSRunCB").OLEFormat.Object
Set JDARunCB = Ws.Shapes("JDARunCB").OLEFormat.Object
Set PDRunCB = Ws.Shapes("PDRunCB").OLEFormat.Object
Set PLWSRunCB = Ws.Shapes("PLWSRunCB").OLEFormat.Object
Set ExPRunCB = Ws.Shapes("ExPRunCB").OLEFormat.Object
Set ASRunCB = Ws.Shapes("ASRunCB").OLEFormat.Object
Set SAPRunCB = Ws.Shapes("SAPRunCB").OLEFormat.Object

 

[Fluff]

You could put all the Set statements into the workbook open event

 

[cizzett]

Well that's what I thought as well but I'm doing something wrong because I have to have the set statements in a sub then when the sub ends and moves on to the next sub it no longer retains the sets from the first sub

 

[Fluff]

Where are all those variables declared?

 

[DanteAmor]

In the module you have something like this:

Public Wb As Workbook
Public Ws As Worksheet


Sub tst()
    Set Wb = ThisWorkbook
    Set Ws = Wb.Sheets("Today")
End Sub

If you run the "tst" macro, then you can use the objects Wb, Ws, etc.

 

[cizzett]

Right now they are repeated in each and every sub across 3 modules

I have to declare it for every sub and I was just hoping to clean up the subs script so its easier to read through etc.

Might be a pointless road to travel but figured I'd give it a go.

 

[DanteAmor]

You only declare it in a module, you execute the macro and then the variables remain in memory and you can use them in other modules without having to declare again.
If you declare them in another module then the first declaration is lost.

 

[Fluff]

Further to Dante's comments.
The variables must be declared at the very top of the module, before any code.

 

[cizzett]

Thanks Guys, The Issue was resolved when I read Dante's reply and figured it is because I had my "Bundles" in one module and the bulk of the script in another module.

When I would call the bundle up it would run the script in the other module and "Forget" the declarations.

Thanks Guys

 

[Fluff]

Glad it's sorted & thanks for the feedback.
For the record, as long as the variables are declared in a regular module, before any code, then it shouldn't make any difference where the actual code is located