Assign worksheet to Global variable. Possible? or Worth it?

[POE333]

Hello all,
I'm tying to figure out how to declare a variable of a worksheet as global so that it can be used from any module.
My best try (failing) looks like this...

Option Explicit

Public wsI2 As String
wsI2 = "Input"
wsI2 = Worksheets(wsI2)

Public wsI As Worksheet
wsI = Sheets("Input")

Public wsO As Worksheet
wsO = Sheets("Output")

Public wsR As Worksheet
wsR = Sheets("Results")

Public HDT As Worksheet
wsH = Sheets("HDT")

Public HDT As Worksheet
HDT = Sheets("HDT")

Public GLL As Worksheet
GLL = Sheets("GLL")

Public GGA As Worksheet
GGA = Sheets("GGA")

Public HDT As Worksheet
wsH = Sheets("HDT")

Sub NavPage1()
wsI2.Select
End Sub



I have no idea how to make this work. I've looked everywhere I can think of.

[Aflatoon]

Assuming you only need it from code in the same workbook, just use its codename - e.g. Sheet1

[POE333]

Forgive that I am UberNewb but... codename? I know the sheet# remains the same even if you change the name of the tab. Is this what you are referring to?
Yes only the same workbook. I am looking to do this programmatically via variables. Part is self education, and part application based.

[Aflatoon]

If you look at the worksheet in the Project Explorer in the VB Editor, you will see it has two names. The first one is the code name, and the one in brackets is what appears on the sheet tab. You can simply use the codename as an object in your code (it's like a worksheet variable) - for example:


msgbox sheet1.name

[POE333]

So, I have found this works.. Oddly enough, part of the answer was derived from a completely different question.

What I would like to know is how I can use these as global variables across all modules.




Option Explicit

Sub SelectPages()
Dim WB1 As Workbook
Dim SH1 As Worksheet
Dim SH2 As Worksheet
Set WB1 = ThisWorkbook
Set SH1 = WB1.Worksheets("Sheet1")
Set SH2 = WB1.Worksheets("Sheet2")
SH1.Select
SH2.Select
End Sub

[Aflatoon]

You'd need to move these lines:


Dim WB1 As WorkbookDim SH1 As Worksheet
Dim SH2 As Worksheet

to the top of a normal module, outside all procedures, and make the declarations public:


Public WB1 As Workbook
Public SH1 As Worksheet
Public SH2 As Worksheet

but you'd still need to make sure to run the SelectPages code before any other code. You'd be better off just using ThisWorkbook and the sheet code names directly in your code. They work like public variables and you don't need to initialise them.

[POE333]

AHHHH.. got it! Thanks. I am just having trouble wrapping my head around using predefined variables with other modules. Do I need to reference the variables in another module? as below?


Option Explicit

Dim WB1 As Workbook
Dim SH1 As Worksheet
Dim SH2 As Worksheet

Sub SelectPages()
Set WB1 = ThisWorkbook
Set SH1 = WB1.Worksheets("Sheet1")
Set SH2 = WB1.Worksheets("Sheet2")
SH1.Select
SH2.Select
End Sub

Sub OtherPages(WB1, SH1, SH2)
WB1.DoSomething
SH1.JumpRope
SH2.EctEctEct
End Sub


Or do I even need the "(WB1, SH1, SH2)" on the OtherPages?

[Aussiebear]

Sorry POE333 but you have missed the point made by Aflatoon.



Option Explicit

Public WB1 As Workbook
Public SH1 as WorkSheet
Public WH2 as Worksheet


Technically you could also use the word "Global" in place of "Public", but that is simply a matter of individual preference. You should "dim" your variables within the procedure not within the Global statements.



Sub SelectPages()
Set WB1 = ThisWorkbook
Set SH1 = WB1.Worksheets("Sheet1")
Set SH2 = WB1.Worksheets("Sheet2")
SH1.Select
SH2.Select
End Sub


Rather than SH1.select or SH2.select use



With SH1
'do something
End With



do I even need the "(WB1, SH1, SH2)" on the OtherPages?

No, its un-necessary to do so.

[Aflatoon]

Just:


Sub OtherPages()
WB1.DoSomething
SH1.JumpRope
SH2.EctEctEct
End Sub

As I said though, all of this is unnecessary.

[Paul_Hossler]

1. You just need to understand 'Scope'

https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/understanding-scope-and-visibility

2. Assigning to an object variable (like Worksheet or Workbook) you need to 'Set' it

3. I like to use the CodeName for worksheets in VBA for WS that the user might rename since that is not easily changed by the user

31802