How to get a permanent Range address?
 

Hi all,

I am trying to get the address of a range in a way that can be used
afterward even if the workbook or the sheet names change.

Now I am using the following:

range.get_Address(Type.Missing, Type.Missing,
Excel.XlReferenceStyle.xlA1, true, Type.Missing)

That will give me a string like this one: "[Book1]Sheet2!$A$1"

Retrieving the range object from that address will fail if, for
instance, I rename Sheet2 to Stocks.

Is there any way to do that?

Thanks.

How to get a permanent Range address?
 

Alex,

To do this you define a range variable and then set the variable equal to
the range. You can then reference the range even if the sheet or book name
changes:

Sub test()
Dim rngPerm As Range

Set rngPerm = Worksheets(2).Range("A1")
Worksheets(2).Name = "changed"
ThisWorkbook.SaveAs Filename:="temp"
MsgBox rngPerm.Address(external:=True)

End Sub

hth,

Doug

"Alex" wrote in message
...
Hi all,

I am trying to get the address of a range in a way that can be used
afterward even if the workbook or the sheet names change.

Now I am using the following:

range.get_Address(Type.Missing, Type.Missing,
Excel.XlReferenceStyle.xlA1, true, Type.Missing)

That will give me a string like this one: "[Book1]Sheet2!$A$1"

Retrieving the range object from that address will fail if, for
instance, I rename Sheet2 to Stocks.

Is there any way to do that?

Thanks.

How to get a permanent Range address?
 

You can create a named range. The named range is a premanent range defined on
a sheet. You can access it via code similar to

Workbooks("MyBook.xls").names("MyName").referstora nge.address
or
Workbooks("MyBook.xls").names("MyName").referstora nge.parent

--
HTH...

Jim Thomlinson


"Alex" wrote:

Hi all,

I am trying to get the address of a range in a way that can be used
afterward even if the workbook or the sheet names change.

Now I am using the following:

range.get_Address(Type.Missing, Type.Missing,
Excel.XlReferenceStyle.xlA1, true, Type.Missing)

That will give me a string like this one: "[Book1]Sheet2!$A$1"

Retrieving the range object from that address will fail if, for
instance, I rename Sheet2 to Stocks.

Is there any way to do that?

Thanks.