How to correctly Public and Set a Worksheet?

Public sp As Worksheet  //  on top of ThisWorkbook module
...
Sub one()
Set sp = Sheets("blueSky")  // marked
MsgBox sp.Name
...
Sub two()
Set sp = Sheets("blueSky")  //  marked
MsgBox sp.Range("A1").Value

Marked line is allways the same. So, is it possible to write the marked line only once - and where ? I tried on Workbook.Open event - without result I want in each Sub write only the third line - which is allways different.

Answers


If you put the followin in a module (not ThisWorkbook) you get a global Worksheet variable:

Option Explicit

Global sp As Worksheet

You can then adress it in any of the following ways in ThisWorkbook (subs one() and two() will also work in a module):

Option Explicit

Public Sub one()
    Set sp = Sheets("Sheet1")  '// marked
    MsgBox sp.Name
End Sub

Sub two()
    Set sp = Sheets("Sheet2") ' //  marked
    MsgBox sp.Range("A1").Value
End Sub

Private Sub Workbook_Open()
    Set sp = Sheets("Sheet3")
End Sub

Need Your Help

Thinking sphinx fuzzy search?

sphinx thinking-sphinx

I am implementing sphinx search in my rails application.