setting object variable

[ndjxw17]

i am looking for some advice on object variables. I have a module with
two sub routines in it. i have the following object variables at
module level

dim codes as worksheets

i want to use this variable in both subs, and the value(worksheet) will
be the same, so do i have to set the variable in both subs,
set codes = worksheets("sheet1)
this is what i am doing at the moment but i am not sure if this is
correct or good programming.

regards
chris

 

[Chip Pearson]

I guess the first question is why you want an object variable set
to Worksheets since you already have the built-in Worksheets
objects. That said, you can write code like

Dim Codes As Sheets
Set Codes = ThisWorkbook.Worksheets
Debug.Print Codes("Sheet1").Name


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

For referencing sheets you can do that directly by renaming the Code Name for
the sheet. In the properties window of the VBE (with Sheet1 selected) you
will see the first listed property is (Name). Change this from Sheet1 to
shtMySheet. In your code you can now reference the sheet directly.

Sheets("Sheet1").Select 'Note intelisense does not work here with "."
shtMysheet.Select 'Note intelisense does work here with "."

will do the exact same thing. The difference is that if the user changes the
tab name from Sheet1 to something else your code will crash. Using shtMySheet
your code will be fine because the user can not change the code name (unless
the go to the VBE).

In your example you have declared codes as worksheets when I think you meant
worksheet. You can pass a worksheet as arguments from one procedure to the
next.

HTH