VBA Remove invalid range name characters

[Brettlb]

Hi, I have some code that sets named ranges based on sheet names defined by the user.

Issue is, sheet names allow characters that range names do no (i.e. #, - etc).

Is there a relatively simple way to clean a sheet name to allow it to be used as a range name.

Code example below. I am using Replace, but unsure how many characters are invalid. Unfortunately I am referencing a third party workbook, so no control over sheet names.

Thanks.

Sub test()

Dim sh As Worksheet, shnm As String, wb As Workbook
Set wb = ThisWorkbook

For Each nm In wb.Names
nm.Delete
Next

For Each sh In wb.Sheets

If sh.Visible = xlSheetHidden Then GoTo continue:

shnm = Replace(Mid(sh.Name, 1, InStr(1, sh.Name, "(") - 2), "#", "_")

For Each col In sh.Range(Cells(2, 2).Address, Cells(2, sh.Range("A2").End(xlToRight).Column).Address).Columns
trw = Cells(2, col.Column).End(xlDown).Row
brw = Cells(trw, col.Column).End(xlDown).Row
coln = col.Column
rAdr = Range(Cells(trw, coln), Cells(brw, coln)).Address
test = col.Value
wb.Names.Add Name:=shnm & Replace(Replace(Replace(col.Value, " ", "_"), "-", "_"), "/", "_"), RefersTo:=rAdr
Next
continue:
Next

End Sub

 

[Norie]

Brett

Have you considered not using the sheet names for the range names?

 

[Brettlb]

Hi Norie, I have, however the end users specify the names that have meaning to them, so I am trying to maintain thier inputs.

For example, sheet named API#4 by one user, could be API-4 by another, so ideally I would like to range name API4 through cleaning the name.

Thanks,

Brett

 

[Norie]

Brett

What is the purpose of these named ranges?

 

[Brettlb]

I am using them to create dynamic ranges for chart inputs.

The sheets contain historical price indices (as forwarded by the user), a single sheet for each index, dates down column A, reporting periods across columns row 2.

My file is a charting template, where the user can select relevant indices, and request different calculation on the indices (e.g. FX ) to display a chart for the chosen reporting period (say Q2'08). The template references thier open historical indices file as described.

Sorry Norie, my work access does not allow me to use the upload tool.

Brett

 

[DonkeyOte]

Brett, see if this helps you iron out what you need to do:

http://www.bettersolutions.com/excel/EFC133/LT423111411.htm

Unfortunately the characters that are invalid are not "invalid" per se so something like CLEAN won't work in this instance.

Have a look at CODE and CHR and iterating the characters of the sheet name

eg

Dim i As Long
Dim s_ws As String: s_ws = ActiveWorksheet.Name
Dim s_nr As String: s_nr = s_ws
For i = 1 to LEN(s_ws)
     Select Case Code(Mid(s_ws,i,1)
          Case .... 'invalid codes
               s_nr = Replace(s_nr,Code(Mid(s_ws,i,1)),"")
     End Select
Next i
'continue and test string to see if meets other criteria like 1st char must be letter; / or _ and adjust accordingly...

EDIT: in hindsight it would be easier to have cases for permitted codes as these can be grouped together quite easily... and remove anything else.

 

[Richard Schollar]

If you need to go down this route then i suspect it would be easier simply to remove any non-alphanumeric characters from the sheet names (and possibly prepend the name with an underscore or other alphanumeric character, as names can't begin with numbers).

 

[Brettlb]

Thanks DonkeyOte and Richard, I will try both and use the most efficent result.

I appreciate the help.

Brett

 

[Richard Schollar]

Here's one way you could remove any non-alphanumeric:

Dim b() As Byte, strRngName As String, i As Long
b = StrConv(ActiveSheet.Name, vbFromUnicode)
For i = 0 To UBound(b)
    Select Case b(i)
        Case 48 To 57, 65 To 90, 97 To 122
            strRngName = strRngName & Chr(b(i))
    End Select
Next
strRngName = "_" & strRngName
MsgBox strRngName

 

[Brettlb]

Thanks again guys.

Here is the code I have used:
In Sub test():

wb.Names.Add Name:=AlphaNum(sh.Name) & AlphaNum(col.Value), RefersTo:=rAdr


Function AlphaNum(nm As String) As String

Dim a$, b$, c$, i As Integer

a$ = nm
For i = 1 To Len(a$)
b$ = Mid(a$, i, 1)
If b$ Like "[A-Z,a-z,0-9]" Then
c$ = c$ & b$
End If
Next i
AlphaNum = c$

End Function