'**********************************************************************************************************
' Name:                 legalSheetName
' Author:               mielk | 2012-03-26
'
' Comment:              Function checks if a given name follows the rules for naming Excel worksheets.
'                       Legal Excel sheet name:
'                           *   cannot be empty,
'                           *   must be at most 31 characters long
'                           *   must not contain any of the illegal characters: : ? / \ * [ ]
'                       If the given name doesn't meet these requirements it is being adjusted and this
'                       adjusted value is being returned by the function.
'
' Parameters:
'   name                The name to be checked if it is a legal Excel worksheet name.
'
' Returns:
'   String              The given name after being adjusted to be a proper Excel sheet name.
'                       * If the original name itself is a legal Excel sheet name it is returned without
'                           any changes.
'                       * If the original name is empty, underline character is returned (_).
'                       * If the original name is longer than 31 characters, it is cut off.
'                       * All the illegal characters : ? / \ * [ ] are removed from the original name.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2012-03-26        mielk           Function created.
'**********************************************************************************************************
Public Function legalSheetName(name As String) As String
    Const METHOD_NAME As String = "legalSheetName"
    '------------------------------------------------------------------------------------------------------
    Const ILLEGAL_CHARS As String = ":?/\*[]"
    Dim intChar As Integer
    Dim strChar As String
    Dim strIllegalChars As String
    '------------------------------------------------------------------------------------------------------

    
    'Function iterates through all characters in the original name and removes the illegal ones. --------|
    For intChar = 1 To VBA.Len(name)                                                                    '|
        strChar = VBA.Mid$(name, intChar, 1)                                                            '|
                                                                                                        '|
        '--------------------------------------------------------------------------------------------|  '|
        If VBA.InStr(1, strIllegalChars, strChar) = 0 Then                                          '|  '|
            legalSheetName = legalSheetName & strChar                                               '|  '|
        End If                                                                                      '|  '|
        '--------------------------------------------------------------------------------------------|  '|
                                                                                                        '|
    Next intChar '---------------------------------------------------------------------------------------|
    
    
    'Checks the length of the sheet name after all the illegal characters have been removed. ------------|
    'If it is too long, the excessive characters are cut off.                                           '|
    Select Case Len(legalSheetName)                                                                     '|
        Case Is > 31                                                                                    '|
            legalSheetName = Left$(legalSheetName, 31)                                                  '|
        Case 0                                                                                          '|
            legalSheetName = "_"                                                                        '|
    End Select '-----------------------------------------------------------------------------------------|

End Function