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

 

[Jon von der Heyden]

In fact I suppose the one thing this isn't doing is validating that the text in the cell is a valid name. There are various rules around valid names.

This method here makes sure that if the text in the cell is an invalid name, then it will leave it be (no delete).
If the cell is not named then it will name it.
If the cell is already named then it will rename it and delete the old name.

Sub RenameCell()
'//renames a cell, removes the old name
    
    Dim rngCELL As Range
    Dim strNAME As String
    
    Set rngCELL = Worksheets("Ark1").Range("C4")
    
    On Error Resume Next
        strNAME = rngCELL.Name.Name
    
    With rngCELL
        On Error GoTo errHandler
            .Name = .Text
        On Error GoTo Finish
            ThisWorkbook.Names(strNAME).Delete
    End With
    
    GoTo Finish
    
errHandler:
    MsgBox "The text in " & rngCELL.Address & " is not a valid range name!", vbCritical
    
Finish:
    
End Sub

 

[svjensen]

I follow you most of the way, but I do have one question.
After deleting the current/old name, you set the new name by using '.Name = .Text'.
But if I understand it correctly that will use the content (text) of the cell itself as the new name. Which if not want I need.
I can of course replace that bit, but I would like to understand exactly what you are trying to do.

Thanks, Soren

 

[Jon von der Heyden]

You want to take the text from the cell above don't you? If so then use:

.Name = .Offset(-1).Text

 

[svjensen]

Ok, got that.

Now, I would like to try to expand it to handle dynamic ranges.
Let's say that I would name a range TestRange by using the following:
=OFFSET(Ark1!$A$1,0,0,COUNTA(Ark1!$A:$A))

How can I do that in VBA?

 

[Jon von der Heyden]

Why would you create it with VBA? It's a one off set-up not so?

ThisWorkbook.Names.Add "TestRange", "=OFFSET(Ark1!R1C1,0,0,COUNTA(Ark1!C1))"

 

[svjensen]

I have a number of spreadsheets, which are used for user inputs in various areas.
The sheets contain a number of variables, som of which are used to populate data validation lists.
The variables are updated from a central data file, which (via VBA) opens the files and copies the data for the variables.

Some of the data validation lists are dependent lists, and I need a way to update the name of the subordinate lists, if the content of the "parent" list is changes.
I have tried with a simple macro which is executed opun canges in the sheet, but this only seems to work if the change is made manually (i.e. typed into the cell) - and not if the content is linked to an other sheet or updated via a macro.

So my idea is to build a macro, that can search for changes in the "parent" lists, and make the appropriate updates. And this thread is part of that.

Hope it makes sense. If you have any other suggestions as to how I can do it, I would - obviously - appreciate that a lot.

Cheers

 

[svjensen]

In the thread so far I have know exactly which cell or cells I needed to get the name from, and by using "strNAME = rngCELL.Name.Name" we pull this out.

Now, I want to be able to handle dynamic ranges, and - by definition - I do not know the cells which a given range covers. Is there a way to go about this?
I should mention, that I do know, that the only data in the column is the data that I need.