Working with NamedRanges in VBA

[svjensen]

This might be elementary, but I cannot get it to work.

I need to name range in VBA, work with the named ranges afterwards and change the names. I have split it into e couple of questions below.

1. Naming a range
By using 'Worksheets("Ark1").Range("C4").Name = Worksheets("Ark1").Range("C3").Value' I named cell C4.
This worked fine (but it is the most appropriate method?).

2. Retrieving a name
By using 'Worksheets("Arka").Range("C4").Name' I try to retrieve the name for cell C4, but this does not work for me. In stead of the expected name I get '=Ark1!$C$4'.
How do I retrieve the name (in this case 'Test')?

3. Renaming a range
The next thing I need to do is rename the cell. How do I go about that?

/Soren

 

[VoG]

Does this help with #2?

MsgBox Worksheets("Ark1").Range("C4").Name.Name

 

[svjensen]

That did the trick!

Could you briefly help me out with the logic behind the 'name.name'?

 

[svjensen]

Let my just elaborate a bit more on #3.

I want to rename based on the content in a given cell (i.e. D3). But I assume that I need to delete the current name first - if one exists.
So in my mind I need to check if a name exists for the given range, delete it og then name it again.
Does that make sense, or should I do it differently?

 

[Jon von der Heyden]

Hi Soren

1.
This method is fine. You could use a more long-winded method but I prefer the way you are doing it. One alternative would be:
ThisWorkbook.Names.Add "Test", Worksheets("Ark1").Range("C4")

2.
Peter has already answered, but here is why it works. If you simply call:
MsgBox Worksheets("Ark1").Range("C4").Name
This will return what the named range refers to, i.e Ark1!$C$4
Using .Name.Name will return the name of the range as a string.

3.
First you need to delete the name:
ThisWorkbook.Names("Test").Delete
And then add the name again as outlined in #1 and #2.

 

[VoG]

range.Name returns a name object, and then .Name returns its name property.

 

[svjensen]

Getting smarter (or at least more knowing) every day. Excellent!!

Cheers,

Soren

 

[svjensen]

I will expand a bit on #3 with my experience in case someone else needs to do the same thing.

With the following I want to test if a given range has a name defined. If yes, then I want to delete it, and then assign a new name.
Since I get an error if I try to retrieve a name from a range that does not have a defined name, I use this to test for a name:

Dim x As Object
Dim y As String
 
On Error Resumes Next
Set x = Worksheets("Ark1").Range("C4").Name
If Err = 0 Then
    'Execute code to remove name and apply new name.
    y = WorkSheets("Ark1").Range("C4").Name.Name
    ThisWorkbook.Names(y).Delete
 
    WorkSheets("Ark1").Range("C4").Name = WorkSheets("Ark1").Range("D3").Value
End If

And as if by magic the name has changed

/Soren

 

[svjensen]

And if the range is bigger than a single cell, just insert this (i.e. C13:d13) instead of C4 in the above example.)

 

[Jon von der Heyden]

Nothing wrong with what you are doing, but here is how I would approach it. For me it's a little easier to interpret.

Sub RenameCell()
Dim rngCELL As Range
Set rngCELL = Worksheets("Ark1").Range("C4")
On Error Resume Next
    With rngCELL
        ThisWorkbook.Names(.Name.Name).Delete
        .Name = .Text
    End With
On Error GoTo 0
End Sub