Multiple Apps

Tests for illegal characters in file name, directory name, xlsheet name, etc

Ease of Use

Easy

Version tested with

2000 

Submitted by:

MWE

Description:

BadChar tests for illegal character in file names, directory names, xlsheet names, etc. If an illegal character is encountered, the function returns the string index value for the illegal character. If no illegal characters are found, BadChar returns zero. 

Discussion:

WinXX and MS applications do not allow certain characters to be embedded in file names, directory names, Excel sheet names, etc. When creating such names under VBA control, one can do an error test to detect invalid names, but a better approach is to test the text for invalid characters before using the name. BadChar is a function of type Long that tests and text string for invalid or bad characters, i.e., " :? , ?\? , ?< " , "/" , "?" , "*" , "[" , "]". BadChar can be used in any VBA application. The demo is Excel-based. 

Code:

instructions for use 

			
Option Explicit 
 
Function BadChar(strText As String) As Long 
     '
     '****************************************************************************************
     '       Title       BadChar
     '       Target Application:  any
     '       Function    test for the presence of charcters that can not be used in
     '                   the name of an xlsheet, file, directory, etc
     '
     '           if no bad characters are found, BadChar = 0 on return
     '           if any bad character is found, BadChar = i where i is the index (in strText)
     '               where bad char was found
     '       Limitations:    passed string variable should not include any path seperator
     '                           characters
     '                       stops and exits when 1st bad char is found so # of bad chars
     '                           is not really known
     '       Passed Values:
     '           strText     [in, string]  text string to be examined
     '
     '****************************************************************************************
     '
     '
    Dim BadChars    As String 
    Dim I           As Long 
    Dim J           As Long 
     
    BadChars = ":\/?*[]" 
    For I = 1 To Len(BadChars) 
        J = InStr(strText, Mid(BadChars, I, 1)) 
        If J > 0 Then 
            BadChar = J 
            Exit Function 
        End If 
    Next I 
    BadChar = 0 
     
End Function

How to use:

  1. Copy the above code.
  2. Open any workbook.
  3. Press Alt + F11 to open the Visual Basic Editor (VBE).
  4. In the left side window, hi-lite the target spreadsheet [it will likely be called VBAProject(filename.xls) where filename is the name of the spreadsheet]
  5. Select an existing code module for the target worksheet; or from the Insert Menu, choose Insert | Module.
  6. Paste the code into the right-hand code window.
  7. Close the VBE, save the file if desired.
  8. See ?Test The Code? below
 

Test the code:

  1. Open the example
  2. The example/demo spreadsheet contains a single sheet, the BadChar function and a testing procedure.
  3. Click on the command button to start the test
  4. Enter text to be tested
  5. The demo should return an appropriate answer.
 

Sample File:

BadChar.zip 14.25KB 

Approved by mdmackillop


This entry has been viewed 108 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express