Range vs RefersToRange

[BizJack]

Hi,

I have a named range (i.e. RangeNameThatContainsSheetName) that contains a string.
This string contains a worksheet name.

In VBA, I used the following 2 formulas to obtain the string stored in that named range.

Option 1:
SheetName = Range("RangeNameThatContainsSheetName").Value

Option 2:
SheetName = ThisWorkbook.Names("RangeNameThatContainsSheetName").RefersToRange.Value

Both options work fine.
Note: in my function the first one will execute faster than the second one.

However, I wonder what is really the difference between the two...

Any suggestions when to use and not to use each option?

In advance, thank you.

Jack

 

[NARAYANK991]

Hi Jack ,

In Excel , there are many ways of doing the same thing ; after all the common example would be VLOOKUP versus INDEX / MATCH.

What you are doing in Option 2 is using indirection ; since you are accessing the name of the named range first , and then using that to access its contents , it is a more roundabout way of achieving the same thing.

However , it would be the only way of doing it if you did not know the name of the named range to start with. In such a case , the logical way would be to step through all the named ranges , and where the contents of the named range match a sheet name within the workbook , you could return that named range as the result. Of course this is assuming that you have only one named range which contains a sheet name.

The worksheet function INDIRECT also has a similar functionality , and in certain cases , it is the only function which will do what you want.

Narayan

 

[Lori]

Option 1 implicitly refers to the ActiveSheet and so will return an error if the workbook containing the code is not active when the code is run. Option 2 doesn't depend on which sheet is active. To get the same results you can make one of the following changes:

- In Option 1 insert ThisWorkbook.Worksheets(1). at the beginning.
- In Option 2 Change ThisWorkbook to ActiveSheet

Another approach is to use: Sheet1.[Name] where Sheet1 is the codename for the first sheet (as you see in the VB Editor). This is generally my preference as it returns a variant error if the name doesn't exist so you can test for errors using IsError whereas the other two options throw an error dialogue message that interrupts the code. Also it doesn't depend on which sheet is active.

In general it is common for different approaches that look to do the same thing on the face of it to actually have subtle differences on closer inspection. The Vlookup and Index/Match formulas are another example. Although both evaluate to the same thing, Vlookup returns the value in the returned cell whereas Index/match actually returns a reference to the returned cell. This distinction can sometimes be useful such as in named formulas for data validation.

 

[BizJack]

Thank you both for your answers and your help.
Kind regards,
Jack