Public Variables and Worksheets

[caffeine]

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

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:

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):

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?

[si_the_geek]

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:

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