CODEWITHSUNDEEP
vbaexcel
Alistair Knock
Alistair Knock

Reputation: 1836

Can a worksheet object be declared globally in Excel VBA?

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)

Upvotes: 8

Views: 88514

Answers (6)

felvhage
felvhage

Reputation: 1199

Your Worksheets are "Microsoft Excel Objects" defined in your VBA-Project-Explorer:

Worksheet Properties editing

In The Screen above i have named my Worksheet DataSheet, so now i can access it directly from within the code:

Set SomeRange = DataSheet.Range("A3:B6")

By Default your Worksheets will be named "Sheet1", "Sheet2", aso... depending on your language.

Upvotes: 2

orwell
orwell

Reputation:

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

Upvotes: 14

shijie
shijie

Reputation: 21

Or if you do not want to use the Excel Event, you can also declare your worksheet publicly, then create a sub to set value for it. Call that sub at the beginning of each sub to initialize the value. (I did this from orwell's answer.)

Public WSRawData As Worksheet

Public Sub RawDataWSInit()

Set WSRawData = Worksheets(RawData)

End Sub

Sub Main()

Call RawDataWSInit

End Sub

Upvotes: 2

Wysmaster
Wysmaster

Reputation: 51

It's a long time ago, but better late than never ;-) I don't know if that works in Excel 2003 (tested it with 2007):

You don't even need to declare worksheets in the code, because they are already declared. I'm working with a German version of Excel and I can access a worksheet by typing "Tabelle1.Cells(...) ...". I assume in the English version it is something like "Table1" or "Sheet1".

You can also change these names. In the Visual Basic Editor have a look at the Microsoft Excel Objects of your VBA-Project. (They are right above your Modules and UserForms in the VBA-Project view). There are the Worksheet-Objects of your workbook. Select a sheet and activate the Properties Toolwindow. There you can edit the name of the sheet and access it by that name in your code.

Upvotes: 5

Treb
Treb

Reputation: 20289

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.

Upvotes: 0

Gary McGill
Gary McGill

Reputation: 27536

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

Upvotes: 5

Related Questions