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?

 

[Micron]

Altering a named range with vba is altering a value in a collection of named ranges. That doesn't alter cell content where you reference a named range. Methinks you would have to do a Find, looking for all the formulas that use the old name and alter the formula.

This should only be a one-time thing, no? You would not be changing named ranges on a regular basis, right. If you are and the range is static, might as well forget the names and use a range reference.

 

[mcomp72]

But if I do it manually, it does change the formulas in the cells. As a test, I created a named range named AAA, and made a formula in a cell that referenced it (=AAA). I tried changing the name of the named range to BBB by doing the following:

1. Unprotected all the sheets in the workbook
2. clicked on the Name Manager on the Formulas tab
3. selecedt the Named Range (AAA)
4. clicked Edit
5. Typed over the name with a new one (BBB)
6. clicked OK
7. clicked Close on the Name Manager

In the names list, the named range was now BBB instead of AAA.

I then checked the cell where it had the formula =AAA. The formula had been changed to =BBB.

So doing it manually works fine. Does doing it via VBA not actually do the same thing as doing it manually?

 

[mcomp72]

This should only be a one-time thing, no? You would not be changing named ranges on a regular basis, right. If you are and the range is static, might as well forget the names and use a range reference.

The workbook I am creating will be used by many, many people, most of whom are not terribly good at the advanced features in Excel. So I'm making a sheet to make it easy for them to create, rename, and delete named ranges. It's hard to know how many times someone might want to rename one.

The thing that is really confusing to me is, why is my code working fine in changing the name if the named range does not appear in a cell, but otherwise it does not. And there's no error message when it doesn't work. It just ignores it. This makes me think it's a bug in Excel, maybe?

I did run across a similar question on Stack Overflow.

Renaming named ranges

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 strOld...

stackoverflow.com

One of the answers (it begins with "Long story short") mentions that the line of code to rename a named range is sometimes ignored. But, oddly enough, it also says that if the line is within a "triggering" sub, such as Private Sub Worksheet_Change, it should work. And in my case, that's exactly the sub that my code is in.

I find this all very confusing. Why would Excel sometimes ignore a line of code? Has anyone ever heard of this happening before? I have been writing VBA code for a few years now, and I don't remember ever hearing about Excel just arbitrarily ignoring code. Is that really a thing?

 

[Alex Blakenburg]

The only way I can get the Name change to do nothing is if the new name already exists. Is that the case ?
Also I have only tested with Workbook scoped names are "all" your names scoped as Workbook ?

 

[mcomp72]

The only way I can get the Name change to do nothing is if the new name already exists. Is that the case ?
Also I have only tested with Workbook scoped names are "all" your names scoped as Workbook ?

Strange. For me, the renaming will work exactly as it's supposed to if there is not a cell with a reference to the named range in a formula. But as soon as I create a formula with the named range, it won't do the rename after that. But if I delete that formula, then it goes back to working again.

No, the new name does not already exist. I have some code that runs that checks that before it tries to do the renaming.

Yes, all of my named ranges are scopes as Workbook.

 

[Alex Blakenburg]

Any chance you can share your workbook or a sample workbook that still has the issue when you try it at your end.
ie using DropBox Google Drive etc and make it available to anyone with the link, posting the link here.

Even with the sheet protected changing the name flowed through to this for me.

20240825 VBA change Named Range mcomp72.xlsm
	A	B	C	D
1
2				900
3
Sheet2

Cell Formulas
Range	Formula
D2	D2	=AnotherName*This_Cell7

Named Ranges
Name	Refers To	Cells
AnotherName	=Sheet1!$B$2	D2
This_Cell7	=Sheet1!$B$1	D2

 

[mcomp72]

Unfortunately I can't share the workbook. It is a proprietary thing, and there is probably 50,000 lines of code in it (yeah, it's a beast). It's basically going to be a specialty application that runs within Excel.

I decided to record a video, so you can see what is happening.

This is the code that is actually running when it attempts to rename the named range. The code is running within the Worksheet_Change sub in the "Globals" worksheet module. (This is not the full sub, but the relevant part.)

oldName = ThisWorkbook.Sheets("Globals").Range("D" & Target.Row).Value
newName = Target.Value

UnprotectAllSheets

Set nm = ThisWorkbook.Names(oldName)

ColNum = ConvertLetterToNumber("G")

ReferStr = "=Globals!R" & Target.Row & "C" & ColNum

With nm
    .Name = newName
    .RefersToR1C1 = ReferStr
    .Comment = ""
End With

ProtectAllSheets

If nm.Name <> newName Then

    MsgBox "The Global name was not able to be changed.", , AppName
   
    Call MakeColEMatchColD(Target.Row)
   
    GoTo Ending

End If

I've tried slight variations on this code, trying to figure out what might be wrong, but so far, no luck. Obviously the code works sometimes, because if there is not a cell with a formula that references the named range, it works, as you can see in the video. So why it doesn't work when there is a formula that references the named range, I have no idea.

I also tried it on my Mac with the latest version of Excel on it, and the same behavior occurred.

 

[Alex Blakenburg]

Do you have any Dim statements ?
Also do you have application.enableevents = False and then True ?

 

[mcomp72]

Do you have any Dim statements ?
Also do you have application.enableevents = False and then True ?

Yes, I use Option Explicit at the top of each module, so I have Dim statements for every variable in the sub. Here are the ones I have that are relevant to the code I posted above.

Dim ReferStr As String
Dim oldName As String
Dim newName As String
Dim ColNum As Long
Dim nm As Name

No, the sub does not have Application.EnableEvents = False anywhere in it. I rarely ever use that... only in specific cases when I know a prompt will appear that I don't want the user to have to deal with. And I don't have any reference to "On Error" in the sub.

But just to be doubly sure, as a test right now, I put this in right before the line of code that is supposed to change the name of the named range:

Application.EnableEvents = True
On Error Goto 0

The problem still occurred.

I recorded another video, this one where I step through the code so you can see what is happening when it gets to the line that is supposed to change the name of the named range.

By the way, this is the exact version of Excel I have on my Windows 10 machine:
Microsoft® Excel® for Microsoft 365 MSO (Version 2407 Build 16.0.17830.20166) 32-bit