Working with NamedRanges in VBA

svjensen

Board Regular
Joined
Jun 10, 2009
Messages
118
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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Does this help with #2?

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

svjensen

Board Regular
Joined
Jun 10, 2009
Messages
118
That did the trick!

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

svjensen

Board Regular
Joined
Jun 10, 2009
Messages
118
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

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Legend
Joined
Jun 19, 2002
Messages
63,650
range.Name returns a name object, and then .Name returns its name property.
 

svjensen

Board Regular
Joined
Jun 10, 2009
Messages
118

ADVERTISEMENT

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

Cheers,

Soren
 

svjensen

Board Regular
Joined
Jun 10, 2009
Messages
118
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:

Code:
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

Board Regular
Joined
Jun 10, 2009
Messages
118
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

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
Nothing wrong with what you are doing, but here is how I would approach it. For me it's a little easier to interpret.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,710
Messages
5,597,702
Members
414,164
Latest member
ARTW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top