Worksheets vs Sheets

If you are a beginner of Excel VBA, you will be confused with Worksheets and Sheets. What is the difference between Worksheets and Sheets? Can they be used synonymously?

Worksheets Object

Worksheets Object is a collection of all the Worksheet objects in the specified or active workbook. Each Worksheet object represents a worksheet. The Worksheet object is also a member of the Sheets collection. The Sheets collection contains all the sheets in the workbook (both chart sheets and worksheets).

Sheets Object

Sheets Object is a collection of all types of sheets in the specified or active workbook. The Sheets collection can contain Chart or Worksheet objects. Although today we only use 2 types of Sheets, Worksheets and Chart Sheets, there used to be 3 more types of Sheets, like Dialog Sheets or Macro Sheets. You may still have in your company old excel files that use them. In that case if you check the Sheets collection you'll seem them all there.

Examples

If a workbook has 4 worksheets and 1 chart sheet, in VBA:
Sheets.Count will include both types: 5
Worksheets.Count will include only worksheets: 4

Note: In VBA you should use Worksheets() instead of Sheets() when referencing a sheet from another sheet in a formula.

In Conclusion

Sheets: collection of the Sheets of all types
Worksheets: collection of Sheets of type Worksheet
Charts: collection of Sheets of type Chart Sheet

Comments

  1. Wellsley Over says

    Thanks. I’ve bookmarked this site, for future reference. I’ve been wondering about Worksheets and Sheets for a while now. They both worked so why the difference.

  2. roberto says

    How should I travel through a wookbook with a FOR-EACH if the workbook has worksheets and charts.
    I would like to make visible or hidden any specific sheet.
    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *