How to reference Worksheets in VBA properly

[Michael.Tarnowski]

Hi experts in the community,

as VBA newbie I know there are two possibilities to reference a
worksheet in an application:

Example: ... Worksheets(workSheetID).Range("openIcon").Value ...

where workSheetID is either:

a.) a numerical ID, the sheet index
or
b.) a string, the name of the sheet as it is displayed in the sheet
tabs.

Question: how can I reference the sheet in VBA without being
intertwined by either sheet name changes or insertions of new sheets
by the user?

Thanks for your help
Have a nice day
Michael

 

[Dave Peterson]

The codename is much more difficult for the user to screw up.

Open the VBE
Hit ctrl-r to see the project explorer
Expand one of the projects
Under the Microsoft Excel Objects, you'll see the sheets in your workbook.

Sheet1 (Sheet1)
Sheet2 (Sheet Name that User Sees Here)

The name in ()'s is the name of the worksheet--the name that the user sees on
the Tab.

The name before the ()'s is the codename.

If you select one of the sheets in the project explorer and hit F4, you'll see
the properties window.

The (Name) property is the codename.

You can change the codename to something meaningful to make your code easier to
read.

Worksheets(1).range("a1").value = "hi"
or
worksheets("Sheet1").range("a1").value = "hi"
can be replaced with the codename (say Prices)
Prices.range("a1").value = "hi"

 

[Gary''s Student]

A few tiny trick. Say we have a worksheet that may change both its name and
position, but we want to be able to find it.

Pick a cell on the sheet and assing a Defined Name to the cell, say "qwerty"

Once this is done, you can move the sheet around and change its name, but:

Sub dural()
MsgBox (Range("qwerty").Parent.Name)
End Sub

will always give you its correct name and:

Sheets(Range("qwerty").Parent.Name).Activate

will get you there. As I said, a shabby little trick, but it works.

 

[Michael.Tarnowski]

A few tiny trick. Say we have a worksheet that may change both its name and
position, but we want to be able to find it.

Pick a cell on the sheet and assing a Defined Name to the cell, say "qwerty"

Once this is done, you can move the sheet around and change its name, but:

Sub dural()
MsgBox (Range("qwerty").Parent.Name)
End Sub

will always give you its correct name and:

Sheets(Range("qwerty").Parent.Name).Activate

will get you there. As I said, a shabby little trick, but it works.

Gary, Dave -- nice suggestions, I will work with that.
But, how should I proceed if I have define global variables for
different worksheets? -- e.g.
const wsShID_1 = "Config"
const wsShID_2 = "Sheet 1"
and so one... ?
Michael

 

[Dave Peterson]

If this was meant for me, I don't understand the question.

 

[Michael.Tarnowski]

If this was meant for me, I don't understand the question.



:

To make my point clear:
If I want to address the worksheets by global constants, I would use
the (user)names of the sheets / or the sheet index, like
const worksheetID = 1
or
const worksheetID = "MySheet"

Is there a function in VBA to determine the values (sheet names or
indices) for the constants, like
const worksheetID = foo(...)

Michael

 

[Michael.Tarnowski]

If this was meant for me, I don't understand the question.



:

To make my point clear:
If I want to address the worksheets by global constants, I would use
the (user)names of the sheets / or the sheet index, like
const worksheetID = 1
or
const worksheetID = "MySheet"

Is there a function in VBA to determine the values (sheet names or
indices) for the constants, like
const worksheetID = foo(...)

Michael

 

[Dave Peterson]

You could add a worksheet level name (hidden) to each sheet and then use that.

But it makes much more sense to me to use the codename.

 

[Michael.Tarnowski]

You could add a worksheet level name (hidden) to each sheet and then use that.

But it makes much more sense to me to use the codename.

Thank you Dave for sharing your insights.
Michael