This Excel VBA function was originally the subject of a previous question on Code Review. This function (Namify()) takes a string as input, modifies it to make it valid for use as a range name, and then returns the valid string. I've revised the function based on some excellent input from Mat's Mug and RubberDuck and I feel it's about as rigorous as I can make it.
In the previous question AlexR raised the point that a VBScript.RegExp could greatly streamline the function, however I'm using Excel for Mac 2011 which doesn't natively support VB regular expressions. You should check out his post if you'd like to clean up the function for yourself.
What I'm asking now is: can you break its functionality?
TLDR? You can download an .xlsm file here for testing the function, or to see all the code in one piece if you don't want read this lengthy post in its entirety.
Intended scope of Namify():
My vision is for Namify() to serve as a tool that can be drawn upon by a subroutine handling the naming of Excel objects. The Sub would source user input (or some other source) to get a desired or initial name, and then pass that name to Namify() to make it legal and optionally verify that it isn't already in use or increment it to make it unique if it is in use. The way I see it, how the name is applied to objects (overwrite name/reference, add name, etc...) is beyond the scope of Namify(), but rather is within the scope of the the Sub that calls Namify().
Overview of revisions to Namify():
- Converts (some) unicode accented characters to standard equivalent.
- Validation of characters now done by
InStr()instead ofMatch(). - No longer removes periods and backslashes from names.
- Invalid characters removed from name instead of being replaced with underscores.
- Throws the user a message if the modified string is longer than 255 characters and exits.
- Prefixes with underscore if string begins with a number OR a period.
- Two optional arguments check in case-insensitive manner if the modified name already exists in the workbook. If it does, a warning is displayed to the user or a numerical increment is added.
The helper function below was added to the module to convert accented unicode characters to their standard equivalent. This prevent the accented characters from being omitted from the name which preserves its intended form and improves readability. My version of the VB editor doesn't handle all unicode characters, so this helper function is by no means as robust as it could be. It does at least convert the most frequently encountered accented characters. I found this helper function here. If an illegal character is not converted by this function, it's just removed from the name string and life goes on.
Private Function StripAccent(ByVal instring As String)
'Credit: http://www.extendoffice.com/documents/excel/707-excel-replace-accented-characters.html#a1
Dim A As String
Dim B As String
Dim i As Integer
Const AccChars = "ŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÑÒÓÔÕÖÙÚÛÜàáâãäåçèéêëìíîïñòóôõöùúûüÿ"
Const RegChars = "YAAAAAACEEEEIIIINOOOOOUUUUaaaaaaceeeeiiiinooooouuuuy"
For i = 1 To Len(AccChars)
A = Mid(AccChars, i, 1)
B = Mid(RegChars, i, 1)
instring = Replace(instring, A, B)
Next
StripAccent = instring
End Function
The Namify function now accepts two optional boolean arguments, MakeUnique and Verify. MakeUnique numerically increments the name if it already exists in the workbook and Verify displays an error if the name exists. MakeUnique supersedes Verify.
Private Function Namify(ByVal inputName As String, _
Optional ByVal MakeUnique As Boolean = False, _
Optional ByVal Verify As Boolean = False) As String
'Takes a string argument and returns a modified string suitable for use as a name
'
'Usage: When calling Namify() from a subroutine, use a handler like the example
' shown below to handle errors Namify() is likely to produce.
'
' On Error GoTo Handler
' Selection.Name = Namify(Range("nametouse"), True)
' Exit Sub
'Handler:
' If Err.Number = 3000 Or Err.Number = 17 Then
' ElseIf Err.Number = 1004 Then
' Selection.Name = Namify("_" & Range("nametouse"), True)
' Else
' MsgBox ("Error Number " & Str(Err.Number))
' End If
The first thing Namify() does is replace accented characters in inputName with their standard equivalents using the helper function StripAccent() and stores the modified string in the variable workingName. Then, it checks each character in workingName and removes it if it isn't legal for use in an Excel name.
'***Begin function body***
'Create a string to be modified and returned
Dim workingName As String
workingName = StripAccent(inputName)
'Create a string array containing all the valid characters in an Excel name
Dim validchars As String
validchars = "1234567890abcdefghijklmnopqrstuvwxyz" & _
"_\.ABCDEFGHIJKLMNOPQRSTUVWXYZ"
'Iterate through each character of the string argument and remove it if it's an illegal character.
Dim i As Long
For i = 1 To Len(inputName)
'Is the character illegal?
If (InStr(1, validchars, Mid(inputName, i, 1), 1) < 1) Then
'Yes, replace it.
workingName = Replace(workingName, Mid(inputName, i, 1), vbNullString)
End If
Next i
The next section handles several cases in which an illegal name could be provided even if all its characters are legal. My solution is to prefix such names with an underscore character.
'If the first character is a number or period, prefix workingName with an underscore.
Dim invalidfirsts As String
invalidfirsts = ".1234567890"
If InStr(invalidfirsts, Left(workingName, 1)) > 0 Then
workingName = "_" & workingName
End If
'If workingName is a single character and one of "C", "c", "R", "r", prefix with underscore.
Dim invalidsingles As String
invalidsingles = "CcRr"
If (Len(workingName) = 1) And (InStr(invalidsingles, workingName) > 0) Then
workingName = "_" & workingName
End If
With all the simple modifications out of the way, Namify() checks if the name already exists in this workbook if either the Verify or MakeUnique arguments are passed as True. The first if statement below explicitly supersedes Verify if MakeUnique is True. Without this if statement, Verify still shouldn't have an impact if MakeUnique is True, but in the previous question Mat's Mug pointed out that it's best practise to be as explicit as possible. I assume that applies to this situation as well. The optional third argument of StrComp() is set to a value of 1 so that names are compared in a case-insensitive manner. Since Excel does not evaluate case when naming objects, this will prevent overwriting equivalent names that differ only in case. If the modified name does not already exist, the function skips the EndVerify handler so that name incrementation is not executed when MakeUnique = True.
'MakeUnique argument supersedes Verify argument
If MakeUnique Then
Verify = False
End If
'If one of the optional arguments MakeUnique or VerifyName is True, check if workingName is already being used.
If MakeUnique Or Verify Then
'***Is workingName already a name in the workbook?***
'Create variable to keep track of whether name is already used
Dim isused As Boolean
isused = False
'create object to hold all name objects in the active workbook
Dim wbnames As Variant
Set wbnames = ActiveWorkbook.Names
'find out if workingName is already used in the active workbook (case-insensitive)
Dim nameindex As Long
For nameindex = 1 To wbnames.Count
If StrComp(wbnames(nameindex).Name, workingName, 1) = 0 Then
isused = True
End If
Next nameindex
'If workingName isn't used, the rest of this block can be skipped
If Not isused Then GoTo EndVerify
'If Verify is true, but MakeUnique isn't, warn the user if the name already exists.
If Verify And Not MakeUnique And isused Then
MsgBox ("This name already exists in this workbook." & vbNewLine & vbNewLine & _
"Choose another name.")
Error (17)
Exit Function
End If
When MakeUnique is passed as True and the modified name already exists (isused = True), the modified name will be postfixed with __#, where # is incremented until the name is unique. Once an appropriate postfix is found, it is appended to workingName.
'***What number can be appended to workingName to make it unique?***
'Create variables for incrementation and detection of incremented workingName
'in active workbook
Dim increment As Long
increment = 1
Dim incrementused As Boolean
'While a unique workingName has not been found, increment a postfix for workingName.
Do While isused
'initialize value of incrementused as False to escape loop when no matching name found.
incrementused = False
'check incremented workingName for a case-insensitive match in existing workbook names
'if a match is found, the value of incrementused will be set to True
For nameindex = 1 To wbnames.Count
If StrComp(wbnames(nameindex).Name, (workingName & "__" & increment), 1) = 0 Then
incrementused = True
End If
Next nameindex
'If the incremented name was used, increment by 1. Else, escape loop.
If incrementused Then
increment = increment + 1
Else
isused = False
End If
Loop
'Set workingName to the unique postfix-incremented workingName
workingName = Namify(workingName & "__" & Str(increment), True)
End If
'Goes to here if workingName was unique
EndVerify:
The last thing to check is that the modified name does not violate the 255-character limit for object names. If it does, a warning message is displayed to the user and the function throws an error. If this length requirement is satisfied, the function returns modified workingName.
'Make sure the name is <= 255 characters
If Len(workingName) > 255 Then
MsgBox ("Choose a shorter name." & vbNewLine & vbNewLine & _
"Names may contain up to 255 characters.")
Error (3000)
Exit Function
End If
'return the string that is now legal to use as a name.
Namify = workingName
End Function
To provide an example of how I would call Namify() from a subroutine, I've included the following. One of the sore points for me with this function is that if Namify() tries to pass a cell reference as a name, I had to resort to handling it outside of the function itself. Error 1004 is thrown when the Sub tries to assign a valid cell reference as a name, so I detect this error within the Sub and call Namify() again on an underscore-prefixed version of the desired name. I feel that this should be handled within the function by Namify()'s optional arguments MakeUnique and Verify, but I couldn't figure out how to test assigning the modified name to a dummy object. I certainly do not want to risk overwriting another name by testing it on a cell. Furthermore, if Error 1004 is thrown for another reason, this solution may not work well in that circumstance.
When the name is too long or when it already exists and MakeUnique = False, Verify = True, Errors 3000 or 17 are thrown. The case for them in the handler just prevents the user from having to see an error dialog other than the one called by Namify().
Sub ApplyName()
'Takes the value of a cell ("namecell") in which the user has entered a desired name and
'applies it as the name of the selected range.
On Error GoTo Handler
Selection.Name = Namify(Range("nametouse"), True)
Exit Sub
Handler:
If Err.Number = 3000 Or Err.Number = 17 Then
ElseIf Err.Number = 1004 Then
Selection.Name = Namify("_" & Range("nametouse"), True)
Else
MsgBox ("Error Number " & Str(Err.Number))
End If
End Sub
Please post an answer or comment if you can think of further improvements to this function or test cases that break it's functionality. And, by all means, go ahead and use the function however you like if it would be useful to you.
Likestatement? \$\endgroup\$If (InStr(1, validchars, Mid(inputName, i, 1), 1) < 1) ThenwithIf Mid(inputName, i, 1) Like "[!_\.0-9a-zA-Z]" Thenand do away with thevalidcharsstring. This may or may not execute faster; I wouldn't know. \$\endgroup\$