shankar
04-22-2009, 04:31 AM
I am a bit confused about the correct syntax for the RefersTo parameter, esp when referencing with a different workbook.
Let's say I have a named range which is local to sheet1 of workbook1
workbook1.worksheets("sheet1").names.add name = "Name1", RefersTo:=workbook1.worksheets("sheet1").range("A1")
Now I want to refer to it in workbook2. Is the following correct?
workbook2.Names.Add Name:="NewName", RefersTo:="=" & workbook1.Name & "." & "sheet1!Name1"
Also is the first statement entirely equivalent to the following?
workbook1.names.add name:="sheet1!Name1", RefersTo:=workbook1.worksheets("sheet1").Range("A1")
Does this mean that if sheet1 of workbook1 is not the active sheet, then referring to "Name1" without "sheet1!" in front of it would cause an error in both cases?
I am a bit confused about the usage of the bang symbol. In RefersTo as well as in names, this comes in between the worksheet and the range (instead of a period, as otherwise). But between the workbook and the worksheet, is a bang used, or is it still the period?
Thanks.
Shankar
Let's say I have a named range which is local to sheet1 of workbook1
workbook1.worksheets("sheet1").names.add name = "Name1", RefersTo:=workbook1.worksheets("sheet1").range("A1")
Now I want to refer to it in workbook2. Is the following correct?
workbook2.Names.Add Name:="NewName", RefersTo:="=" & workbook1.Name & "." & "sheet1!Name1"
Also is the first statement entirely equivalent to the following?
workbook1.names.add name:="sheet1!Name1", RefersTo:=workbook1.worksheets("sheet1").Range("A1")
Does this mean that if sheet1 of workbook1 is not the active sheet, then referring to "Name1" without "sheet1!" in front of it would cause an error in both cases?
I am a bit confused about the usage of the bang symbol. In RefersTo as well as in names, this comes in between the worksheet and the range (instead of a period, as otherwise). But between the workbook and the worksheet, is a bang used, or is it still the period?
Thanks.
Shankar