Function to return a string legal for a range name

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 mac

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

StackExchange Code Review Q#119091, answer score: 5