Cannot change name of Named Range in VBA if named ranged appears in a cell formula

[mcomp72]

I have code that will change the name of a named range. It works just fine if the named range does not appear in a formula anywhere in the workbook. But if it does appear, even in just one formula (and even in something as simple as something like =MyName), the code doesn't change the name. It doesn't cause an error, it just doesn't do what it's supposed to. It's as if it is simply ignoring the line of code.

The code:

UnprotectEntireWorkbook
UnprotectAllSheets
               
Set nm = ThisWorkbook.Names(oldName)
               
nm.Name = newName
               
ProtectAllSheets
ProtectEntireWorkbook

oldName and newName are string variables, and I have confirmed the values of both are legit names. UnprotectEntireWorkbook, UnprotectAllSheets, ProtectAllSheets, and ProtectEntireWorkbook are all subs that do exactly what their names suggest.

So my workbook is unprotected and all of the sheets within the workbook are unprotected, yet the line nm.Name = newName does not do anything if the named range (oldName) appears in any cells in the workbook.

Does anyone have any idea why this would be happening?

 

[Alex Blakenburg]

I need to do some more testing but won't be able to until this evening my time.
You are showing the line of code resetting column E but not the line in which change column D to the new value.

 

[mcomp72]

I've been doing more testing, and have more videos to share. My problem might be caused by a bug in Excel, but I'm not sure. Take a look at all three videos and you'll see what I mean.

I may try to build a test workbook, to see if the problem happens in it. If I'm able to get it to do it, I'll post that file here.

 

[mcomp72]

Okay... I made a test workbook to highlight the problem, and also created another video showing when it works correctly and when it doesn't.

You can download the test workbook here:

Dropbox

www.dropbox.com

After I made the video, I saved the file, closed it, reopened it, and pressed the PeterP button. That time, it worked. So there's something to do with closing the file and reopening it that seems to fix the problem temporarily.

If you try it on your computer, I'll be curious if you encounter the same behavior that I did in the video, and if closing & reopening the file has any effect.

The code that is tied to the two buttons in the test workbook is very simple -- I kept it that way on purpose. (It's in Module1 of the file.) Here it is, in case anyone can't download the file.

Sub ChangeNameToSpiderMan()

If NamedRangeExists("SpiderMan") Then
    MsgBox "The name SpiderMan already exists."
Else
    ThisWorkbook.Names("PeterP").Name = "SpiderMan"
End If

End Sub

Sub ChangeNameToPeterP()

If NamedRangeExists("PeterP") Then
    MsgBox "The name PeterP already exists."
Else
    ThisWorkbook.Names("SpiderMan").Name = "PeterP"
End If

End Sub

If you have any idea why the problem could be happening, I'm all ears. It doesn't make any sense to me.

 

[Alex Blakenburg]

I can't test until tonight but if you are still testing try declaring old & new name as Variant. Although it shouldn't make a difference it did seem to in my last few passes last night.

 

[mcomp72]

I can't test until tonight but if you are still testing try declaring old & new name as Variant. Although it shouldn't make a difference it did seem to in my last few passes last night.

While I haven't tried it yet, I don't think that will solve it. If you take a look at the last video I posted and the test workbook I made, the code is changing the name not through a variable, but through a name that is "hard-coded". And the problem still occurs, as shown in the video. I'm thinking this is a bug in Excel. What do you think?

 

[Alex Blakenburg]

I agree, it looks to be a bug to me. Using your test workbook the code below is the only way I can get it to work and it is very ugly.

Sub ChangeNameToSpiderMan()

Dim oldName As String
Dim newName As String
Dim tempName As String

oldName = "PeterP"
newName = "SpiderMan"
tempName = "Z_" & Format(Now, "yyyymmdd_hhmmss")

If NamedRangeExists(newName) Then
    MsgBox "The name " & newName & " already exists."
Else
    ThisWorkbook.Names(oldName).Name = newName
    If NamedRangeExists(oldName) Then
        With ActiveWorkbook
            .Names.Add Name:=newName, RefersToR1C1:=ThisWorkbook.Names(oldName).RefersToR1C1
            .Names(newName).Name = tempName
            .Names(tempName).Delete
            .Names(oldName).Name = newName
        End With
    End If
End If

End Sub

Sub ChangeNameToPeterP()

Dim oldName As String
Dim newName As String
Dim tempName As String

oldName = "SpiderMan"
newName = "PeterP"
tempName = "Z_" & Format(Now, "yyyymmdd_hhmmss")

If NamedRangeExists(newName) Then
    MsgBox "The name " & newName & " already exists."
Else
    ThisWorkbook.Names(oldName).Name = newName
    If NamedRangeExists(oldName) Then
        With ActiveWorkbook
            .Names.Add Name:=newName, RefersToR1C1:=ThisWorkbook.Names(oldName).RefersToR1C1
            .Names(newName).Name = tempName
            .Names(tempName).Delete
            .Names(oldName).Name = newName
        End With
    End If
End If
End Sub

Where the function NamedRangeExists is your original being:

Function NamedRangeExists(rangeName As String) As Boolean
 
On Error Resume Next
NamedRangeExists = Not ThisWorkbook.Names(rangeName) Is Nothing
On Error GoTo 0
 
End Function

 

[mcomp72]

Alex, that is amazing. It never occurred to me to use the tempName variable the way you did as a work-around.

I made a slight alternation to it, to cover if the user had formulas somewhere in the workbook that referenced both oldName and newName. This seemed to solve that issue.

Sub ChangeNameToSpiderMan()

Const oldName = "PeterP"
Const newName = "SpiderMan"

Call ChangeTheName(oldName, newName)

End Sub

Sub ChangeNameToPeterP()

Const oldName = "SpiderMan"
Const newName = "PeterP"

Call ChangeTheName(oldName, newName)

End Sub


Sub ChangeTheName(oldName As String, newName As String)

Dim tempName As String

tempName = "Z_" & Format(Now, "yyyymmdd_hhmmss")

If NamedRangeExists(newName) Then

    MsgBox "The name " & newName & " already exists."
    
Else

    ThisWorkbook.Names(oldName).Name = newName
    
    If NamedRangeExists(oldName) Then
    
        With ActiveWorkbook
        
            .Names.Add Name:=newName, RefersToR1C1:=ThisWorkbook.Names(oldName).RefersToR1C1
            
            .Names(newName).Name = tempName
            
            .Names(tempName).Name = newName
            
            .Names(oldName).Delete
            
        End With
        
    End If
    
End If

End Sub

I'm actually a bit surprised that it worked, but it seems to... at least in the test workbook. I don't think I ever would have figured this out without your idea of using tempName. Thank you!!

Now to implement this into my actual workbook to see if it works there. Fingers crossed!

 

[mcomp72]

[post deleted]

 

[mcomp72]

Alex, it turns out I was mistaken -- the alteration to your code that I made did not, in fact, solve the issue I was trying to solve. I am going to test the code you posted, unaltered this time, in my real workbook to see if that works. I was hoping to figure out a way to deal with the possibility of there being a formula in the file that referenced the oldName and one that referenced the newName. In that instance, one of the formulas will be left with the reference of the tempName. But that may just have to be something I have to deal with, unless you can think of solution to that.

Here's a revision to the test workbook so you can see what I mean. Try changing the name to SpiderMan and you'll notice the formula that referenced SpiderMan changed to reference the tempName.

Dropbox

www.dropbox.com

 

[mcomp72]

Alex, the code you posted did work in my actual workbook. So thank you for taking the time to come up with that. Much appreciated!

If you have any thoughts on how to deal with the problem I mentioned in my last post about if there is both a formula referencing the oldName and one referencing the newName, so the one doesn't end up with a formula referencing the tempName, let me know. Otherwise, I think I can just roll with what you've come up with and let my users know that might happen on rare occasions.

I've also reported this to Microsoft as a possible bug. Sometimes when I reach out to them they respond, but not always. If I hear anything back, I will try to remember to come back to this thread and post an update.