I know a good thing when I see it. Dick’s use of the word Redux in yesterday’s post for one. Dick’s nifty CreateDynamicNames() sub for another. Put them together, and you’ve got a winning combination. Or at least, 5 minutes of distraction between whatever the heck it is you’re supposed to be doing instead of reading this post. Work, most likely.
Yesterday I posted a barely fleshed out bit of code that took some of the tedium out of maintaining Named Ranges. Mostly it saved you scrolling through a potentially overwhelming list of Names in order to find the one you want to maintain or zap:
Today, we’re going to turn yesterday’s rather underwhelming interface:
…into this slightly less underwhelming interface:
Our first step is to put some code in the Personal Macro Workbook to add those additional right-click items to the right-click menus – aka context menus – that we’re likely to use in relation to the grid. Three of these menus spring to mind:
- The “List Range PopUp” CommandBar that you see if you right click over a Table.
- The “PivotTable Context Menu” CommandBar that you see if you right click over a PivotTable.
- The “Cell” CommandBar that you’re likely to see if you right click over a range that’s not a Table or a PivotTable.
There’s probably more. Let me know in the comments if you can think of others relevant to this post.
Name your poison
Here’s the code I use to add the shortcuts:
Sub AddShortcuts()
Dim cbr As CommandBar
Dim i As Long
DeleteShortcuts
For i = 1 To 3
Select Case i
Case 1: Set cbr = Application.CommandBars("Cell")
Case 2: Set cbr = Application.CommandBars("List Range PopUp")
Case 3: Set cbr = Application.CommandBars("PivotTable Context Menu")
End Select
'Add Stand-alone buttons for Duplicate/Delete resource subs
With cbr.Controls.Add(Type:=msoControlButton, Temporary:=True)
.Caption = Chr(Asc("&")) + "Rename Selected Named Range"
.Tag = "RenameName"
.OnAction = "RenameName"
.Style = msoButtonIconAndCaption
.Picture = Application.CommandBars.GetImageMso("NameDefine", 16, 16)
.BeginGroup = True
End With
With cbr.Controls.Add(Type:=msoControlButton, Temporary:=True)
.Caption = Chr(Asc("&")) + "Point Selected Named Range Elsewhere"
.Tag = "RepointName"
.OnAction = "RepointName"
.Style = msoButtonIconAndCaption
.Picture = Application.CommandBars.GetImageMso("ArrangeByAppointmentStart", 16, 16)
End With
With cbr.Controls.Add(Type:=msoControlButton, Temporary:=True)
.Caption = Chr(Asc("&")) + "Zap the Selected Named Range"
.Tag = "DeleteName"
.OnAction = "DeleteName"
.Style = msoButtonIconAndCaption
.Picture = Application.CommandBars.GetImageMso("DeleteTable", 16, 16)
End With
With cbr.Controls.Add(Type:=msoControlButton, Temporary:=True)
.Caption = Chr(Asc("&")) + "Lightning fast Dynamic Ranges!"
.Tag = "DynamicRanges"
.OnAction = "CreateDynamicNames"
.Style = msoButtonIconAndCaption
.Picture = Application.CommandBars.GetImageMso("UMLEvents", 16, 16)
End With
Next
End Sub
Here’s the code I use to delete ’em:
Sub DeleteShortcuts()
Dim cbr As CommandBar
Dim ctrl As CommandBarControl
Dim i As Long
For i = 1 To 3
Select Case i
Case 1: Set cbr = Application.CommandBars("Cell")
Case 2: Set cbr = Application.CommandBars("List Range PopUp")
Case 3: Set cbr = Application.CommandBars("PivotTable Context Menu")
End Select
' Delete the custom controls with the Tag : My_Cell_Control_Tag.
For Each ctrl In cbr.Controls
Select Case ctrl.Tag
Case "RenameName", "RepointName", "DeleteName", "DynamicRanges"
ctrl.Delete
End Select
Next ctrl
Next i
End Sub
That Chr(Asc(“&”)) + stuff in that first routine sets the accelerator keys, so that all you musophobes don’t have to obsessively wash your hands each time you use these. Instead, you can use the menu key:
…and then hit the R, P, Z, or L keys accordingly.
I was going to try to spell something rude with these, but it was like playing Scrabble against Microsoft, who already took all the best letters. Cheats!
(Aside: There’s a good discussion over at Chandoo’s blog about the menu key, and what to do if some tight-wad manufacturer hasn’t put it on their machines.)
These shortcuts get added when Excel starts/closes courtesy of the Workbook_Open/Workbook_Close events in the ThisWorkbook module in my Personal Macro Workbook:
Private Sub Workbook_Open()
AddShortcuts
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteShortcuts
End Sub
So that sets the stage. Next, we need some actual routines to do something when we select from those right-click menus.
Hi. My name is…
(what?) My name is… (who?)
My name is… [scratches] Slim Shady
Here’s the main function: a routine that returns a delimited string containing the names of any names that reference your reference:
Function IdentifyNames(rng As Range) As String
'Identifies any Named Ranges that map directly to rng
Dim nm As Name
Dim strNames As String
For Each nm In ActiveWorkbook.Names
On Error Resume Next
If nm.RefersToRange.Address(External:=True) = rng.Address(External:=True) Then
If Err.Number = 0 Then strNames = strNames & nm.Name & "|"
End If
On Error GoTo 0
Next
IdentifyNames = strNames
End Function
And here’s my three functions to Repoint, Rename, or completely ‘Reck those names:
Sub RepointName()
Dim nm As Name
Dim strNames As String
Dim rngNew As Range
Dim rngExisting As Range
Dim lngNames As Long
Dim strMessage As String
Dim strMultipleNames As String
Dim i As Long
Set rngExisting = Selection
strNames = IdentifyNames(rngExisting)
lngNames = UBound(Split(strNames, "|"))
If lngNames = -1 Then
'There is no named range that matches. So let the user choose one.
Application.Dialogs(xlDialogNameManager).Show
Else:
For i = 0 To lngNames - 1
Set nm = ActiveWorkbook.Names(Split(strNames, "|")(i))
If lngNames > 1 Then
strMultipleNames = "I found " & lngNames & " Named Ranges that reference your selection, "
strMultipleNames = strMultipleNames & "so we 'll go through them one by one."
strMultipleNames = strMultipleNames & vbNewLine & vbNewLine
strMultipleNames = strMultipleNames & "Name " & i + 1 & " of " & lngNames & ":"
strMultipleNames = strMultipleNames & vbNewLine & vbNewLine
End If
On Error Resume Next
Set rngNew = Application.InputBox( _
Title:="Please select new range", _
Prompt:=strMultipleNames & "Select the range where you want """ & nm.Name & """ to point at.", _
Default:=Selection.Address, _
Type:=8)
On Error GoTo 0
If Not rngNew Is Nothing Then
nm.RefersTo = "='" & ActiveSheet.Name & "'!" & rngNew.Address
rngNew.Select
End If
Next i
End If
End Sub
Sub RenameName()
Dim nm As Name
Dim strNames As String
Dim nmExists As Name
Dim strRefersTo As String
Dim strMultipleNames As String
Dim strNew As String
Dim rng As Range
Dim lngNames As Long
Dim i As Long
Set rng = Selection
strNames = IdentifyNames(rng)
lngNames = UBound(Split(strNames, "|"))
If lngNames = -1 Then
'There is no named range that matches. So let the user choose one.
Application.Dialogs(xlDialogNameManager).Show
Else:
For i = 0 To lngNames - 1
Set nm = ActiveWorkbook.Names(Split(strNames, "|")(i))
If lngNames > 1 Then
strMultipleNames = "I found " & lngNames & " Named Ranges that reference your selection, "
strMultipleNames = strMultipleNames & "so we 'll go through them one by one."
strMultipleNames = strMultipleNames & vbNewLine & vbNewLine
strMultipleNames = strMultipleNames & "Name " & i + 1 & " of " & lngNames & ":"
strMultipleNames = strMultipleNames & vbNewLine
End If
On Error Resume Next
strNew = Application.InputBox( _
Title:="Please input the new name...", _
Prompt:=strMultipleNames & "Please type the new name for """ & nm.Name & """.", _
Default:=nm.Name, _
Type:=2)
If strNew = "False" Then Exit Sub
If Not strNew = nm.Name Then
strNew = Fix_Name(strNew)
On Error Resume Next
Set nmExists = ActiveWorkbook.Names(strNew)
On Error GoTo 0
If nmExists Is Nothing Then
nm.Name:=strNew
Else:
MsgBox "That name already exists. Please choose another."
Set nmExists = Nothing
End If
End If
Next
End If
End Sub
Sub DeleteName()
Dim nm As Name
Dim strNames As String
Dim strMessage As String
Dim iResponse As Integer
Dim rngExisting As Range
Dim lngNames As Long
Dim i As Long
strNames = IdentifyNames(Selection)
lngNames = UBound(Split(strNames, "|"))
Select Case lngNames
Case -1:
'There is no named range that matches. So let the user choose one.
Application.Dialogs(xlDialogNameManager).Show
Case 1: ActiveWorkbook.Names(Split(strNames, "|")(0)).Delete
Case Else:
For i = 0 To lngNames - 1
Set nm = ActiveWorkbook.Names(Split(strNames, "|")(i))
strMessage = "I found " & lngNames & " Named Ranges that reference your selection, "
strMessage = strMessage & "so we 'll go through them one by one."
strMessage = strMessage & vbNewLine & vbNewLine
strMessage = strMessage & "Name " & i + 1 & " of " & lngNames & ":"
strMessage = strMessage & vbNewLine
strMessage = strMessage & "Do you want to delete the Named Range """ & nm.Name & """?"
iResponse = MsgBox( _
Title:="Multiple Names Found", _
Prompt:=strMessage, _
Buttons:=vbYesNoCancel + vbQuestion)
Select Case iResponse
Case vbYes: nm.Delete
Case vbNo: 'do nothing
Case vbCancel: Exit Sub
End Select
Next i
End Select
End Sub
These subs are all fairly intelligent in that they handle the case where just one Named Range resolves to the selection:
…as well as multiple Named Ranges resolving to the selected range:
So given this code is supposed to do something to Named Ranges that point at the current selection, what happens if someone runs it on a range that no names point at? Glad I asked. It brings up a the inbuilt ‘Name Manager’, in case the user does actually want to do something with a name, but forgot to select the range it resolves to:
Lastly, here’s Dick’s code that I shamelessly lifted, that lets you create lots of Dynamic Named Ranges from your current selection, in response to a question from GMF. (Now that is a strange name!)
Sub CreateDynamicNames()
Dim rCell As Range
Dim sCol As String
Dim sPrefix As String
Dim strPrompt As String
If TypeName(Selection) = "Range" Then
strPrompt = "I'll use the headings in the top row to name each range." & vbNewLine & vbNewLine
strPrompt = strPrompt & "OPTIONAL: You can enter a prefix below if you want, and I'll use it to prefix each Named Range with." & vbNewLine & vbNewLine
strPrompt = strPrompt & "Otherwise just push OK, and I'll use the headings as is."
sPrefix = Application.InputBox( _
Title:="Please input a prefix if you want one...", _
Prompt:=strPrompt, _
Type:=2)
If sPrefix = "False" Then Exit Sub
For Each rCell In Selection.Rows(1).Cells
If rCell.Value <> "" Then ActiveWorkbook.Names.Add Fix_Name(sPrefix & rCell.Value), "='" & rCell.Parent.Name & "'!" & rCell.Offset(1).Address & ":INDEX('" & rCell.Parent.Name & "'!" & rCell.EntireColumn.Address & ",COUNTA('" & rCell.Parent.Name & "'!" & rCell.EntireColumn.Address & "))"
Next rCell
End If
End Sub
This code is a real timesaver. Simply select a range that looks like this:
…select this from the Right Click menu:
…add a prefix if you want:
…and next time you open NameManager, you’ll see those names are all good to go:
Pure magic, Dick.
And lastly, here’s Craig Hatmaker’s function I use to clean names:
Public Function Fix_Name(sName As String) As String
' Description:Conforms a string so it can be used as a name
' Parameters: sName String to be conformed
' Example: sColumnName = Fix_Name("1st deposit %")
' Date Ini Modification
' 11/02/10 CWH Initial Programming
' 11/20/10 CWH Used "Like" operator
'If Not DebugMode Then On Error GoTo ErrHandler
Fix_Name = sName
Dim i As Integer
'Substitute special invalid characters w/standard abbreviations
sName = Replace(sName, "#", "_NUM")
sName = Replace(sName, "$", "_AMT")
sName = Replace(sName, "%", "_PCT")
sName = Replace(sName, "-", ".")
sName = Replace(sName, ",", "-")
sName = Replace(sName, " ", "_")
'Get rid of all other illegal characters
i = 1
Do While i <= Len(sName)
If Not Mid(sName, i, 1) Like "[A-Z,a-z,0-9,.,_,\]" Then _
sName = Left(sName, i - 1) & Right(sName, Len(sName) - i)
i = i + 1
Loop
'First Character cannot be numeric & result cannot look like cell ref.
If IsNumeric(Left(sName, 1)) Or sName Like "[A-Z]#" Then _
sName = "_" & sName
Fix_Name = sName
ErrHandler:
If Err.Number <> 0 Then MsgBox _
"Fix_Name - Error#" & Err.Number & vbCrLf & _
Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
On Error GoTo 0
End Function
All this is in the attached file, along with some names for you to try it out on. Adjust Named Ranges_20140801
Name-dropper!
In terms of how the above code works, you can find a very good introduction on Ron de Bruin’s site, and you’ll likely learn a lot by poking around Doug Glancy’s site and in the VBA in his MenuRighter and FaceIdViewer addins.
We prefer the term ‘Mavisphile’.
Who’s Mavis?
You guys didn’t have Mavis Beacon Teaches Typing down there? OK it was a bit of a stretch but keyboardphile wasn’t clever enough.
I’m quite the keyboardphile myself…not only do I touch type, but I do it using the Dvorak keyboard layout. Querty is like sooo last century.
Thanks for dropping my name (but not dragging it through the mud).
I’ve been fooling around a lot with names lately. If I had 1/7th your energy, I’d get a post together.
Here’s another way you could test if a name refers to a specific range, using the wonderful Name.RefersToRange property:
If nm.RefersToRange.Address(External:=True) = rng.Address(External:=True) ThenSadly, it will fail if the name doesn't refer to a range, so you'd have to test for that. (On the other hand the one you're using will fail if there's an equal sign in the sheet name. It's always something.)
I have a fancy named range maker too, complete with UserForm and choices for prefix, illegal char replacement and dynamic vs. static. It's been relegated to a lonesome corner of my utility addin though, because now I just use tables instead.
Oh, and I should mention that Name.RefersToRange is a read-only property. Took me quite a while to figure that out the first time :).
Doug, if someone like me drops your name, it is getting dragged through the mud. If someone like Mike Alexander mentions your name, you’ll need to lawyer up.
Thanks for the post, saving this one away. :)
Looking good! One final thought… adding a scope parameter to allow the user to add or move a range from workbook to sheet and vice versa. That always drives me nuts that the scope is greyed out when you want to edit the range. I like to use named ranges for XY common elements of charts (such as a line showing the current date if you have an XY chart showing progress) and sometimes they have to be defined at the sheet level. I haven’t played around with the macro to know if you have to delete the range at the previous scope level and re-enter at the new or if it’s just a quick property change in VBA.
GMF: Another good idea. Please hold, caller…
@GMF: Forgot to mention that Jan Karel Pieterse’s Name Manager will let you do that. But I agree…would be handy to have this available from the right click option.
@Luke. Hi buddy. Sorry, haven’t been around the forum much recently. Hope all is well with you.
Good gracious, what makes you think I have the time or expertise to know about Jan Karel Pieterse’s products, let alone the plethora of other Excel sites. If you, Chandoo, Oscar, or Peltier don’t have it, is it truly worth knowing?
Yep. JK’s Name Manager belongs at the top of Maslow’s Hierarchy of Needs! Download it, and check it out. It’s truly awesome.
@Doug: I implemented your suggested code change. Thanks for the pointer. I also made a small change to RenameName…for some reason I was deleting the name and then adding a new one. Which breaks any references to the old name. Now I just set nm.Name = strNew and things work as expected.
mean
@GMF: It’s actually pretty complicated to change the scope of a name, because you need to delete it and then add another one, and doing this means that any formulas or other names that reference the name will return a #NAME? error. So the code has got to also go through the workbook and Names collections and replace any instance of the old name with the new name.
To do this, the code would first have to encase the Name in something like the Pipe symbol, to handle the case where the Name would otherwise appear as part of a substring in a formula or sheetname or another name – which is likely to be the case in the event of a poor choice of name such as Date, given there are also functions that contain the substring Date in them. THen it could do a Find and Replace in sheets other than the one where the name is scoped to. (No need to change them in the local sheet, and this would in fact likely go against what the programmer is actually wanting to do in using a local name in the first place).
I’ll have a crack at this as time permits. I imagine Jan Karel takes this approach in his NameManager addin.
Note that if you use Jan Karel’s Name Manager, and you want to change a global name to local AND that global name is curretly referenced by formulas on other sheets, then after you change the name to Local those formulas in those other sheets will return a #NAME? error, because they don’t have the requisite sheet prefix in front of them that allows you to point to a local name on another sheet.
The way around this Jan Karel informs me is instead of using the ‘Convert to Local’ button, instead use the Rename option and simply append the existing name with the SheetName of the sheet that you want to make the currently global name local to.
Hard to explain. Even harder to comprehend, I’ll bet. :-)
As long as we’re on the subject of names, how ’bout a separate blog post on when to use them and when not, limits on array in names, etc.
For example, some time ago you had a guest post on building circular org charts using XY values. I loved the concept and, per the post, used named ranges for several of the trigonometric functions for creating the circles:
Theta =(ROW(INDIRECT(“1:361”))-1)*PI()/180
XVal = _CIR1*COS(Graphs!Theta)
YVal = _CIR1*COS(Graphs!Theta)
And so on. Most of the time it works great, and I’ve added more circles and charts as I create various type of images, but every so often Excel crashes completely and I have reboot, open and save the worksheet to an xlsb, then save it back to an xlsx to make it work again. Is there a limit to arrays in named ranges?
Another example: I use XY charts for schedules and I like to have a vertical line showing today’s date. I do this so frequently I have a named range for each of the X and Y values. To get the current date to show up as 2 similar X values the best I could come up with is:
=(ROW(INDIRECT(“1:2”))/ROW(INDIRECT(“1:2”)))*TODAY()
There must be a better way, but I can’t just put ={TODAY(),TODAY()} as a value and I’m curious why.
Yet another curiosity: INDIRECT won’t work with dynamic ranges (which I almost always name). Example: Validation List 1 (named ProduceList) chooses between Fruits and Vegetables, each of which represent a named range. Validation List 2 will display the choices in (say) Fruits by using =INDIRECT(ProduceList). But ProduceList can’t be a dynamic range because INDIRECT will throw an error.
Not that I expect your site to substitute for MSDN… I’d just like an explanation in layman’s terms . And I don’t subscribe to MSDN – should I, even though I’m just a director trying to get the right numbers in front of my organization and not a developer?
Every so often Excel crashes completely and I have reboot, open and save the worksheet to an xlsb, then save it back to an xlsx to make it work again. Is there a limit to arrays in named ranges?
If there is, I don’t come across it. And I leverage off of Arrays in Named Ranges a heck of a lot. Might need to rebuild that workbook from scratch. Or refactor your arrays so they don’t refer to entire columns/rows.
There must be a better way, but I can’t just put ={TODAY(),TODAY()} as a value and I’m curious why.
Yes, there is a better way. Blogpost on the way.
INDIRECT won’t work with dynamic ranges (which I almost always name)
I wrote a post recently over at Chandoo that give alternate approaches, plus also ensure that you don’t have invalid downstream choices by subsequently changing upstream choices.
Dynamic (Cascading) Dropdowns that reset on change: http://chandoo.org/wp/2014/02/13/dynamic-cascading-dropdowns-that-reset/
Robust Dynamic (Cascading) Dropdowns Without VBA: http://chandoo.org/wp/2014/02/25/robust-dynamic-cascading-dropdowns-without-vba/