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 = "_" &amp; 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.
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