Speed up looping through slicers and .selected true if slicer item name matches input words

Question

Sub SearchSlicer()
Dim oSc As SlicerCache
Dim oSi As SlicerItem
Dim searchword As Variant
searchword = InputBox("Enter a searchword")

Set oSc = ThisWorkbook.SlicerCaches("Slicer_SEARCH_WORDS")

For Each oSi In oSc.SlicerItems
      If oSi.Name Like "*" & UCase(searchword) & "*" Then
          oSi.Selected = True
      Else: oSi.Selected = False
      End If
Next

End Sub

So I have a pivot table with two columns - a column for a verb and a column for search words containing synonyms/similar phrases. For example, if a word in the first column is 'broke', the second column will contain the list 'fell apart, broken, split'.

Running the above code asks the user for a search word which will be used to loop through the search word column and filter the search word pivot table and slicers.

This search word slicer is linked to first column slicer as the search word slicer is not visible on the input sheet.

The reason for using slicers is because I have another code for when the user clicks on a slicer choice, it automatically pastes it into the active cell.

I have a table with 350 rows and this code is already taking very long to run. How can I make this more optimal?

Answer 1

Invariants, variables which don't change in a loop, should be set before entering the loop. Additionally, if you want to store the value of a Boolean expression, just store the expression's return value:

Sub SearchSlicer()
    Dim oSc As SlicerCache
    Dim oSi As SlicerItem
    Dim searchword As Variant

    searchword = InputBox("Enter a searchword")
    searchword = "*" & UCase(searchword) & "*"
    Set oSc = ThisWorkbook.SlicerCaches("Slicer_SEARCH_WORDS")

    For Each oSi In oSc.SlicerItems
        oSi.Selected = oSi.Name Like searchword
    Next oSi
End Sub

But I doubt this will speed up the loop much. Most likely the 'like' operator is where most of the time is spent (and I don't see any alternative to it).