Function to return a legal name for an Excel range: Follow-up

Question

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 of Match().
• 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.

• RubberDuck: I understand that Mac doesn't support the RegExp library, but are you sure it doesn't support the built in pattern matching using the Like statement?
• Jeremy Caron: You're correct, it does, and I wish i looked into it sooner as it was mentioned in the post to which AlexR linked in the original question. In the third code block of this question, I can replace If (InStr(1, validchars, Mid(inputName, i, 1), 1) < 1) Then with If Mid(inputName, i, 1) Like "[!_\.0-9a-zA-Z]" Then and do away with the validchars string. This may or may not execute faster; I wouldn't know.

Answer 1

Standard response - use Option Explicit to catch any variable issues of which there are none! Good job!

Overall I can't say much about the method, but I can talk about variables

I'm not entirely sold on your naming of variables:

• validchars - validChars
• i - I'll give you i, But I won't give you a or B - why is one lower and one upper?
• invalidfirsts - Const invalidFirsts as String = ".234567890"
• invalidsingles - Const invalidSingles as String = "CcRr"
• isused - Is used? maybe inUse
• wbnames - WorkbookName, or better yet, give the workbook a name in VBA to do away with this variables.
• nameindex - `nameIndex
• incrementused - incrementUsed`
• AccChars and RegChars are nitpicky - the first letter should be lowercase

I'm also no terribly sold on using Verify as a variable - maybe Option ByVal verifyName as Boolean. Using Verify might confused the reader as being an excel method or function.

And if I understand correctly MakeUnique will make name if used, into name1? That's not a great practice, you'll easily get confused by name1, name2, name3 - you'll constantly be opening the Name Manager. Perhaps prompt for a new name?

You have a lot of comments explaining what is happening, but the code should speak for itself. If you need to explain it, explain why you're doing it this way rather than that way:

'Make sure the name is <= 255 characters could be 'Excel doesn't allow names longer than 255 characters

By the way, your namify handles formulas pretty well.

How can you overwrite a name?

Defining a range TRUE twice throws error 450.

• Jeremy Caron: Thanks for the input @Raystafarian. I'm a super noob at programming, so these sorts of style tips are gold to me. If Namify() is passed a pre-existing name and MakeUnique is True, then the output is incremented following a double underscore such that name becomes name__1. This could be a problem if the user wants to implement their own incrementation scheme using double underscores, but I believe this is much less likely to be a problem than turning name into name1.
• Jeremy Caron: As for overwriting a name. I see this as being up to the subroutine that calls Namify(). All Namify() does is spit out a valid (and optionally, a unique) name, but you could have your Sub remove existing names from your target object before calling Namify(). Alternatively, a Sub that omits passing both optional arguments MakeUnique and Verify to Namify() or passes them both as False could overwrite the reference of an existing name if Namify() returns the existing name. Multiple names can refer to the same cell, but only the first one will be shown in the name box.
• Raystafarian: Ah, right - it's a private function. I was thinking it was a worksheet function, those are good points.
• Jeremy Caron: On an okay from you @Raystafarian, I'll remove the questions from your answer and edit my question to make sure the points you raised are clearly covered. After reviewing this post and this post, doing so would seem to keep this page within the preferred format for Stack Exchange. Cool?
• Jeremy Caron: Good catch on Error 450. It also gets thrown if you try to define a valid cell reference as the name of the same object twice. I don't understand at this point why the assignment statement throws Error 1004 on the first function call and Error 450 on subsequent calls. The quick and dirty fix: get the error handler in the sub to deal with it, like this: ElseIf Err.Number = 1004 Or Err.Number = 450 Then Selection.Name = Namify("_" & Range("nametouse"), True). If I can figure out a better solution, preferably one that's self contained within Namify(), I'll include it in a follow-up post.