Renaming Invalid Defined Names

  • Thread starter Thread starter P. Dua-Brown
  • Start date Start date
P

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
 
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,
 
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,
 
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
 
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!)
 
Hi Dave

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

Regards
Preeti
 
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.
 
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?
 
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

P. Dua-Brown said:
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?
Click to expand...
 
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

P. Dua-Brown said:
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?
Click to expand...
 
Back
Top