VBA: Multiple OR conditions in single InStr?

Question

Is there an efficient way to have a single InStr have a list of expressions sought?

The segment of my code in question:

For Each sf In oFolder.SubFolders
    If InStr(1, sf, "STAG", vbTextCompare) Or InStr(1, sf, "STAP", vbTextCompare) Then
    Else
        colFolders.Add sf
    End If
Next sf

I'm using this to gather a collection of all files within the folders and subfolders of a directory. There are possibly hundreds of folders in question and there are a few repeating folder names that I want to just skip. My macro is working and doing exactly what I want it to (finally). And I can use the mentioned InStr to skip subfolders with the specified name. But there is a list of about a dozen recurring names, and it seems like it would be very inefficient to have a dozen InStr items in my IF statement. I feel like there's got to be a better way, but my amateur knowledge is lacking and doesn't even know what to begin searching for.

My complete code, in case it's needed:

Sub GetFilesCol()
    Application.ScreenUpdating = False
    
    Dim ofso As Scripting.FileSystemObject
    Dim oFolder As Object
    Dim oFile As Object
    Dim i As Long, colFolders As New Collection, ws As Worksheet
 
    Set ws = Sheets.Add(Type:=xlWorksheet, After:=ActiveSheet)
    Set ofso = CreateObject("Scripting.FileSystemObject")
    Set oFolder = ofso.GetFolder("F:\TestDirectory")
    
    'Keeping On Error Resume Next only temporarily while I test and make sure everything else is working
    On Error Resume Next
           
    ws.Cells(1, 1) = "File Name"
    ws.Cells(1, 2) = "File Type"
    ws.Cells(1, 3) = "Date Created"
    ws.Cells(1, 4) = "Date Last Modified"
    ws.Cells(1, 5) = "Date Last Accessed"
    ws.Cells(1, 6) = "File Path"
    
    Rows(1).Font.Bold = True
    Rows(1).Font.Size = 11
    Rows(1).Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous
    Range("C:E").Columns.AutoFit
           
    colFolders.Add oFolder          'start with this folder
    
    Do While colFolders.Count > 0      'process all folders
        Set oFolder = colFolders(1)    'get a folder to process
        colFolders.Remove 1            'remove item at index 1
                    
        For Each oFile In oFolder.Files
            
                ws.Cells(i + 2, 1) = oFile.Name
                ws.Cells(i + 2, 2) = oFile.Type
                ws.Cells(i + 2, 3) = oFile.DateCreated
                ws.Cells(i + 2, 4) = oFile.DateLastModified
                ws.Cells(i + 2, 5) = oFile.DateLastAccessed
                ws.Cells(i + 2, 6) = oFolder.Path
                i = i + 1
            
        Next oFile

        'add any subfolders to the collection for processing
        For Each sf In oFolder.SubFolders
            If InStr(1, sf, "STAG", vbTextCompare) Or InStr(1, sf, "STAP", vbTextCompare) Then
            Else
                colFolders.Add sf
            End If
        Next sf
           
    Loop

    Application.ScreenUpdating = True
    
End Sub

`If InStr(1, sf, "STA", vbTextCompare) > 0 Then` ? Could you have more undesirable folders? Let us say you would like STAG and STAP, but not STAM? If so then you could have an array of elements "STAG", "STAP", STAH", "STAC" or, if you only have one element that you would like to exclude (I want STAA to STAZ but exclude STAM) go like this `If InStr(1, sf, "STA", vbTextCompare) > 0 And InStr(1, sf, "STAM", vbTextCompare) = 0 Then` — Sgdva, Aug 10 '22 at 21:03
Most of the folders do start with "STA" but not all of them. They're actually internal acronyms for my employer's different business locations. STAG, STAM, STAR, STAV and more (13 of them actually), but there are a few other folders that don't fit their naming convention that need to be excluded as well. So I'm just trying to grab all the files within our directories that are not specific to these particular business locations. (I can't just specify my business location because there are also lots of folders that are not location specific that I need) — Sange7, Aug 10 '22 at 21:12
What's the pattern for those that don't fit because right now you can just `instr` on `STA`. If the other pattern is also similar, that's just 2 `if`s. — findwindow, Aug 10 '22 at 21:14
Outside of our typical location pattern, there isn't any pattern. Examples of folders I need to include are "DivS", "SRS", "98 Berufsbilding" "GEB Ausschuss" (we're an international company, so I'm going to be doing a lot of copying/pasting to make my lengthy exclusion list) — Sange7, Aug 10 '22 at 21:19

score 1 · Accepted Answer · answered Aug 10 '22 at 21:07

1

You can add all the specific names to an array and then iterate the array. Something like this:

Dim canAdd As Boolean
Dim arrExclude() As Variant
Dim v As Variant

arrExclude = Array("STAG", "STAP") 'Add as many names as needed
For Each sf In oFolder.SubFolders
    canAdd = True
    For Each v In arrExclude
        If InStr(1, sf, v, vbTextCompare) > 0 Then
            canAdd = False
            Exit For
        End If
    Next v
    If canAdd Then colFolders.Add sf
Next sf

answered Aug 10 '22 at 21:07

Cristian Buse

4,020
1
13
34

This worked perfect! Thank you so much! I actually tried your suggestion as well as another one and they both worked flawlessly. I'm going to do some experimenting now on some of my larger directories and see what works best/fastest and go from there. – Sange7 Aug 10 '22 at 21:25

score 1 · Answer 2 · answered Aug 10 '22 at 21:10

1

Maybe push out the check into a separate function, so you can do this in the main code:

'...
If Not SkipFolder(sf.Name) Then colFolders.Add sf
'...

Function:

Function SkipFolder(folderName)
    Dim v
    For Each v In Array("STAG", "STAP", "XXX") 'etc etc
        If InStr(1, folderName, v, vbTextCompare) > 0 Then
            SkipFolder = True
            Exit Function
        End If
    Next v
End Function

answered Aug 10 '22 at 21:10

Tim Williams

154,628
8
97
125

This worked perfect! Thank you so much! I actually tried your suggestion as well as another one and they both worked flawlessly. I'm going to do some experimenting now on some of my larger directories and see what works best/fastest and go from there. – Sange7 Aug 10 '22 at 21:25

score 0 · Answer 3 · answered Aug 10 '22 at 21:57

Another approach is to use Regular Expressions. In the .Pattern you would add your exclusions separated by the pipe symbol |.

' Library Reference: Microsoft VBScript Regular Expressions 5.5
With New RegExp
    .IgnoreCase = True
    .Pattern = "STAG|STAP"
    
    For Each sf In oFolder.SubFolder
        If Not .test(sf) Then
            colFolders.Add sf
        End If
    Next
End With

VBA: Multiple OR conditions in single InStr?

3 Answers3