Look For Sheets Wildcard no Loop

[fredlo2010]

Hello,

I am wondering if there is a way to find a specific group of sheets with a wildcard without looping. I have this code and it works perfectly. I am just curious to see if there is another way. I think performance will improve when the code has to go through 300 sheets.

In my mind I was thinking on a way similar to how DIR() behaves.

Sub Try() '

Dim sh As Worksheet


Const strNAME As String = "AP-*"


For Each sh In ActiveWorkbook.Sheets
    If sh.Name Like strNAME And sh.Visible = xlSheetHidden Then
        sh.Visible = xlSheetVisible
    End If
Next sh
    
End Sub

Thanks

[Aflatoon]

But you would still have to loop to test the visibility.

As long as the workbook name doesn't include your search text, you could use:

    Dim vSheets    
    ActiveWorkbook.Names.Add "SheetList", "=GET.WORKBOOK(1)"
    vSheets = Filter(ActiveSheet.Evaluate("SheetList"), "AP-")

but I don't really see the point.

[Paul_Hossler]

From what I've read, Like is a slow operator, so a few minor suggestions.

I'd just make them visible all the time

Sub Try2()
     
    Dim sh As Worksheet 
     
    For Each sh In ActiveWorkbook.Sheets
        If Left(sh.Name,3) = "AP-" then sh.Visible = xlSheetVisible 
    Next sh 
     
End Sub

[fredlo2010]

From what I've read, Like is a slow operator

Wow I did not know that. Thanks a lot for the information Paul. The only problem about this is that I will have to pay attention to the number of chars.

I wonder how much of a difference "like" compared to "Left$()"

Thanks

[Paul_Hossler]

http://www.aivosto.com/vbtips/stringopt.html

http://www.aivosto.com/vbtips/stringopt2.html

http://www.aivosto.com/vbtips/stringopt3.html

Like

The Like operator is not particularly fast. Consider alternatives. We don't have a generic rule to follow here. You need to measure the performance differences between your alternatives. Here is one rule though. It applies if you're looking for a certain string inside another one.
Instead of:
If Text$ Like "*abc*" Then

Use:
If InStr(Text$, "abc") <> 0 ThenYou may also use InStrB if you know what you're doing

But it most likely will not much difference for 300 or so compares

[fredlo2010]

Thanks

I was just reading the same articles. Yes, I am not going back and change all my "like" now but its something to keep in mind for the future or when I get back to the code to refactor.

Thanks