vba 重命名命名范围

Renaming named ranges

提问by WPO

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.

显然，我在分配strNewName.

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

我还尝试使用.text,.value和修剪字符串，所有结果都相同。

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 CONCATENATEformula which creates the new range name based on several other pieces of info contained in other columns.

rngNamedRanges.Cells(1,6)指包含纯文本的单元格。
rngNamedRanges.Cells(1,8)是指包含CONCATENATE公式的单元格，该公式根据其他列中包含的其他几条信息创建新的范围名称。

回答by Jerome Montino

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.

循环取决于你。:) 如果这有帮助，请告诉我们。

回答by WPO

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

回答by aVIPtoYou

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.

我修改了上面的代码来重命名一些NAMES。关于上面的代码，要遍历工作表并在公式（等）中查找/替换每个 NAME...我发现我需要删除每个 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

回答by Doug Watson

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

这是重命名范围名称的一种非常简单的方法。我从 Excel Macro Mastery 的 Paul Kelly 那里得到了这个。效果很好。

Sub rename_a_range_name()

Dim NewName As Variant

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

End Sub

回答by Andreas Dietrich

Long story short

长话短说

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

将更新放入例如工作表更改事件中可能会起作用。

Root Cause

根本原因

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

更新：从单元格调用的 UDF 不允许更改工作表上的内容。（这就是我所做的）

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 ignoredand sometimes I got an error 1004(application- or object-defined error- in German: Anwendungs- oder objektdefinierter Fehler).

我想重命名一个范围，但有时会被忽略，有时会出现错误1004（application- or object-defined error- 德语：）Anwendungs- oder objektdefinierter Fehler。

Let's say one has a renamefunction like this:

假设有一个rename这样的函数：

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 casesand in other cases the 1004error is raised
  • e.g. putting some =rename("oldName", "newName")into A1where oldNameexists
  • 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

• 如果更新是由某些单元格更新上的某些 UDF（用户定义的单元格函数）触发的，则在某些情况下将被忽略，而在其他情况下1004会引发错误
  • 例如放一些=rename("oldName", "newName")到A1其中oldName存在
  • 我不知道为什么以及何时忽略它或引发错误
• 如果更新是由某个事件触发的，例如Private Sub Worksheet_Change(ByVal Target As Range)它将始终被应用

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 1004error.

在找出所有这些并对其进行调试时，可能导致单元格被自动锁定，从而也导致了一些1004错误。