Excel VBA worksheet.names vs worksheet.range
excel, range, vba
Solution
Yes, you are right. Names can be local (belong to a worksheet) and global (belong to a workbook).
`(worksheet object).Names("bob")` will only find a local name. Your name is obviously global so you could access it as `(worksheet object).Workbook.Names("bob").RefersToRange`.
The "other names" are probably local. They only appear in the ranges list when their parent sheet is active (check that out). To create a local name, prepend it with the sheet name, separated by a '!': `'My Sheet Name'!bob`.
Problem
I have created a defined name/range on a worksheet called `bob`, pointing to a single cell. There are a number of other name/ranges set up on this worksheet, which I didn't create. All the number/ranges work perfectly except for mine. I should be able to refer to the contents of this cell by using either of the following statements: ``` (worksheet object).Names("bob").RefersToRange.Value (worksheet object).Range("bob").Value ``` However, only the second statement, referring to the `Range` works for some reason. The first one can't find the name in the `Names` list. My questions are: - What is the difference, if any, between a `Name` and a `Range`? - Is this something to do with the global/local scope of my name/range? - How were the other name/ranges created on the sheet so that they appear in both the worksheets `Name` and `Range` list?