Renaming named ranges

Question

I have a workbook with many named ranges to rename. I have a spreadsheet with the old names and the new names.

This works:

Dim strOldName As String
Dim strNewName As String            

strOldName = rngNamedRanges.Cells(1, 6).Value2
strNewName = strOldName & "_Renamed"

With ActiveWorkbook.Names(strOldName)
    .Name = strNewName
End With

This does not:

Dim strOldName As String
Dim strNewName As String            

strOldName = rngNamedRanges.Cells(1, 6).Value2
strNewName = CStr(rngNamedRanges.Cells(1, 8).Value2)

With ActiveWorkbook.Names(strOldName)
    .Name = strNewName
End With

Clearly, I'm doing something wrong assigning strNewName.

I have also tried using .text, .value, and trimming the string, all with the same non-result.

The non-working code does not produce an error. It just fails to change the name.

rngNamedRanges.Cells(1,6) refers to a cell containing straight text.
rngNamedRanges.Cells(1,8) refers to a cell containing a CONCATENATE formula which creates the new range name based on several other pieces of info contained in other columns.

Answer 1

Renaming is always a pain. Try the following:

Sub Rename()

    StrOld = "MyRange1"
    StrNew = StrOld & "_Renamed"

    Range(StrOld).Name = StrNew
    With ThisWorkbook
        .Names(StrOld).Delete
    End With

End Sub

Looping is up to you. :) Let us know if this helps.

Answer 2

Thanks for the input, all! I still don't understand why the first example I gave worked and the second one did not. Nonetheless, the following code appears to be working. I apologize for poor formatting of the snippet.

Dim rngNamedRanges As Range
Dim strOldName As String
Dim strNewName As String
Dim strRefersTo As String

    Set rngNamedRanges = ActiveWorkbook.Worksheets("Named Ranges").Range("A2:K909")

i = 1
Do Until [CONDITION] = ""
    strOldName = CStr(Trim(rngNamedRanges.Cells(i, 6).Value2))
    strNewName = CStr(Trim(rngNamedRanges.Cells(i, 8).Value2))

            strRefersTo = ActiveWorkbook.Names(strOldName).RefersTo

                'Update all the formulas to use the new name.
                For Each ws In Worksheets
                    If ws.Name <> "Named Ranges" Then
                        ws.Cells.Replace What:=strOldName, Replacement:=strNewName, LookAt _
                        :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                        ReplaceFormat:=False
                    End If
                Next

            'Delete old name and replace with the new one
            ActiveWorkbook.Names(strOldName).Delete
            ActiveWorkbook.Names.Add strNewName, strRefersTo

        End If

    strOldName = ""
    strNewName = ""

    i = i + 1
Loop

Answer 3

This is a really simple way to rename a range name. I got this from Paul Kelly at Excel Macro Mastery. Works great.

Sub rename_a_range_name()

Dim NewName As Variant

ThisWorkbook.Names("CurrentName").Name = "NewName"

End Sub

Answer 4

I modified the above code to rename some NAMES. With regards to the code immediately above, to loop through the worksheets and find/replace each NAME in formulas (etc)... I found that I needed to remove the Sheet Reference that is in the beginning of the string for each NAME's name.

'Update all the formulas to use the new name.
    myStart = InStr(1, strOldName, "!", vbTextCompare) + 1
    myLength = Len(strOldName) - myStart + 1
    strOldNameSHORT = Mid(strOldName, myStart, myLength)

    myStart = InStr(1, strNewName, "!", vbTextCompare) + 1
    myLength = Len(strNewName) - myStart + 1
    strNewNameSHORT = Mid(strNewName, myStart, myLength)

For Each ws In Worksheets
    ws.Cells.Replace What:=strOldNameSHORT, Replacement:=strNewNameSHORT,
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, 
        SearchFormat:=False, ReplaceFormat:=False
Next

Answer 5

Long story short

Putting the update into e.g. the worksheet change event is likely to work.

Root Cause

Update: UDFs called from cells are not allowed to change things on the sheet. (That's what I did)

Old: I guess there are certain calculation processing phases, where updates to names are allowed or not allowed.

More explanation and findings

I wanted to rename a range and it was sometimes ignored and sometimes I got an error 1004 (application- or object-defined error - in German: Anwendungs- oder objektdefinierter Fehler).

Let's say one has a rename function like this:

Function rename( nold As String, nnew As String ) As Boolean
  ThisWorkbook.Names(nold).Name = nnew
  rename = True
End Function

I found out the following:

• if the update is triggered by some UDF (user-defined cell function) on some cell update it will be ignored in some cases and in other cases the 1004 error is raised
  • e.g. putting some =rename("oldName", "newName") into A1 where oldName exists
  • why and when it is ignored or the error is raised is unknown to me
• if the update is triggered by some event, e.g. the Private Sub Worksheet_Change(ByVal Target As Range) it will always be applied

Other side-effects

In finding out all this and debugging it, it may have caused that cells got locked automagically and thus also causing some 1004 error.