Can a worksheet object be declared globally in Excel VBA?

Question

I'm refactoring a number of modules in an Excel 2003 workbook and the same set of worksheets are declared in each procedure in each module; I'd like to just declare them once globally. I can set the worksheet name as a literal, e.g.:

Public Const xlwkGSModel = "gs_model" As String

And then in the procedure use:

...ActiveWorkbook.Worksheets(xlwkGSModel).Cells(1,1)

But is there a way to declare the worksheet object so that the code in the procedure could be:

...xlwkGSModel.Cells(1,1)

Answer 1

Edit: The comment by Alistair Knock is correct, I should have read the question thoroughly - of course my answer is not valid for objects, only for types like string or integer. For objects you need a function or sub that creates an instance.

---

Yes, you can, I recently did it. If you define your definitions as Public you can use them directly in your other modules (within the same workbook, of course).

Maybe the best approach is to have a seperate module Globals and put them there.

Answer 2

You could, but do you really want more global variables? Why not create (within a standard module) a public property ModelWorksheet, like this:

Public Property Get ModelWorksheet As Worksheet
    Const ModelWorksheetName As String = "gs_model"
    Set ModelWorksheet = ActiveWorkbook.Worksheets(ModelWorksheetName)
End Property

...then your code can do:

With ModelWorksheet
    .Cells(1,1).Value = "foo"
    .Font.Bold = True
End With

Note also that you can refer to any of the worksheets in the current workbook directly, so if there's a sheet called Sheet1 you can do:

With Sheet1
    .Cells(1,1).Value = "foo"
    .Font.Bold = True
End With

Answer 3

'1. Insert a module

'2. Declare worksheet public variable in the module as follows

Public xlwkGSModel As Worksheet

'3. Instantiate this public variable in the application load event

Sub Workbook_Open()

Set xlwkGSModel = ActiveWorkbook.Worksheets("gs_model")

End Sub

'Now you can refer the gs_model worksheet with the xlwkGSModel variable

'For example

dim x as string

x = xlwkGSModel.Cells(1,1)