Hey guys, welcome back. So in this video we're going to discuss the difference between the worksheets collection object and the sheets collection object. So basically, the worksheets collection contains only the worksheets that we use often you know the regular type when you press the plus button. This is the type of worksheets that get inserted by default, or if you right click on any sheet click on insert and click on worksheet and press OK. This is the only type of worksheets or sheets that is contained in the worksheets collection. Okay.

However, the sheets collection contains the other types as well. So the chart and the macro sheets and international macro sheets and all these sheets that we don't use often. Okay, so that's mainly the difference actually, the worksheets collection contains just this single type of worksheets, but actually the sheets collection contains chart sheets. Grow sheets and these other types that we don't use often. And to illustrate that with an example, if we write worksheets dot add, and then on the type parameter, we would write excel sheet, type dot XML chart, for example. Okay, so actually, the chart sheet is not one of the types that are part of the worksheets collection.

So if you run that you will get an error. And actually, it is a bit weird that there is actually a type parameter in the worksheets collection because if you think about it, the worksheets collection contains only one type, and there is no need to put a type parameter, right. At least that's what I think so, I mean, this parameters, in my opinion should not exist, but Oh, well. Okay, so if you actually change the worksheets dot add and make it just sheets dot add and run That you will actually have a chart sheet inserted. So this actually illustrates that actually the chart sheet is part of the sheets collection. One more thing I want to tell you as well is that when inserting a sheet, you could actually omit the actual excel sheet type.

This is a constant, you can omit it actually and right just type equals Excel chart and this will enable you to insert a chart sheet as well. But the Excel sheet type enables you to have a drop down with the sheet type. So the different sheet types so it is better I believe to write it because this will enable you to have a list and you won't need to memorize the difference. Sheet types how they are written. So, okay to illustrate the difference even more, we're going to use the sheets to count property. We're going to put it inside a message box And actually, I'm going to comment this line of code as well so that it wouldn't be executed.

And now if you run that macro here, you will get a four. And the reason is that Excel is counting all the sheet types because the sheets collection object contains all the sheet types. So the regular worksheets that we use and the chart sheets as well, we've got four in total here. Now if you actually run the messagebox was worksheets count. You'll see here that we get a two only because it's only counting the regular type of worksheets that we use, which is sheet one and sheet two. This is why we're getting it to Okay, so this is basically the difference between the worksheets collection object and the sheets collection object.

So thanks guys for watching this video and I'll see you on the next one.