VBA Excel string functions: sanitize sheet name

Microsoft Excel does not allow certain special characters in sheet names and the length of a sheet name cannot exceed 31 characters.

The function below sanitizes the name of the new sheet by replacing illegal characters with underscore (“_”) and by shortening the name string if it’s too long. This prevents unexpected run-time errors and is especially useful if the new sheet name is based on user input.

Returns an empty string (null string pointer) if the input string passed is an empty string or vbNullstring.

Table of Contents

Function
Parameters
Usage example

Function

VBA
Function SanitizeSheetName(ByVal sheetName As String, Optional replaceWith As String = "_") As String

        Dim arrExclude As Variant, varTmp As Variant
        
        If sheetName = vbNullString Then Exit Function
        If replaceWith <> "_" Then replaceWith = SanitizeSheetName(replaceWith)
        
        arrExclude = Array(":", "\", "/", "?", "*", "[", "]", ";")

        For Each varTmp In arrExclude
            sheetName = Replace(sheetName, varTmp, replaceWith)
        Next varTmp
        
        SanitizeSheetName = Left(sheetName, 31)

End Function

Parameters

Name	Optional/ Required	Date type	Description
sheetName	Required	String	The new sheet name to be sanitized. Notice, this parameter is passed by value, i.e. the original input string is left unchanged.
replaceWith	Optional	String	Default value is underscore, “_”. Optionally replace illegal characters with another string than underscore by passing this parameter. If the replacement string passed contains illegal characters, the function replaces these illegal characters with underscore before the sheet name character replacements are carried out.

Usage example

VBA
Sub TestSanitizeSheetName()

    Debug.Print SanitizeSheetName("[A sheet with a very long name and illegal characters */%]")
    ' result: _A sheet with a very long name 
    
    Debug.Print SanitizeSheetName("")
    ' result: nullstring
    
    Debug.Print SanitizeSheetName(vbNullString)
    ' result: nullstring
    
    Debug.Print SanitizeSheetName(":\/?*[];")
    ' result: ________

End Sub