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

Question

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

The accepted answer is 100% OK, but there is another reason why your logic fails: you should be using AND in your comparisons, not OR. Maybe this is easiest to see with the equality: NOT a OR NOT b == NOT (a AND b), where in your case (a AND b) will always be FALSE and the message will always be printed. — user1016274, Jun 27 '21 at 11:14

BigBen · Accepted Answer · 2021-06-27T00:49:07.140

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

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

1 Answers1