Excel VBA worksheet.names vs worksheet.range

Question

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:

1. What is the difference, if any, between a Name and a Range?
2. Is this something to do with the global/local scope of my name/range?
3. How were the other name/ranges created on the sheet so that they appear in both the worksheets Name and Range list?

Solution 1

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.

Solution 2

I don't know how to do it with code, but if you go to the Name Manager Under the Formula tab group in the Ribbon in Excel 2007, you can create names and choose their scope.