VBA UDF (User Defined Function) to determine if a file name is valid, e.g.

If IsValidFileName("test.txt") Then
     Msgbox "File name is valid"
Else
     Msgbox "File name is NOT valid."
End If

I was working on some code that would save an Excel file and thought it would be good to check if the file name was actually a valid name. So, I found an article about naming files and used the info in it to write a formula to check if a file or folder name is a legal file name. I was thinking whether to name it 'IsValidFileFolderName' or 'IsValidFileName'. I decided on the shorter version. See the quote below for why.

From 'Naming Files, Paths, and Namespaces:

Note that a directory is simply a file with a special attribute designating it as a directory, but otherwise must follow all the same naming rules as a regular file. Because the term directory simply refers to a special type of file as far as the file system is concerned, some reference material will use the general term file to encompass both concepts of directories and data files as such. Because of this, unless otherwise specified, any naming or usage rules or examples for a file should also apply to a directory.

The code below takes a string and determines if it is a legal file name or folder name.

VBA Code

Public Function IsValidFileName(sFileName As String) As Variant

    '************************************************************************
    'Written by Azli Hassan
    'http://azlihassan.com.apps
    '
    'Function: To determine if a string is a valid file or folder name
    '************************************************************************

    Dim aReservedChars As Variant, aReservedNames As Variant
    Dim i As Long, lngPeriodPos As Long

    On Error GoTo ErrHandler

    'Naming Files, Paths, and Namespaces
    'https://msdn.microsoft.com/en-us/library/windows/desktop/aa365247(v=vs.85).aspx
    
    'Note that a directory is simply a file with a special attribute designating it as a directory,
    'but otherwise must follow all the same naming rules as a regular file.
    'Because the term directory simply refers to a special type of file as far as the file system is concerned,
    'some reference material will use the general term file to encompass both concepts of directories and data files as such.
    
    'Array of reserved characters/filenames
    aReservedChars = Array("< ", ">", ":", """", "/", "\", "|", "*", "?")
    
    'Array of reserved  file names
    aReservedNames = Array("CON", "PRN", "AUX", "NUL", _
                           "COM1", "COM2", "COM3", "COM4", _
                           "COM5", "COM6", "COM7", "COM8", _
                           "COM9", "LPT1", "LPT2", "LPT3", _
                           "LPT4", "LPT5", "LPT6", "LPT7", _
                           "LPT8", "LPT9")

    
    'Check sFilename is not error value (Shouldn't be able to pass an error)
    If IsError(sFileName) Then
        IsValidFileName = False
        Exit Function
    End If
    
    'Check that something was passed, and not error
    If IsMissing(sFileName) Or sFileName = "" Then
        IsValidFileName = False
        Exit Function
    End If
    
    
    'Set initial result to True
    IsValidFileName = True
    
    'Check for RESERVED characters
    For i = LBound(aReservedChars) To UBound(aReservedChars)
        If InStr(1, sFileName, aReservedChars(i)) > 0 Then
            IsValidFileName = False
            Exit Function
        End If
    Next i

    'Check for ILLEGAL characters
    'Integer value zero, sometimes referred to as the ASCII NUL character.
    'vbNullChar / Chr(0) / Character having a value of 0.
    'Characters whose integer representations are in the range from 1 through 31
    For i = 0 To 31
        If InStr(1, sFileName, Asc(i)) > 0 Then
            IsValidFileName = False
            Exit Function
        End If
    Next i

    'Check for RESERVED file names
    lngPeriodPos = InStrRev(sFileName, ".")
    'If no ".", then use length so that Left(sFileName, ..) won't fail
    If lngPeriodPos = 0 Then lngPeriodPos = Len(sFileName)
    'Use upper case for string comparison to be case-insensitive
    sFileName = UCase(sFileName)
    For i = LBound(aReservedNames) To UBound(aReservedNames)
        'Do not assume case sensitivity. For example, consider the names OSCAR, Oscar, and oscar to be the same,
        'even though some file systems (such as a POSIX-compliant file system) may consider them as different.
        'ALSO avoid these names followed immediately by an extension; for example, NUL.txt is not recommended.
        If sFileName = UCase(aReservedNames(i)) Or Left(sFileName, lngPeriodPos - 1) = UCase(aReservedNames(i)) Then
            IsValidFileName = False
            Exit Function
        End If
    Next i

    Exit Function

ErrHandler:
    'On error, set result to error
    IsValidFileName = CVErr(Err.Number)
    Exit Function

End Function

The code in the sample file is slightly different that the one below. It returns a user defined type that contains two values; the results and a string that explains why a file name was illegal, e.g. "has reserved character". Thought it might be useful to be able to know why something wasn't valid. That being said, the code above will work just fine as-is.

• IsValidFileName(): Used to get either the result or the explanation. Can be used as an formula in Excel.
• IsValidFileNameUDT(): Essentially the same function as the one showed above, but returns two values as a UDT (User Defined Type).