VBA Code to Rename a Defined Range

[azbasketcat]

I am trying to find a way to use VBA to change the name of a Defined Range.

Everything I have found so far creates a new range and then deletes the old range. I have used the existing named ranges throughout a large workbook, so I don't want to delete the existing named ranges.

Also, I have the names of the ranges I want to rename (a subset of all the named ranges in the workbook) in a range called "ExistingNames" and then I have another named range called "NewNames" which has the new name. My hope is to have the macro loop through ExistingNames and for each range assign the new name.

Any suggestions would be greatly appreciated. Thanks

 

[Jerry Sullivan]

Hi Joe, Does your existing name "cat" match the case of the value in Sheet2!A4?
If the existing name is "Cat" and Sheet2!A4 is "cat", my NameChanger3 will give a message "Old name: cat not found".

Denis' NameChanger code and my NameChanger2 example, will not find the name but there is no report of the exception.

 

[JoeMo]

Hi Joe, Does your existing name "cat" match the case of the value in Sheet2!A4?
If the existing name is "Cat" and Sheet2!A4 is "cat", my NameChanger3 will give a message "Old name: cat not found".

Denis' NameChanger code and my NameChanger2 example, will not find the name but there is no report of the exception.

Hello Jerry,
To answer your first question, the name "cat' is lower case in the Name Manager and in sheet2 A4.

I ran both versions of the code you posted: NameChanger2, and NameChanger3. Namechanger2 ran w/o a hitch but left "cat" intact on the Name Manager and didn't add "dog". Once again, I stepped through the code to confirm that arNames(1,2) was in fact "dog".

NameChanger3 ran w/o a hitch and returned the message:
MsgBox "Unable to rename " & sOld & " to " & sNew & "." & vbCr _
& "Make sure to use valid names."
indicating that the name change cannot be made. I checked the Name Manager to confirm the only two names in the workbook are "cat" and "NameList" and there are no names with errors. Then I checked for hidden names using a name manager from Jan Karel Pieterse, and found none.

Now here's what mystifies me, I changed "dog" to "pig" in the NameList Sheet2!B4 and ran your code again and Bingo "cat" has been replaced by "pig". Excel is fun(?) - go figure!

 

[Jerry Sullivan]

That's interesting. I'm away from my computer - does excel allow you to create a new name "dog". Could be a reserved word???

With excel 2007+ you cant use dog1 because it could be address $DOG$1, but dog with no number should be ok.

 

[JoeMo]

I finally sorted it out. I had pre-loaded two cells in Sheet1 (where the named range "cat" exists in A1) with formulas:
one cell was =cat+5, the second was =dog+2. The intent was to see if #NAME? error in the second cell vanished after the name change code ran as a quick indicator that the change had taken effect. Apparently, Excel guards against changing a name referenced in a formula to another name referenced in a formula, even if the latter name doesn't exist in the Name Manager and the formula returns a #NAME? error. You can verify this by opening the Name Manager, selecting "cat" and then Edit, and try to enter "dog" (when both "cat" & "dog" are contained in formulas). Excel responds with a pop-up indicating that the change is not allowed. A good protective measure - restores my faith in Excel.

 

[Jerry Sullivan]

Ahhhh... that's a new one to me. Thanks for sharing that discovery, Joe.

 

[SydneyGeek]

Yes, thanks.
I wasn't aware of that either.

Denis

 

[APandainFjord]

Hi Denis,

I have been searching for months for a solution like this... I was really happy to find your macro... However, I installed it and ran it and nothing happened... Could you help to see why...

I have an Excel file with around 20 sheets and each of them have ranges named as EMBLEMFac1, EMBLEMFac2, EMBLEMFac3... I added a new sheet caleld Sheet1, in A1 to B3, I put EMBLEMFac1, EMBLEMFac2, EMBLEMFac3 in column A and cat, dog, pig in column B. And named this area NameList. I opened macro and put your codes (barely changed Sheet2 to Sheet1) into VBAProject - Microsoft Excel Objects - ThisWorkbook and ran it ... nothing happened...

I also tried to put your codes into Modules- Module 1, also, nothing happened...

Could you help to see why? Thanks in advance!

 

[SydneyGeek]

Hi, this code belongs in a standard module like you did the second time. Places like Sheet1 or ThisWorkbook are generally a bad place to put general VBA code.

When you put the code into Module1 what did you do then? And could you paste the modified code please, so that we can see it? There may be something obvious that needs correcting.

Note that when you paste the code into a reply, it's best to select all the code and press the # symbol in the toolbar in the Quick Reply window. This will keep the indneting on the code, and make is easier to read.

Denis

 

[APandainFjord]

Hi Denis,

Thank you very much for help!
The codes I used are exactly the same as you put there... except I changed sheet name from Sheet 2 to Sheet 1... and Excel gave some extra lines like this:

Public Function NameExists(ByVal sName As String) As Boolean
    On Error Resume Next
    NameExists = ActiveWorkbook.Names(sName).Name = sName
End Function

When I used the codes friom another person here who slightly modified your codes, I got error message that my range name was not found! I was surprised because obviously the range names are there and I used copy and paste, so there couldn't be any spelling mistake...and when I tried to define more ranges, it worked on the new range I defined!

I am totally confused... Is it possible that you pm me your email address and I send the file to you?

Thanks a lot in advance.

Hi, this code belongs in a standard module like you did the second time. Places like Sheet1 or ThisWorkbook are generally a bad place to put general VBA code.

When you put the code into Module1 what did you do then? And could you paste the modified code please, so that we can see it? There may be something obvious that needs correcting.

Note that when you paste the code into a reply, it's best to select all the code and press the # symbol in the toolbar in the Quick Reply window. This will keep the indneting on the code, and make is easier to read.

Denis

 

[Bnagel07]

Im trying to use your macro to change my named ranges names in the Name Manager.

Created a list, 2 columns and named that range as NameList.
Column A = Current Named Ranges Names
Column B = New Named Ranges Names

Changed code to reference Sheet10, thats where my list is.
Ran Macro, No changes in the Name Manager.

No error or compile messages. Mouse icon changes to the hour glass for a few seconds and returns to normal.

Any idea what I might be doing wrong?