"LegalWorkSheetName" Function?

[(PeteCresswell)]

I'm almost done writing a little "LegalWorkSheet()" function that accepts the
proposed worksheet name and returns a version of it that's guaranteed not to
trap out when assigned to a worksheet.

One thing I haven't done yet is to iterate through all existing sheets to see if
any are the same as the one I just concocted and modify the new one if
needed...and then check again.

Can anybody point me to some other code that does this - perhaps in a more
elegant and correct way?

Here's what I have so far:
---------------------------------------

Const mExcelWorkSheetNameLen_Lim As Long = 29 'Error 1004 says 31, but
experimentation suggests 29 is the actual limit

Public Function LegalWorksheetName(ByVal theWorkSheetName As String) As String
debugStackPush mModuleName & ": LegalWorksheetName"
On Error GoTo LegalWorksheetName_Err

' PURPOSE: To make sure that a proposed worksheet name is legal per Excel's
standards
' ACCEPTS: The string to be converted
' RETURNS: A string with any illegal characters replaced with underscores
'
' NOTES: 1) We originally chose which chars to replace from the following
' error thrown by Excel when we tried to feed it
' an unacceptable name and then added "^" and dropped the length
limit
' from experience...
' -----------------------------------------------------------------
' Error 1004: While renaming a sheet or chart, you entered an
invalid name.
' Try one of the following:
' ? Make sure the name you entered does not exceed 31
characters.
' ? Make sure the name does not contain any of the
following characters: : \ / ? * [ or ]
' ? Make sure you did not leave the name blank.

Dim myBadBoyz() As Variant

Dim i As Integer
Dim myWorkSheetName As String
Dim myArraySize As Long
Dim myRemainder As Long

Const myBenignChar As String = "x"
Const myTrimLen_Left As Long = 15
Const myTrimLen_Rite As Long = mExcelWorkSheetNameLen_Lim - myTrimLen_Left

myBadBoyz = Array(":", "\", "/", "?", "*", "[", "]", "^")
myArraySize = UBound(myBadBoyz)

myWorkSheetName = theWorkSheetName
For i = 0 To myArraySize
myWorkSheetName = Replace(myWorkSheetName, myBadBoyz(i), myBenignChar)
Next i

If Len(myWorkSheetName) <= mExcelWorkSheetNameLen_Lim Then
LegalWorksheetName = myWorkSheetName
Else
LegalWorksheetName = Left$(myWorkSheetName, myTrimLen_Left) &
Right$(myWorkSheetName, myTrimLen_Rite)
End If

LegalWorksheetName_xit:
DebugStackPop
On Error Resume Next
Exit Function

LegalWorksheetName_Err:
BugAlert True, ""
Resume LegalWorksheetName_xit
End Function

 

[Bob Phillips]

Const mExcelWorkSheetNameLen_Lim As Long = 29 'Error 1004 says 31, but
experimentation suggests 29 is the actual limit

Public Function LegalWorksheetName(ByVal theWorkSheetName As String) As
String
debugStackPush mModuleName & ": LegalWorksheetName"
On Error GoTo LegalWorksheetName_Err

' PURPOSE: To make sure that a proposed worksheet name is legal per Excel's
standards
' ACCEPTS: The string to be converted
' RETURNS: A string with any illegal characters replaced with underscores
'
' NOTES: 1) We originally chose which chars to replace from the following
' error thrown by Excel when we tried to feed it
' an unacceptable name and then added "^" and dropped the length
limit
' from experience...
' -----------------------------------------------------------------
' Error 1004: While renaming a sheet or chart, you entered an
invalid name.
' Try one of the following:
' ? Make sure the name you entered does not exceed
31 characters.
' ? Make sure the name does not contain any of the
following
' characters: : \ / ? * [ or ]
' ? Make sure you did not leave the name blank.

Dim myBadBoyz() As Variant

Dim i As Integer
Dim myWorkSheetName As String
Dim myArraySize As Long
Dim myRemainder As Long
Dim fGood As Boolean
Dim sh As Worksheet
Dim iSheet As Long

Const myBenignChar As String = "x"
Const myTrimLen_Left As Long = 15
Const myTrimLen_Rite As Long = mExcelWorkSheetNameLen_Lim - myTrimLen_Left

myBadBoyz = Array(":", "\", "/", "?", "*", "[", "]", "^")
myArraySize = UBound(myBadBoyz)

myWorkSheetName = theWorkSheetName
For i = 0 To myArraySize
myWorkSheetName = Replace(myWorkSheetName, myBadBoyz(i), myBenignChar)
Next i

If Len(myWorkSheetName) <= mExcelWorkSheetNameLen_Lim Then
LegalWorksheetName = myWorkSheetName
Else
LegalWorksheetName = Left$(myWorkSheetName, myTrimLen_Left) & _
Right$(myWorkSheetName, myTrimLen_Rite)
End If

On Error Resume Next
iSheet = 0
Do
Set sh = Nothing
Set sh = Worksheets(LegalWorksheetName)
fGood = sh Is Nothing
If Not fGood Then
iSheet = iSheet + 1
LegalWorksheetName = Left(LegalWorksheetName, _
Application.Min(mExcelWorkSheetNameLen_Lim -
Len(CStr(iSheet)), _
Len(LegalWorksheetName))) & iSheet
End If
Loop Until fGood

LegalWorksheetName_xit:
DebugStackPop
On Error Resume Next
Exit Function

LegalWorksheetName_Err:
BugAlert True, ""
Resume LegalWorksheetName_xit
End Function



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

I'm almost done writing a little "LegalWorkSheet()" function that accepts
the
proposed worksheet name and returns a version of it that's guaranteed not
to
trap out when assigned to a worksheet.

One thing I haven't done yet is to iterate through all existing sheets to
see if
any are the same as the one I just concocted and modify the new one if
needed...and then check again.

Can anybody point me to some other code that does this - perhaps in a more
elegant and correct way?

Here's what I have so far:
---------------------------------------

Const mExcelWorkSheetNameLen_Lim As Long = 29 'Error 1004 says 31,
but
experimentation suggests 29 is the actual limit

Public Function LegalWorksheetName(ByVal theWorkSheetName As String) As
String
debugStackPush mModuleName & ": LegalWorksheetName"
On Error GoTo LegalWorksheetName_Err

' PURPOSE: To make sure that a proposed worksheet name is legal per
Excel's
standards
' ACCEPTS: The string to be converted
' RETURNS: A string with any illegal characters replaced with underscores
'
' NOTES: 1) We originally chose which chars to replace from the
following
' error thrown by Excel when we tried to feed it
' an unacceptable name and then added "^" and dropped the
length
limit
' from experience...

-----------------------------------------------------------------
' Error 1004: While renaming a sheet or chart, you entered an
invalid name.
' Try one of the following:
' ? Make sure the name you entered does not
exceed 31
characters.
' ? Make sure the name does not contain any of
the
following characters: : \ / ? * [ or ]
' ? Make sure you did not leave the name blank.

Dim myBadBoyz() As Variant

Dim i As Integer
Dim myWorkSheetName As String
Dim myArraySize As Long
Dim myRemainder As Long

Const myBenignChar As String = "x"
Const myTrimLen_Left As Long = 15
Const myTrimLen_Rite As Long = mExcelWorkSheetNameLen_Lim - myTrimLen_Left

myBadBoyz = Array(":", "\", "/", "?", "*", "[", "]", "^")
myArraySize = UBound(myBadBoyz)

myWorkSheetName = theWorkSheetName
For i = 0 To myArraySize
myWorkSheetName = Replace(myWorkSheetName, myBadBoyz(i), myBenignChar)
Next i

If Len(myWorkSheetName) <= mExcelWorkSheetNameLen_Lim Then
LegalWorksheetName = myWorkSheetName
Else
LegalWorksheetName = Left$(myWorkSheetName, myTrimLen_Left) &
Right$(myWorkSheetName, myTrimLen_Rite)
End If

LegalWorksheetName_xit:
DebugStackPop
On Error Resume Next
Exit Function

LegalWorksheetName_Err:
BugAlert True, ""
Resume LegalWorksheetName_xit
End Function

 

[(PeteCresswell)]

Per Bob Phillips:

On Error Resume Next
iSheet = 0
Do
Set sh = Nothing
Set sh = Worksheets(LegalWorksheetName)
fGood = sh Is Nothing
If Not fGood Then
iSheet = iSheet + 1
LegalWorksheetName = Left(LegalWorksheetName, _
Application.Min(mExcelWorkSheetNameLen_Lim -
Len(CStr(iSheet)), _
Len(LegalWorksheetName))) & iSheet
End If
Loop Until fGood

I like it.

Thanks.

 

[Dave Peterson]

just a comment about the length--you should test it again. I think you'll find
it really is 31 characters.

I'm almost done writing a little "LegalWorkSheet()" function that accepts the
proposed worksheet name and returns a version of it that's guaranteed not to
trap out when assigned to a worksheet.

One thing I haven't done yet is to iterate through all existing sheets to see if
any are the same as the one I just concocted and modify the new one if
needed...and then check again.

Can anybody point me to some other code that does this - perhaps in a more
elegant and correct way?

Here's what I have so far:
---------------------------------------

Const mExcelWorkSheetNameLen_Lim As Long = 29 'Error 1004 says 31, but
experimentation suggests 29 is the actual limit

Public Function LegalWorksheetName(ByVal theWorkSheetName As String) As String
debugStackPush mModuleName & ": LegalWorksheetName"
On Error GoTo LegalWorksheetName_Err

' PURPOSE: To make sure that a proposed worksheet name is legal per Excel's
standards
' ACCEPTS: The string to be converted
' RETURNS: A string with any illegal characters replaced with underscores
'
' NOTES: 1) We originally chose which chars to replace from the following
' error thrown by Excel when we tried to feed it
' an unacceptable name and then added "^" and dropped the length
limit
' from experience...
' -----------------------------------------------------------------
' Error 1004: While renaming a sheet or chart, you entered an
invalid name.
' Try one of the following:
' ? Make sure the name you entered does not exceed 31
characters.
' ? Make sure the name does not contain any of the
following characters: : \ / ? * [ or ]
' ? Make sure you did not leave the name blank.

Dim myBadBoyz() As Variant

Dim i As Integer
Dim myWorkSheetName As String
Dim myArraySize As Long
Dim myRemainder As Long

Const myBenignChar As String = "x"
Const myTrimLen_Left As Long = 15
Const myTrimLen_Rite As Long = mExcelWorkSheetNameLen_Lim - myTrimLen_Left

myBadBoyz = Array(":", "\", "/", "?", "*", "[", "]", "^")
myArraySize = UBound(myBadBoyz)

myWorkSheetName = theWorkSheetName
For i = 0 To myArraySize
myWorkSheetName = Replace(myWorkSheetName, myBadBoyz(i), myBenignChar)
Next i

If Len(myWorkSheetName) <= mExcelWorkSheetNameLen_Lim Then
LegalWorksheetName = myWorkSheetName
Else
LegalWorksheetName = Left$(myWorkSheetName, myTrimLen_Left) &
Right$(myWorkSheetName, myTrimLen_Rite)
End If

LegalWorksheetName_xit:
DebugStackPop
On Error Resume Next
Exit Function

LegalWorksheetName_Err:
BugAlert True, ""
Resume LegalWorksheetName_xit
End Function

 

[(PeteCresswell)]

Per Dave Peterson:

just a comment about the length--you should test it again. I think you'll find
it really is 31 characters.

I'll revisit it.

Was suspicious of 29 from the get-go - but I must of tried it twenty times in
the Immediate window.

Probably some kind of RCI on my part though.

Same with the up-carrot ("^")

 

[(PeteCresswell)]

Per (PeteCresswell):

Was suspicious of 29 from the get-go - but I must of tried it twenty times in

Still can't get past the 29/31 thing.

It's not trapping out when I add the sheet, but when I try to associate a chart
with the sheet.

To wit:
4998 myChart.Location Where:=xlLocationAsObject, Name:=theWS.Name

Other than that, it seems tb working ok.

Here's what I wound up with.

I wimped on the error trapping approach to determining whether a sheet
of a given name was already there because I got into trouble with it -
also because of my basic anal nature, I guess... don't like code with
error trapping turned off....

Still need to revisit the up-carrot.

-------------------------------------------------------------------
Const mExcelWorkSheetNameLen_Lim As Long = 29 'Error
1004 says 31, but experimentation seems to indicate 29 is the actual limit


Public Function WorkSheetName_Legal(ByVal theWorksheetName As String, ByRef
theWB As Excel.Workbook) As String
10000 debugStackPush mModuleName & ": WorkSheetName_Legal"
10001 On Error GoTo WorkSheetName_Legal_Err

' PURPOSE: To make sure that a proposed worksheet name is legal per Excel's
' standards
' ACCEPTS: - The string to be converted
' - Pointer to the workbook where the sheet bearing the proposed
' name
' will reside
' RETURNS: - A string with any illegal characters replaced with underscores
'
' NOTES: 1) The source for our logic is the following error - thrown when
' we try to add an illegal name
' ----------------------------------------------------
' Error 1004: While renaming a sheet or chart, you entered an
' invalid name.
' Try one of the following:
' • Make sure the name you entered does not exceed
' 31 characters.
' • Make sure the name does not contain any of the
' following characters: : \ / ? * [ or ]
' • Make sure you did not leave the name blank.
' ----------------------------------------------------

10002 Dim myBadBoyz() As Variant

Dim i As Integer
Dim myWorkSheetName As String
Dim myArraySize As Long
Dim myRemainder As Long

Const myBenignChar As String = "_"

' ----------------------------------------
' Create an array of illegal characters

10010 myBadBoyz = Array(":", "\", "/", "?", "*", "[", "]", "^")
10019 myArraySize = UBound(myBadBoyz)

' ----------------------------------------
' Replace any illegal characters with a harmless character

10020 myWorkSheetName = theWorksheetName
10021 For i = 0 To myArraySize
10022 myWorkSheetName = Replace(myWorkSheetName, myBadBoyz(i), myBenignChar)
10029 Next i

' ----------------------------------------
' If the name is too long right-trunc it to allowed length

10030 If Len(myWorkSheetName) > mExcelWorkSheetNameLen_Lim Then
10031 myWorkSheetName = Left$(myWorkSheetName, mExcelWorkSheetNameLen_Lim)
10039 End If

' ----------------------------------------
' Finally, make sure that the name is unique, replacing
' the last few chars with a sequence number if it is not

10050 myWorkSheetName = worksheetName_Unique(myWorkSheetName, theWB)

' ----------------------------------------
' Return the name

10999 WorkSheetName_Legal = myWorkSheetName

WorkSheetName_Legal_xit:
DebugStackPop
On Error Resume Next
Exit Function

WorkSheetName_Legal_Err:
BugAlert True, ""
Resume WorkSheetName_Legal_xit
End Function


Private Function worksheetName_Unique(ByVal theName As String, ByRef theWB As
Excel.Workbook) As String
11000 debugStackPush mModuleName & ": worksheetName_Unique"
11001 On Error GoTo worksheetName_Unique_err

' PURPOSE: To ensure that a proposed worksheet name will be unique within a
' given workbook
' ACCEPTS: - Proposed name
' - Pointer to the Excel Workbook object that the worksheet will be
' part of
' RETURNS: A name that is guaranteed tb unique - albeit maybe truncated by
' a few chars with a sequence# appended

11002 Dim myWS As Excel.Worksheet

Dim k As Long
Dim myName As String
Dim mySuffix As String
Dim gotError As Boolean
Dim gotGoodName As Boolean

11010 myName = theName

11020 Do
1129 gotGoodName = Not worksheet_Exist(myName, theWB)
11030 If gotGoodName = False Then
11031 k = k + 1
11032 mySuffix = Format$(k, "#0")
11033 myName = Left(theName, mExcelWorkSheetNameLen_Lim - Len(mySuffix))
& mySuffix
11039 End If
11099 Loop Until gotGoodName = True

11999 worksheetName_Unique = myName

worksheetName_Unique_xit:
DebugStackPop
On Error Resume Next
Exit Function

worksheetName_Unique_err:
BugAlert True, ""
Resume worksheetName_Unique_xit
End Function


Private Function worksheet_Exist(ByVal theWorksheetName As String, ByRef theWB
As Excel.Workbook) As Boolean
debugStackPush mModuleName & ": worksheet_Exist"
On Error GoTo worksheet_Exist_err

' PURPOSE: To determine if a worksheet of a given name exists in a
' specified workbook
' ACCEPTS - Name of the worksheet in question
' - Pointer to the workbook we want to check
' RETURNS: True if the sheet exists, else False
'
' NOTES: 1) We got into trouble trying to do it the easy way: namely
' just trying to set a WorkSheet pointer to the name
' in question and checking .Err.

Dim k As Long
Dim i As Long

k = theWB.Worksheets.Count

If k > 0 Then
For i = 1 To k
If theWB.Worksheets(i).Name = theWorksheetName Then
worksheet_Exist = True
End If
Next i
End If

worksheet_Exist_xit:
DebugStackPop
On Error Resume Next
Exit Function

worksheet_Exist_err:
BugAlert True, ""
Resume worksheet_Exist_xit
End Function

 

[(PeteCresswell)]

Per (PeteCresswell):

10020 myWorkSheetName = theWorksheetName
10021 For i = 0 To myArraySize
10022 myWorkSheetName = Replace(myWorkSheetName, myBadBoyz(i), myBenignChar)
10029 Next i

Oops! I see a bug already....