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
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
Overview of revisions to
- Converts (some) unicode accented characters to standard equivalent.
- Validation of characters now done by
- 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
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
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
MakeUnique is passed as
True and the modified name already exists (
isused = True), the modified name will be postfixed with
# is incremented until the name is unique. Once an appropriate postfix is found, it is appended to
'***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
'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
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
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.