Obtaining the 'Name' of a Specific Cell as Defined by Excel's Name Manager-CodePudding

I am creating a class module that I would like to be used with minimal setup/preparation as I expect it to be used within multiple projects. One of the requirements is that the class module should first check for a special worksheet that is to be hidden named _SYS that will store property values from the class so they may persist between sessions.

One way I was hoping to accomplish this task was by having the class create named ranges on the fly. This shouldn't be much of an issue using WorkbookObj.Names.Add method, but I would like to know if a range has its own property that would allow me to set or retrieve its name as opposed to needing to loop through the collection of names.

My goal was to have the class loop through all names within a column in the hidden worksheet to determine the next available row to use to by determining if its name began with an underscore or not (_Name1, _Name2, A3 <- This would be the next available range to use).

I figured that Range.Name would have accomplished this for me, but it appears to just be returning an "extended address" instead of the name:

As you can see below, it successfully created the named range, but it will not return its name.

Here's essentially what I am attempting to do:

Private Function NextUnnamedCell() As Range

    Dim Cell As Range
    For Each Cell In Sheet1.Columns(4).Cells
        If Not Left(Cell.Name, 1) = "_" Then
            Set NextUnnamedCell = Cell
            Exit Function
        End If
    Next Cell

End Function

CodePudding user response：

The Name object has a Name property, so:

Cell.Name.Name

should give you the name, as long as Cell has a Name, otherwise you'll get an error.