Name Collection

[Jos Vens]

Hi,

I Can't get the defined names of a specific worksheet. Who can help me.

That's what I did

All defined names

For each vName in Names
    debug.print vName.Name & " " & vName.RefersTo
Next

In one worksheet, I get nothing

For each vName in Sheets(”MyWorksheet”).Names
    debug.print vName.Name & " " & vName.RefersTo
Next

However, I’ve noticed that sometimes in the excel-environment, the worksheetname is mentioned next to the defined name on the right side of the Name-box.  Especially when you have two ranges with the same name, the name of the worksheet is in the box.

When this is so, I can return with the above procedure the worksheet-specific names.  How can I procedurally put the worksheetname in the right corner of the box???  Or how can I get the names of only one sheet.  Now I have solved this by extracting the sheetname out of the RefersTo-string, but this is not a good practice.

Any help is appreciated

Jos Vens
Jos....@pi.be

[Leo Heuser]

Name CollectionJos

In Excel you have two kinds of names: Global and local.

A global name (or a workbook name) is the name of a range,
to which you can jump to from the
name box, no matter which sheet is active.
You create a global name by selecting the range and writing the name
e.g. "DataBlock1", without quotes, in the namebox
(or using Insert > Name > Define)

A local name (or a worksheet name) is the name of a range,
to which you can jump to only from the namebox in that particular worksheet
(it will not show, if you use the namebox from another sheet).
You create a local name by selecting the range and writing the name with a
prefixed sheetname like this "sheet2!DataBlock3" in the namebox
(or using Insert > Name > Define)

For each vName in ActiveWorkbook.Names

debug.print vName.Name & " " & vName.RefersTo

Next vName

and

For each vName in Names
debug.print vName.Name & " " & vName.RefersTo

Next vName

Will return all global and local names in the active workbook.

while

For each vName in Sheets("MyWorksheet").Names
debug.print vName.Name & " " & vName.RefersTo

Next vName

will return all local names in MyWorksheet.

Local names can be alike, except for the prefixed sheetname,
which are the ones you see to the right in the namebox in Insert > Name >
Define.

If you have two local names e.g. Sheet1!Test and Sheet2!Test, the namebox
will
display "Test" from sheet1 and sheet2, but they refer to two ranges in two
different sheets, as you will see, if you choose them in the namebox.

I hope this has shed some light on the matter :-)

--
Best regards
Leo Heuser
MVP Excel

"Jos Vens" <jos....@planetinternet.be> skrev i en meddelelse
news:B881974D.55BD%jos....@planetinternet.be...

[Jos Vens]

Thanks Leo,

Very clear and correct information!

Jos Vens

in article O6HKV#BrBHA.1604@tkmsftngp04, Leo Heuser at leo.h...@get2net.dk

[Leo Heuser]

You're welcome!

"Jos Vens" <jos....@planetinternet.be> skrev i en meddelelse

news:B8821974.599F%jos....@planetinternet.be...