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



LikeThe 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 (http://www.vbaexpress.com/forum/#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