excelvbastringsyntax-errorinstr

Problem with Scripting.FileSystemObject and INSTR and IF THEN in VBA code


I do not understand why this does not work

xSource = vrtSelectedItem  '<<== C:\Users\Me\Desktop\Document01.pdf

Set FSO = VBA.CreateObject("Scripting.FileSystemObject")
    
If Not InStr(xSource, ".jpg") Or Not InStr(xSource, ".bmp") Or Not InStr(xSource, ".png") _
Or Not InStr(xSource, ".tif") Or Not InStr(xSource, ".tga") Or Not InStr(xSource, ".jpeg") _
Or Not InStr(xSource, ".doc") Or Not InStr(xSource, ".pdf") Or Not InStr(xSource, ".rtf") _
Or Not InStr(xSource, ".htm") Or Not InStr(xSource, ".html") Or Not InStr(xSource, ".txt") _
Or Not InStr(xSource, ".docx") Or Not InStr(xSource, ".tdm") Or Not InStr(xSource, ".wri") _
Or Not InStr(xSource, ".xls") Or Not InStr(xSource, ".xlsx") Or Not InStr(xSource, ".xlsm") _
Or Not InStr(xSource, ".ods") Or Not InStr(xSource, ".odt") Then

MsgBox "File type not allowed"
Exit Sub

Else

.....

Although the file contains .pdf, I get MsgBox "File type not allowed"! This Happens also with all other file types I listed to actually exclude them from the error message! Can anyone give me some advice? Thanks


Solution

  • TL;DR: InStr doesn't return a Boolean, but rather a Variant (Long) specifying the position of the first occurrence of one string within another.


    Simplifying to explain the problem:

    xSource = "C:\Users\Me\Desktop\Document01.pdf"
    
    Debug.Print InStr(xSource, "pdf")
    Debug.Print Not InStr(xSource, "pdf")
    Debug.Print CBool(Not InStr(xSource, "pdf"))
    

    returns

     32 
    -33 
    True
    

    InStr does not return a boolean, rather the position of the first occurrence of one string within another. Rather than using Not, normally one would check if the result of InStr is > 0 to determine if a match was found.

    Using Not on a numeric expression is performing bitwise negation, which as demonstrated above causes the result of a match to ultimately evaluate to True. In fact, no match also evaluates to True (CBool(Not(0)) returns True).

    Here's an alternate way to do this:

    Private Function IsValidFileType(ByVal filePath As String) As Boolean
        Dim FSO As Scripting.FileSystemObject
        Set FSO = New Scripting.FileSystemObject
        
        Dim extension As String
        extension = FSO.GetExtensionName(filePath)
    
        Select Case extension
            Case "jpg", "bmp", "png" '< and so on
                IsValidFileType = True
            Case Else
                IsValidFileType = False
        End Select
    End Function