Excel VBA worksheet.names vs worksheet.range

Question

Hi There

The question I have is a quick one to do with the VBA object model. I have created a defined name/range on a worksheet called "bob", pointing to a single cell. I have a bunch of other name/ranges set up on this worksheet, which I didn't create. All the other ones work perfectly. The new one created by me does not work perfectly in the following way:

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 2nd 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?

Many thanks in advance for you assistance :)

Mike

Answer 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.

Answer 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.