The safest way (unless the name is a formula) is to reference the name via
the Worksheet.Names("Bob") collection rather than the Workbook.names()
collection.
It also works if you reference the Name in the Workbooks.name collection but
prefacing it with "sheet1!"

If you have a global name and a local name with the same name all bets are
off and life gets tricky (although it can be done ... I just don't recommend
it, there is a Name Manager filter to spot these)

If the name references a formula you should use Evaluate but bear in mind
that application.evaluate defaults to the active worksheet for unqualified
references.

regards

PS: its not Jan Karel Pieterse's Name Manager: we co-authored it.

Charles Williams
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"John" wrote in message
...
Hi there,

I've been trying out Jan Karel Pieterse's Name Manager add-in
(http://www.decisionmodels.com/downloads.htm#namemanager) which seems to
work very nicely, but I have a question: The add-in has a function to
convert a Name from Global to Local and vica-versa and when you convert to
local the name changes from:

"Bob" (RefersTo Sheet1!$E$7)

to

"Sheet1!Bob" (RefersTo Sheet1!$E$7)

That's fine, but the name in the drop down box (to the left of the address
bar) continues to show "Bob". So my question is, is how can you retreive
that name ("Bob") in code as both .Name and .NameLocal return
"Sheet1!Bob"?

(The context to this is that I'm converting all names with formulas to
cell references.)

Best regards

John