VBA Express : Excel - Function to Produce a Valid Worksheet Name from a String

Excel

Function to Produce a Valid Worksheet Name from a String

Ease of Use

Easy

Version tested with

2007

Submitted by:

TJCMicropile

Description:

This function accepts any string and returns a string that is correctly formatted for use as worksheet name.

Discussion:

It is sometimes desireable to name a worksheet from a user-input value (i.e. from a textbox, etc.) - especially when the worksheet is added programmatically. However, such user input (i.e. text) may contain characters that are invalid for use in a worksheet name. This simple function reduces any string into one that can be properly used as a worksheet name without generating an error.

Code:

instructions for use

			Function CleanWorksheetName(ByRef strName As String) As String 
    Dim varBadChars As Variant 
    Dim varChar As Variant 
     
    varBadChars = Array(":", "/", "\", "?", "*", "[", "]") 
     
     'correct string for forbidden characters
    For Each varChar In varBadChars 
        Select Case varChar 
        Case ":" 
            strName = Replace(strName, varChar, vbNullString) 
        Case "/" 
            strName = Replace(strName, varChar, "-") 
        Case "\" 
            strName = Replace(strName, varChar, "-") 
        Case "?" 
            strName = Replace(strName, varChar, vbNullString) 
        Case "*" 
            strName = Replace(strName, varChar, vbNullString) 
        Case "[" 
            strName = Replace(strName, varChar, "(") 
        Case "]" 
            strName = Replace(strName, varChar, ")") 
        End Select 
    Next varChar 
     
     'correct string for worksheet length requirement
    strName = Left(strName, 31) 
     
    CleanWorksheetName = strName 
End Function

How to use:

Download and open the example file. ---OR---
Copy above code.
In the Office Application, press Alt + F11 to enter the VBE.
Press Ctrl + R to show the Project Explorer.
Right-click desired file on left (in bold).
Choose Insert -> Module.
Paste code into the right pane.
Press Alt + Q to close the VBE.
Save workbook before any other changes.

Test the code:

If you opened the example file, open the macro window (button on the developer tab for 2007, Tools=>Macros for 2003 and earlier), choose "Test" and click "Run." Enter \/\/\/\/?[\/?[vbaexpress]?\/]?\/\/\/\/ in the inputbox and click OK - the result should be the same as described on the last step. ---OR---
If you didn't open the example file, do the following:
Open the VBE again.
Add a new procedure (Insert => Procedure => (name = "Test") => Sub => Public => OK).
In the new procedure (i.e. sub) add a call to the function with the string of your choice - try the example code for this below, as shown on the next step.
MsgBox = CleanWorksheetName("\/\/\/\/?[\/?[vbaexpress]?\/]?\/\/\/\/")
Press F5 to run the procedure.
If you used the example code above, you should see a message box with a valid form of the worksheet name, no longer than 31 characters: --------(--(vbaexpress)--)-----

Sample File:

Example.zip 11.76KB

Approved by Jacob Hilderbrand

This entry has been viewed 188 times.