Rx_
Nothing In Moderation
- Local time
- Today, 02:09
- Joined
- Oct 22, 2009
- Messages
- 2,803
This function can be used to remove un-wanted characters from any string. Sometime in Excel object model reporting, the user is asked to name the tab.
This function allows the user to type anything, then just removes the characters that could cause a problem.
People can adjust it for just about any purpose.
This function allows the user to type anything, then just removes the characters that could cause a problem.
People can adjust it for just about any purpose.
Code:
'--------------------------------------------------------------------------------
' Procedure : FormatExcelTabName
' DateTime : 08/05/2010
' Author : Rx
' Purpose : Excel - allow user to enter tab name during custom report in Excel
' Add your own characters to avoid in the Case statement after line 40 - can be used for
' any type of string entry to remove unwanted characters
'--------------------------------------------------------------------------------
' Immediate Window Test: ? FormatTabName("MySt/r?ing*") Returns: MyString
Public Function FormatExcelTabName(strExcelTabName As String) As String
Dim strNewExcelTabName As String
Dim intTabLen As Integer
Dim intPos As Integer
10 On Error GoTo PROC_ERROR
20 intTabLen = Len(strExcelTabName)
30 For intPos = 1 To intTabLen
40 Select Case Mid(strExcelTabName, intPos, 1)
Case ":", "\", "/", "?", "*", "[", "]"
50 strNewExcelTabName = strNewExcelTabName & ""
60 Case Else
70 strNewExcelTabName = strNewExcelTabName & Mid(strExcelTabName, intPos, 1)
80 End Select
90 Next intPos
100 strNewExcelTabName = Trim(strNewExcelTabName)
110 FormatExcelTabName = Trim(Mid(strNewExcelTabName, 1, 31))
PROC_EXIT:
120 On Error Resume Next
130 Exit Function
PROC_ERROR:
140 Select Case Err.Number
'Case ###
Case Else
150 ' add logging code here (optional)
160 Resume PROC_EXIT
170 End Select
End Function
