Sheet vs. Worksheets

[Guest]

I'm trying to avoid potential problems caused by the change of the name or
moving of my sheets. How can I refer to a sheet regardles its name or
position.
The VBA project explorer refers to sheets as Sheet1, Sheet2 and so on.
How can I do the same in my code?

 

[Guest]

Sheets are identified either by their name or position. If you need to
change both, consider putting a unique marker in a specific cell. You can
then search the sheets, looking for the marker.

 

[Dave Peterson]

Inside your project, you can use that same Codename.

If you have a worksheet that has a codename of Sheet1, then no matter what the
Name of the worksheet is (on the bottom tab in excel), you can refer to it by
something like:

Sheet1.Range("a1").value = "hi"

In fact, you may find that it makes your coding life easier/more understandable
if you change the codename to something significant.

Inside the VBE
Show the project explorer
Select your sheet.
Hit F4 to view its properties
Change the (Name) property to something that makes sense in your code.
(Like Prices)

The Name (without the ()'s) is the name the user sees on the worksheet tab.

Then your code could look like:

Dim Res as Variant
res = application.vlookup(somevalue, prices.range("a:e"),3,false)
if iserror(res) then
'not found
else
msgbox res
end if

 

[Richard Winstone]

Instead of using, for example, Worksheets("Sheet1"), you can use just
Sheet1.

So
x = Sheet1.Cells(1, 2).Value
will work even if the worksheet is renamed while
x = Worksheet("Sheet1").Cells(1, 2).Value
will not.

I'm trying to avoid potential problems caused by the change of the name or
moving of my sheets. How can I refer to a sheet regardles its name or
position.
The VBA project explorer refers to sheets as Sheet1, Sheet2 and so on.
How can I do the same in my code?

Richard
--