Add
Cells(R, 2) = defined_name.Name
below the existing line beginning: Cells(R ..

or all by itself:
Range("B10").ListNames
but this doesn't reveal hidden names.

I couldn't run your code  - I "Dim'ed" the others, but Interestingly
enough "defined_name" is an undefined label, and I didn't know how to
resolve this.

Daulton showes .Name.Name. ".Name.Name" references  the Range Name
rather than the address.


Code from other ways I found to get the .Address and Range Name
also use .Name.Name.
Note: I have seen that when names  are deleted, they may remain in this
list with a #REF for the .Address. Not sure what does garbage
collection on the list.

Debug.Print " App Name Count= "; Application.Names.Count

Debug.Print "*******************************"
Debug.Print " Show Application Named Ranges"
For izi = 1 To Application.Names.Count
Debug.Print " App Names = "; izi; ; Application.Names.Item(izi);
Chr(9); Chr(9); Application.Names.Item(izi).Name
Next izi
Debug.Print

' OR

Debug.Print " Wk Bk Name Count= "; ThisWorkbook.Names.Count

Debug.Print "*******************************"
Debug.Print " Show Workbook Named Ranges"
For izi = 1 To ThisWorkbook.Names.Count
Debug.Print " Wkbk Names = "; izi; ; ThisWorkbook.Names.Item(izi);
Chr(9); Chr(9); ThisWorkbook.Names.Item(izi).Name
Next izi
Debug.Print


'However, this last gives nothing. No errors, just Count = 0.
' It makes sense, but names don't 'reside' in the sheet in which they
reside. (;-)

Debug.Print " Sheet Name Count= "; sheet4.Names.Count

Debug.Print "*******************************"
Debug.Print " Show Sheet4 Named Ranges"
For izi = 1 To Sheet4.Names.Count
Debug.Print " Sheet Names = "; izi; ; Sheet4.Names.Item(izi); Chr(9);
Chr(9); Sheet4.Names.Item(izi).Name
Next izi
Debug.Print

I got it to run, but have the following Questions about the code.
Here's my whole Sub:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Sub ListNamedCells()
' Show Range Names.
Dim R As Integer
Dim WBName As String
Dim defined_name As Object ' Error said must be Object or Variant

Workbooks.Add
WBName = ActiveWorkbook.Name
' Workbooks(WBName).Save
Cells(1, 5).Value = ThisWorkbook.Names.Count ' I added this
R = 1
For Each defined_name In ThisWorkbook.Names
Cells(R, 1).Value = R ' Show the: Item number
Cells(R, 2).Value = defined_name ' Range Address
Cells(R, 3).Value = defined_name.Name ' Range Name
R = R + 1
Next
End Sub
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Errors told me I needed the Dim's... However.

1 - How does VBA know that "defined_name" contains the name? I don't
see where it gets assigned [defined_name = ??]. Is it just because
the "For Each" construct needs a place to hold the members of the
collection "ThisWorkbook.Names", so it puts them there?

2 - What is the purpose of the line:
Workbooks(WBName).Save
Works fine without it.

3 - With these two statements:
Workbooks.Add
WBName = ActiveWorkbook.Name

I have Active workbook  Object confusion.
Why isn't the added workbook the active one - Is it because adding
a workbook doesn't make it active?
If the added one is not the active one, why do the Names get stored
there? There is no explicit workbook or sheet specified "Cells(...".

> Errors told me I needed the Dim's... However.
>
> 1 - How does VBA know that "defined_name" contains the name? I don't
> see where it gets assigned [defined_name = ??]. Is it just because
> the "For Each" construct needs a place to hold the members of the
> collection "ThisWorkbook.Names", so it puts them there?

Yes, I think it goes something like that. You could:
Dim defined_name As Name
since you know they're going to be names.


>
> 2 - What is the purpose of the line:
> Workbooks(WBName).Save
> Works fine without it.

It does indeed.. no idea.

>
> 3 - With these two statements:
> Workbooks.Add
> WBName = ActiveWorkbook.Name
>
> I have Active workbook  Object confusion.
> Why isn't the added workbook the active one - Is it because adding
> a workbook doesn't make it active?

It does. The line
WBName = ActiveWorkbook.Name
only assigns the name of the active workbook to the string WBName
(usually Book2.xls or some such), it does not activate anything.

you missed my last and therefore the essence of my last confusion.
OK. Adding a workbook  doesn't make it active. Makes sense...Then
there is still an issue I can't resolve.

I don't think I did: In your last post you said: "Is it because adding
a workbook  doesn't make it active?" I replied "It does." meaning that
i does make the new workbook the active one.

So your statement below "OK. Adding a workbook doesn't make it
active." is at odds with this.

This should answer all your questions below.

I misinterpreted your answer. AND understand adding
makes it active.
BUT, then *after* Adding, we get the active workbook  name (for
getting the range names). (I was assuming this was the original
workbook Name, not the Added one)
I think this tells me that Range names  are actually in the
Application Object (not the Workbook) - along with the fact that my
other way of getting the Range Names uses the Application Object.
[Application.Names.Item(x)]
[Application.Names.Item(x).Name]

The fact that these are valid:
[ThisWorkbook.Names.Item(x)]
[ThisWorkbook.Names.Item(x).Name]
is because Excel allows referencing a lower object in the accession,
right?
The full accession must be (and these are valid):
[Application.ThisWorkbook.Names.Item(x)]
[Application.ThisWorkbook.Names.Item(x).Name]

Have I got it now ?

Someone is misleading you.

When you add a workbook  like that, it DOES become active. Try this


MsgBox ActiveWorkbook.Name
Workbooks.Add
MsgBox ActiveWorkbook.Name

If you Dim defined_name as a Variant, it will be able to hold anything you
throw at it.

The .Name.Name syntax is typically used to retrieve the name associated with
a Range. Not when working through the list of defined names.

The #ref on a name indicates that the cell  that it referred to has been
removed. This is either by deleting the column or row that it was in, or by
Cut/Paste of another cell over it (n.b. not Copy/Paste).

There is no garbage collection on the list. The #ref items will remain
until you remove them.