Function to return a string legal for a range name

Question

I wrote an Excel VBA function that will take a string argument and return a string that is legal for naming a range. Upon looking through this, if you're wondering what I've been smoking, I should add a disclaimer that I'm new to VBA and I have not yet come across a less restrictive way to obtain this functionality. And, of course, please feel welcome to modify and use it for your own purposes.

Function Namify(inputName As String) As String
'Takes a string argument and returns a modified string suitable for use as a name

    'Create a string to be modified and returned
    Dim workingName As String
    workingName = inputName

    'Create a string array containing all the valid characters in an Excel name
    Dim validchars() As Variant
    validchars = Array("_", "1", "2", "3", "4", "5", "6", "7", "8", "9", "0", _
        "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", _
        "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z", _
        "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", _
        "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z")

    'Iterate through each character of the string argument and replace that character
    'with an underscore if it's an illegal character.
    Dim i As Long
    For i = 1 To Len(inputName)

        'Is the character illegal? If so replace it in workingName
        If IsError(Application.Match(Mid(inputName, i, 1), validchars, 0)) Then
            workingName = Replace(workingName, Mid(inputName, i, 1), "_")
        End If

    Next i

    'Detect whether the first character in workingName is a number.
    Dim numbers() As Variant
    numbers = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "0")

    If Not IsError(Application.Match(Mid(workingName, 1, 1), numbers, 0)) Then
        workingName = "_" & workingName
    End If

    'return the string that is now legal to use as a name.
    Namify = workingName

End Function

How I use it:

I have a macro that gets a text name from the user, instantiates a new object, and displays the user-input name in a cell followed by the object's property values in cells to the right. Then the sub calls Namify (user-selected name) to create a prefix for assigning a unique name to all these cells so their values can be updated with changes to the object's properties. The prefix itself is stored as a property of the object for later retrieval.

Some potential problems for this function (feel free to add to this list by editing or asking me to add in a comment):

• It could return a name that is also a valid cell address.
• It would return the same string from arguments with different illegal characters in the same position. (Example arguments: "User;Name", "User=Name")
• It does not return all possible arrangements of valid characters.
• It would return any of the one-character strings "C", "c", "R", "r"
• There is no limit on the length of the returned string

What I'd like to know:

• Is this a reasonable approach for allowing the user to create whatever name they like and then using a version of that name to name objects and ranges?
• What ideas do you have to make this function more rigorous or more useful?

Disclaimer: I accepted Mat's Mug's answer because it worked well for me. AlexR's answer does appear to be the ideal way to handle this, however VB regular expressions aren't natively supported on my version of Excel (for Mac 2011). Please have a look at his answer if you're interested in optimizing something like my function for yourself.

Follow Up: With some great answers on the board, I revised this function and posted it in a follow up question. Please have a look at it before posting additional answers here.

Answer 1

Nice code, I like what you've been smoking. An alternative (better?) name for the function could have been Legalize, too.

You could easily modify it to optionaly verify existing names (e.g. Optional ByVal verify As Boolean = False), and append a digit (or increment an existing one?) to the return value if verify is True and the resulting name already exists in ActiveWorkbook.Names.

Nitpick: inputName is implicitly passed ByRef, and should be passed ByVal to clearly say "I'm not modifying my input here" - with a ByRef parameter if your function reassigns the parameter, the caller gets to see the new value (and that would be... yuck!).

The function is also implicitly Public, which is much less of an issue, but still for readability's sake I like to see things explicit - especially since VBA's defaults (ByRef/Public) are pretty much upside down compared to most languages.

That said, I don't like the [ab]use of worksheet functions in VBA code. For example, this:

'Is the character illegal? If so replace it in workingName
If IsError(Application.Match(Mid(inputName, i, 1), validchars, 0)) Then

Should be using the InStr VBA function instead of Application.Match and IsError.

Then again this:

'Detect whether the first character in workingName is a number.
Dim numbers() As Variant
numbers = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "0")

If Not IsError(Application.Match(Mid(workingName, 1, 1), numbers, 0)) Then

Could be rewritten like this:

If Not IsNumeric(Left(workingName,1)) Then

---

You should also make sure that the resulting string is less than 255 characters, and... I think I'd replace invalid characters with vbNullString instead of an underscore.

The validChars array doesn't need to distinguish upper and lower case characters, so long as you specify vbTextCompare (default is vbBinaryCompare, which is indeed case-sensitive) when you use InStr to determine if a character is legal - a helper function like this can be tremendously helpful for this:

Public Function ContainsAny(ByVal string_source As String, ByVal caseSensitive As Boolean, ParamArray find_strings() As Variant) As Boolean

    Dim find As String, i As Integer, found As Boolean

    For i = LBound(find_strings) To UBound(find_strings)

        find = CStr(find_strings(i))
        found = Contains(string_source, find, caseSensitive)

        If found Then Exit For
    Next

    ContainsAny = found

End Function

With this function, you don't even need to declare the array anymore - you could just inline the valid characters into the call, like this:

If Not ContainsAny(Mid(inputName,i,1), False, "_", _
    "1", "2", "3", "4", "5", "6", "7", "8", "9", "0", _
    "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", _
    "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z") _
Then
    'character is illegal
End If

_{I wonder if there's a noticeable difference in performance between this approach and your possibly better-performing Match approach.}

Answer 2

Using a VBScript.RegExp will greatly help clean your algorithm:

Static reg As Object
If reg Is Nothing Then
    Set reg = CreateObject("VBScript.RegExp")
    reg.Pattern = "(?:^(?=\d)|\W)" 'A Digit at the start or any illegal character
    reg.Global = True 'Replace all matches
End If
Namify = reg.Replace(inputName, "_")

This does the same as your entire function. Read more about RegExps here

If you additionally write a function ContainsName(name As String) As Boolean wich traverses ActiveWorkbook.Names and returns True if name is a valid name, you can add an additional feature to generate a valid new name as Mat suggests:

Static trailingNum As Object
Dim number As String
If trailingNum Is Nothing Then
    Set trailingNumm = CreateObject("VBScript.RegExp")
    trailingNum.Pattern = "\d*$"
End If
While ContainsName(Namify)
    number = trailingNum.Execute(Namify)(0).Value
    If Len(number) = 0 Then number = "0"
    Namify = trailingNum.Replace(Namify, CStr(CInt(number) + 1))
End While