How to find all the names of a cell? 2

[davidbeach]

I have a spreadsheet where certain cells have been given multiple names. Is there a way of easily showing all the names that refer to a given cell?

In this instance, the spreadsheet deals with relay settings, and for instance zone 1 phase mho distance reach may be named Z1_, Z1P, M1P, and perhaps others. This is to facilitate macros that can then use the setting as named for that particular relay. I can see the name that comes first alphabetically in the box at the left end of the formula bar, but is there an easy way to see all the names?

At this point I use ^G (go to) to test to see if a name relates to the cell I think it might. The Insert|Name|Define method is very cumbersome given that there are probably already 1000+ named ranges and many are mind numbingly similar (great for macros, really bad for skimming through lists), and it is sorted by range name, not by cell.

 

[IDS]

The Range.Listnames method will write a list of names, starting in the named cell, e.g.:

Range("a1").ListNames

It puts the range name in the chosen column, and the address in the next column, so you could either sort on the address column, or read it back into VBA and do what you want with it there.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[MintJulep]

Easy? No.

You can get the list of names and the range that each name refers to by looping through the Names collection.

Names(index).name
Names(index).refersto
or
Names(index).RefersToRange.Address

Life gets complicated because named ranges can be more than one cell.

You can test for multi-cell ranges with

names(index).RefersToRange.Cells.Count

and you can use Intersect to test if a specific cell is within a range.

So it's possible, and left to the interested student to devise a solution.

 

[electricpete]

The following will work if you have only one sheet or only names belonging to one sheet. I causes an error when it tests a name from another sheet because intersection doesn't like comparing different sheets. Prior to the If Not... it needs another "If..." to check if ThisName is within the same sheet at mycell and if not, it should not attempt to do the intersection check.

Function FindNamedRanges(mycell As Range)
Stop
Dim ThisName As Name
For Each ThisName In Names
  If Not (Intersect(ThisName.RefersToRange, mycell) Is Nothing) Then
    Debug.Print ThisName.Name + "- Address:" + ThisName.RefersTo
  
  End If
     

Next ThisName

End Function

=====================================
(2B)+(2B)' ?

 

[electricpete]

By the way the intersection idea was of course MintJuleps. I tried to implement it but fell slightly short.

=====================================
(2B)+(2B)' ?

 

[electricpete]

OK I think this will work

Function FindNamedRanges(mycell As Range)
    Dim ThisName As Name
    For Each ThisName In Names
        If ThisName.RefersToRange.Parent Is mycell.Parent Then
            If Not (Intersect(ThisName.RefersToRange, mycell) Is Nothing) Then
                Debug.Print ThisName.Name + "- Address:" + ThisName.RefersTo
            End If
        End If
    Next ThisName
End Function

=====================================
(2B)+(2B)' ?

 

[electricpete]

Actually it's not set up to run as a function. Change it from a function to a sub and run it from the vba interactive editor and view the output from debug.print statements in the immediate window.

=====================================
(2B)+(2B)' ?

 

[IDS]

Pete - nice function/sub, but I never understand why everyone uses debug.print when there is a spreadsheet available. I have modified the code to work as a UDF

Function FindNamedRanges(mycell As Range)
    Dim ThisName As Name, NameList(1 To 100, 1 To 2) As Variant, i As Long

    i = 1
    For Each ThisName In Names
        If ThisName.RefersToRange.Parent Is mycell.Parent Then
            If Not (Intersect(ThisName.RefersToRange, mycell) Is Nothing) Then
                NameList(i, 1) = ThisName.Name
                NameList(i, 2) = ThisName.RefersTo
                i = i + 1
            End If
        End If
    Next ThisName
    FindNamedRanges = NameList
End Function

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[MintJulep]

I built on Pete's work too. This works as a function, returning a comma separated list of names that refer to the cell that the function is pointed at.

Function FindMyNames(mycell As Range) As String
    Dim ThisName As Name
    Dim Answer As String
    Answer = ""
   
   If mycell.Cells.Count > 1 Then
        Answer = "One Cell Only Please"
        
    Else
        
        For Each ThisName In Names
            If ThisName.RefersToRange.Parent Is mycell.Parent Then
                If Not (Intersect(ThisName.RefersToRange, mycell) Is Nothing) Then
                    Answer = Answer & ThisName.Name & ", "
                End If
            End If
        Next ThisName
    
    End If
    FindMyNames = Answer
End Function

 

[IDS]

I just tidied mine up to get rid of the nasty hard coded array size

Function FindNamedRanges(mycell As Range)
    Dim ThisName As Name, NameList() As String, i As Long, NumRows As Long

    NumRows = Application.Caller.Rows.Count
    ReDim NameList(1 To NumRows, 1 To 2)
    i = 1
    For Each ThisName In Names
        If ThisName.RefersToRange.Parent Is mycell.Parent Then
            If Not (Intersect(ThisName.RefersToRange, mycell) Is Nothing) Then
                NameList(i, 1) = ThisName.Name
                NameList(i, 2) = ThisName.RefersTo
                i = i + 1
                If i > NumRows Then Exit For
            End If
        End If
    Next ThisName
    FindNamedRanges = NameList
End Function

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[electricpete]

I never understand why everyone uses debug.print when there is a spreadsheet available.

Why output to debug window?
Because it’s easier. (one line of code vs 6 lines of code you used to replace it)… outputs right to where I’m developing my program.
Not better, just easier. Thanks you guys for fixing it up to provide a better output.


=====================================
(2B)+(2B)' ?

 

[davidbeach]

Thanks all, but I seem to be particularly dense this morning. Where do I put the function (any of them) and how do I call it? I've tried a few things, but without success.

 

[MintJulep]

http://www.wikihow.com/Create-a-User-Defined-Function-in-Microsoft-Excel

 

[davidbeach]

OK, turns out my problem is the use of multiple sheets and Intersect throwing an error. Fighting other battles today, I get back to this in a day or two.

 

[electricpete]

The intersection wasn’t supposed to execute unless the names were in the same sheet “If ThisName.RefersToRange.Parent Is mycell.Parent Then..”

Apparently that didn’t work. The check assumes that the parent of name refers to a sheet. That should usually be the case, but maybe for some reason for structure of your spreadsheet it’s not? (parent is workbook… or chart…?).

If you get the error again, try to examine which names are being examined and what are the parents of those names (to figure out why the check for different sheets didn’t work).


=====================================
(2B)+(2B)' ?

 

[yakpol]

David,

You can do it without VBA.
In Excel 2007 follow menu commands:
Formulas/Use in Formula/Paste Names.../Paste List
In Excel 2003: Insert/Names/Paste... (somewhere there).

It will display a table of all names and their addresses. Sort by addresses to identify the names corresponding to the same address.

Yakpol

 

[davidbeach]

Thank you. Much nicer than the macros.

 

[electricpete]

Another simple approach which seems like it would be helpful in large complicated spreadsheets like you describe is the "trace dependents" feature.

For Excel 2000, with the cell in question selected, select Tools/Auditing/TraceDependents.

It will show arrows to all the other cells in same sheet that reference the selected cell (regardless of whether those other cells refer to selected cell by name or by address). It will also show an arrow to a little sheet icon and if you click on that arrow you see a list of all the cell addresses in other sheets that refer to your selected cell.

Then Tools/Auditing/RemoveAllArrows to get rid of the arrows

=====================================
(2B)+(2B)' ?

 

[davidbeach]

In my case, all the references are from other VBA macros, using excel as a way of moving data between programs that know nothing of each other but have export and import capabilities where excel is used to convert the data of the export file to the import file. Read it, place it, then write it.