Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Tuesday, September 22, 2015 4:08 PM
The Excel 2010 Name Manager (Ribbon -> Formulas -> Name Manager (Defined Names section)) does not open after clicking it.
After clicking the Name Manager icon, the cursor turns to the Tron Cycle Wheel of Perpetual Working, Thinking, and Waiting... After about 2 seconds the cursor returns to normal.
the workbook has many sheets with the sheet scoped names (we copied many sheets).
thoughts on how to re-enable the name manger?
All replies (5)
Thursday, September 24, 2015 7:06 AM âś…Answered
Hi Rogge,
Based on your description, first I suggest you try this code and check how many names are there in your Workbook.
Sub DeleteDeadNames2()
Dim nName As Name
Dim lCount As Long
With ActiveWorkbook
For lCount = .Names.Count To 1 Step -1
If lCount Mod 1000 = 0 Then
Debug.Print lCount
.Save
DoEvents
End If
If InStr(1, .Names(lCount).RefersTo, "#REF!") > 0 Then
.Names(lCount).Delete
End If
Next lCount
End With
End Sub
Then you need to check if there are any error about these names.
Now you can try to use this code to help you "clean" these names in your Workbook.
Sub DeleteDeadNames2()
Dim nName As Name
Dim lCount As Long
With ActiveWorkbook
For lCount = .Names.Count To 1 Step -1
If lCount Mod 1000 = 0 Then
Debug.Print lCount
.Save
DoEvents
End If
If InStr(1, .Names(lCount).RefersTo, "#REF!") > 0 Then
.Names(lCount).Delete
End If
Next lCount
End With
End Sub
This process runs very slow at first and gets progressively faster as the number of names is reduced.
Please Note: Since the web site is not hosted by Microsoft, the link may change without notice. Microsoft does not guarantee the accuracy of this information.
Please try this method and check if it works for you.
I'm glad to help and follow up your reply.
Regards,
Emi Zhang
TechNet Community Support
Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact [email protected].
Tuesday, September 22, 2015 7:28 PM
Use Jan Karel Pieterse's name manager (much better than the built-in one)
http://www.jkp-ads.com/officemarketplacenm-en.asp
Tuesday, September 22, 2015 8:36 PM
thanks, unfortunately we can't use 3rd party add-ons.
Wednesday, September 23, 2015 5:44 AM
This is the forum to discuss questions and feedback for Excel for Developers , I'll move your question to the MSDN forum for Word
https://social.technet.microsoft.com/Forums/office/en-US/home?forum=excel
The reason why we recommend posting appropriately is you will get the most qualified pool of respondents,
and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.
Thanks for your understanding.
Sunday, April 22, 2018 12:53 PM
For an issue that I had last night, I took a combined approach. Last night, I ran into an issue of exceeding 66,530 hyperlinks within a single worksheet. Therefore, I had to split that one worksheet into three worksheets. This in turn also meant my defined names also got messed up. Prior to even starting to work on the process, I had saved the file as a different name so as I would have a known good copy should the fix of the issue not work.
I didn't want to go with the straight forward delete (as suggested above) and not have the global range names properly replaced or edited. I learned that the RefersTo properly couldn't be edited directly via code, so I had to do a delete, then add. Also in the process of splitting, then limiting, it first created various local names (worksheet level range names), and then broke a lot of them too. In the end, it had created over 125,000 defined names in the workbook.
By using the code below, I got it down to 32,275 global range names with the links that's already in place now working properly again after having split the worksheets. As a side benefit, learned it now only takes about 40 seconds to save instead of the previously 78 seconds to save. I tested it out for some time, fixing as I did the testing. Once I was good with it, I then let it run and I went to bed as I knew it would take a while to run. I also felt tired too as it was past midnight once I was done with it, which I got up this morning at almost 7:30am just waking up on my own. I checked on it, and everything was good to go.
Also, by getting the defined names down below the 65,536 mark, it also prevents the possible situation of the workbook going corrupt with Dr. Watson coming up and removing everything except for data values, formulas, and default format during the opening of a workbook that has more than 65,536 defined names. That's an issue I ran into with Excel 2002 (aka Excel XP). I won't forget this one after that happened to me in 2005. I had to resort to one of my backup copies as I had utilized a total of 8 backup programs in addition to my primary data source (4 backups created by me in addition to me having created the primary source, 3 backups created by IT, and 1 backup created by MS). While there were overlaps among the 8 different backup programs, they each also covered something that none of the other backup programs covered.
Sub FixDeadNames()
Dim nName As Name
Dim lCount As Long
Dim l_strRangeName As String, l_strRefersToRange As String
Dim chkRange As Range
Dim areasName(2) As String
Dim chkCnt As Long, l_blnFixed As Boolean
Dim i As Integer
areasName(0) = "HolyBibleH!"
areasName(1) = "HolyBibleP!"
areasName(2) = "HolyBibleNT!"
With ActiveWorkbook
For lCount = .Names.Count To 1 Step -1
If lCount Mod 1000 = 0 Then
Debug.Print lCount
.Save
DoEvents
End If
If InStr(1, .Names(lCount).RefersTo, "#REF!") > 0 Then
l_blnFixed = False
l_strRangeName = ActiveWorkbook.Names(lCount).Name
'.Names(lCount).Delete
For i = 0 To 2
On Error Resume Next
If VBA.InStr(1, ActiveWorkbook.Names(areasName(i) & ActiveWorkbook.Names(lCount).Name).RefersTo, "#REF!") > 0 Then
chkCnt = 0
Else
chkCnt = 1
l_strRefersToRange = ActiveWorkbook.Names(areasName(i) & ActiveWorkbook.Names(lCount).Name).RefersTo
End If
On Error GoTo 0
If chkCnt <> 0 Then
ActiveWorkbook.Names(lCount).Delete
ThisWorkbook.Names.Add Name:=l_strRangeName, RefersTo:=l_strRefersToRange
l_blnFixed = True
On Error Resume Next
ActiveWorkbook.Names(areasName(i) & l_strRangeName).Delete
On Error GoTo 0
chkCnt = 0
Exit For
End If
Next i
If Not l_blnFixed Then
.Names(lCount).Delete
End If
End If
Next lCount
End With
End Sub