Public Variables and Worksheets

It's very convenient to set up Worksheet aliases before any code runs. So I made some public variables:

Code:

---

Public outsht As Worksheet
Public loansht As Worksheet
Public batsht As Worksheet

---

and in the Worksheet_Open event (which I define in "ThisWorkbook"), I set them to the respective worksheets:

Code:

---

Private Sub Workbook_Open()
    Set outsht = Worksheets("pool-level analysis")
    Set loansht = Worksheets("loan-level analysis")
    Set batsht = Worksheets("batch files")

    Call createToolBar
End Sub

---

However, when I run this function (which is called from a toolbar that I create):

Code:

---

Sub runApproximator()
    ' Sheets("pool-level analysis").Range("$AW$4") = "Approximator" ' Run Type
    outsht.Range("$AW$4") = "Approximator" ' Run Type
End Sub

---

I get an error "variable not found" for outsht.

Why is this? Are complex objects like Worksheets not allowed to be public?

They can be public (otherwise you would get an error when declaring them), the problem is how you are using them from outside the workbook.

To use a public variable from a workbook (or form, or other object), you need to also specify that object, eg:

Code:

---

WorkBooks("my workbook").outsht.Range("$AW$4")
Form2.outsht.Range("$AW$4")

---