Alternative named range

[tiredofit]

It is safer to refer to worksheet codenames than worksheet names because users can easily change the name of a worksheet but not its codename, so I tend to write:

wksData.Cells(1, 1).Value = 10

as opposed to:

Worksheets("Data").Cells(1, 1).Value = 10

Is there something similar for named ranges, ie I prefer NOT to write:

wksData.Range("SomeName").Value = 10

just in case the named range SomeName has been changed.

Thanks

 

[Joe4]

You can set up range variables and set them, just the same as you can with worksheets, i.e.

Dim rng as Range
Set rng = Range("A1")

wksData.rng.Value = 10

 

[mikerickson]

One way to keep your named ranges would be to set their visible property to False.

ThisWorkbook.Name("myNamedRange").Visible = False

Which removes the named range from the Names manager, so the user can't change them. The user can create a second name for the same range but not remove the hidden name.
(But deleting the cells in the range, making the Name refer to #REF is another issue)

 

[tiredofit]

One way to keep your named ranges would be to set their visible property to False.

ThisWorkbook.Name("myNamedRange").Visible = False

Which removes the named range from the Names manager, so the user can't change them. The user can create a second name for the same range but not remove the hidden name.
(But deleting the cells in the range, making the Name refer to #REF is another issue)

Thanks but I'm in the process of changing some of my named ranges myself!

Like the worksheets example, although I may want to change the worksheets name, the worksheets' codename will remain the same.

So was just wondering if I could do the same for named ranges.

 

[Joe4]

Like the worksheets example, although I may want to change the worksheets name, the worksheets' codename will remain the same.

So was just wondering if I could do the same for named ranges.

Did you see my reply?
I showed you how to create object variables for your range the same as you did for your sheet.

 

[kvsrinivasamurthy]

Every sheet will have index which will not change even name is changed . You can use that.

 

[tiredofit]

Thanks for all the suggestions.