views:

938

answers:

3

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

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.

Treb
Presumably while the definitions can be made at module level, the values will need to be assigned in a procedure, e.g. in Workbook_Open()? Since a constant can't contain objects...
Alistair Knock
+2  A: 

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
Gary McGill
Thinking about this... I don't know if I would recommend it. The problem with a property is that it looks like an object and if you're in a loop that updates, say, 15000 cells one by one, this is a lot of additional indirection to go through on each cycle. I think it would be better to use an Worksheet reference directly but YMMV. I still think it's better to go Dim sht as Worksheet / Set sht = ModelWorksheet() / sht.Cells(1,1)... and put the sheet grab into a ModelWorksheet function. I know it doesn't reduce the number of lines involved but going global is usually ill-advised.
Joel Goodwin
@goodgai: agreed wholeheartedly; that actually is why I used a With statement, to avoid referencing the property twice, but I should have been more explicit. And you're right about even this method being not much help in a loop (unless the With ... End With wraps the entire loop).
Gary McGill
Yes I did notice the Withs in your answer and saw what you were doing but I ran out of characters in my comment =) I worked out what I was trying to say now, concisely: defensive programming - by avoiding the property you're less likely to walk into a performance ambush by accident.
Joel Goodwin
+3  A: 

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