Renaming Invalid Defined Names

[P. Dua-Brown]

Hi
I'm using Excel 2003 and would like to know if the following is possible.

Some of the spreadsheets used in the office started life in Lotus 1-2-3 and
now contain loads of defined names! I can remove most of the names but some
are a bit more tricky as they contain characters such as %, $, & and even
squares.

Reading through past threads I have figured out that by switching to R1C1
notation through the Tools, Options, General tab I can rename the invalid
ones. So I use Application.ReferenceStyle = xlR1C1 to switch over and then
the Name Conflict dialog box is displayed and I'm able to enter new names
(one at a time) for each of the invalid ones. What I would like to do is
automate the renaming of the invalid names in the Name Conflict dialog box
to Junk1, Junk2 etc.

Is this possible???

Many thanks
Preeti

 

[Guest]

Taking a slightly different approach, you can rename the 'bad' names using
the following macro (ChangeRangeNames) that uses the user-defined function
(CheckForLegalAnsi).


'/=============================================/
Sub ChangeRangeNames()
'change 'illegal' range names to 'legal' ones
Dim nRng As Name

On Error Resume Next

For Each nRng In ActiveWorkbook.Names
nRng.Name = CheckForLegalAnsi(nRng.Name)
Debug.Print nRng.Name
Next nRng

End Sub

'/=============================================/
Private Function CheckForLegalAnsi(strCheckName As String) _
As String
'strip out 'bad' characters from range names
Dim i As Integer, iTest As Integer, iChecker As Integer
Dim strBuildLegalName As String

If Len(strCheckName) = 0 Then
CheckForLegalAnsi = "Unknown"
Exit Function
End If

If Asc(Left(strCheckName, 1)) >= 48 And _
Asc(Left(strCheckName, 1)) <= 57 Then
strCheckName = "A_" & strCheckName
End If

strBuildLegalName = ""

'Test for illegal DOS characters in name
For i = 1 To Len(strCheckName)
iTest = 0
iChecker = Asc(Mid(strCheckName, i, 1))
If iChecker >= 48 Then
If iChecker <= 57 Then
iTest = 1
Else
If iChecker >= 65 Then
If iChecker <= 90 Then
iTest = 1
Else
If iChecker = 95 Then
iTest = 1
Else
If iChecker >= 97 Then
If iChecker <= 122 Then
iTest = 1
End If
End If
End If
End If
End If
End If
End If
If iTest = 0 Then
If i = 1 Then
strBuildLegalName = strBuildLegalName & "a_"
Else
strBuildLegalName = strBuildLegalName & "_"
End If
Else
strBuildLegalName = strBuildLegalName & _
Mid(strCheckName, i, 1)
End If
Next i

CheckForLegalAnsi = strBuildLegalName
' i = i
End Function
'/=============================================/

HTH,

 

[Guest]

If you really want to rename the ranges 'Junk', try this...

'/=========================================/
Sub ChangeRangeNames()
'change 'illegal' range names to 'legal' ones
Dim iJunk As Integer
Dim nRng As Name

On Error Resume Next

For Each nRng In ActiveWorkbook.Names
If CheckForLegalAnsi(nRng.Name) <> "OK" Then
nRng.Name = "Junk" & Text(iJunk, "_000")
iJunk = 1 + iJunk
End If
Next nRng

End Sub

'/=========================================/
Private Function CheckForLegalAnsi(strCheckName As String) _
As String
'rename 'bad' range names to 'Junk'
Dim i As Integer, iTest As Integer, iChecker As Integer

On Error Resume Next

CheckForLegalAnsi = ""

If Asc(Left(strCheckName, 1)) >= 48 And _
Asc(Left(strCheckName, 1)) <= 57 Then
CheckForLegalAnsi = "NoGood"
Exit Function
End If

'Test for illegal DOS characters in name
For i = 1 To Len(strCheckName)
iTest = 0
iChecker = Asc(Mid(strCheckName, i, 1))
If iChecker >= 48 Then
If iChecker <= 57 Then
iTest = 1
Else
If iChecker >= 65 Then
If iChecker <= 90 Then
iTest = 1
Else
If iChecker = 95 Then
iTest = 1
Else
If iChecker >= 97 Then
If iChecker <= 122 Then
iTest = 1
End If
End If
End If
End If
End If
End If
End If
If iTest = 0 Then
CheckForLegalAnsi = "NoGood"
Exit Function
End If
Next i

CheckForLegalAnsi = "OK"

End Function
'/=========================================/


HTH,

 

[P. Dua-Brown]

Hi Gary

Many thanks for your help - it's really appreciated.

I've tried both of the routines and am having problems. The routines create
new names but do not delete the invalid ones - is it possible to delete the
invalid names?

TIA
Preeti

 

[Dave Peterson]

Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager has
the ability to fix these invalid names.

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

You can also do it manually.

Tools|options|General|check r1c1 reference style
then
Tools|options|General|uncheck r1c1 reference style

You'll be prompted at one of these steps to supply a new name for those invalid
names. (I forget which one!)

 

[P. Dua-Brown]

Hi Dave

Thanks for the link. My query is it possible to automate the manual method
you've stated?

Regards
Preeti

 

[Dave Peterson]

Depends on what you mean by automate.

Jan Karel kind of automates it in his addin--but the user still needs to respond
to the dialog.

If you mean that, then you could do the same kind of thing--toggle that setting
and have the user ready to answer the prompt.

 

[P. Dua-Brown]

What I'd like to do automate the renaming process. Let say I have 32 invalid
names is there a way to use vba to rename each of the invalid names to new
valid names let's say Junk1, Junk2 ... Junk32?

 

[jkpieterse]

Hi,

I do have a utility that is able to do batch fixing of corrupt names.
Send me an email if you want it.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

What I'd like to do automate the renaming process. Let say I have 32 invalid
names is there a way to use vba to rename each of the invalid names to new
valid names let's say Junk1, Junk2 ... Junk32?

 

[jkpieterse]

Hi,

I do have a utility that is able to do batch fixing of corrupt names.
Send me an email if you want it.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

What I'd like to do automate the renaming process. Let say I have 32 invalid
names is there a way to use vba to rename each of the invalid names to new
valid names let's say Junk1, Junk2 ... Junk32?