Collection of selected worksheets

[YasserKhalil]

Hello everyone

The selected worksheets is supposed to be a collection and when I tried these two lines

Code: Select all

Dim x
x = ThisWorkbook.Windows(1).SelectedSheets

I encountered an error 'Error 450' Wrong number of arguments or invalid property assignment.

[rory]

You need to use Set with object variables (which collections are).

[YasserKhalil]

Thank you very much. How can I loop through the collection in that case?
I tried

Code: Select all

Dim e
For Each e In x.Items
    Debug.Print e
Next e

[HansV]

1) Items is not a property of a collection. You should use

Code: Select all

For Each e in x

2) e is a (work)sheet object, so Debug.Print e makes no sense. You can refer to its name, for example:

Code: Select all

    Debug.Print e.Name

or to a range on the sheet:

Code: Select all

    Debug.Print e.UsedRange.Address

etc.

[YasserKhalil]

Thank you very much.
Is there a way to check if a specific worksheet is within the selected sheets without using loops? I mean to match the worksheet name with the collection of the selected sheets.

[HansV]

You'll have to loop. Unlike the Dictionary object, the Collection object does not have a built-in method to check for membership, so a loop is the only way.

[YasserKhalil]

Thank you very much, my tutor.
Best Regards for all of you.

[SpeakEasy]

Erm ... not quite true. The Sheets Collection is not a simple VBA collection. It does support the Item property, and the Index you pass that can be an integer OR the name of the sheet in the collection, because you are matching the Key, which behind the scenes Excel sets to the same value as the Sheet name.. So a function like

Code: Select all

Public Function SheetExists(strSheetName As String, Optional testSheets As Sheets) As Boolean
    If testSheets Is Nothing Then Set testSheets = ActiveWorkbook.Sheets
    On Error Resume Next
        SheetExists = testSheets.Item(strSheetName).Name <> ""
    On Error GoTo 0
End Function

will happily tell you if a sheet with a specific name exists in the Sheets collection without having to loop at all

[YasserKhalil]

That's really amazing. Thanks a lot, Alan for this solution. Is it necessary to use On Error statements, is there an alternative for those statements?

[HansV]

Erm ... not quite true.

Thanks, very clever solution!

[SpeakEasy]

>is it necessary to use On Error statements

Yep. Or go back to looping. Nothing wrong with carefully considered use of On Error Resume Next