VBA Worksheets

Excel is a workbook. In that workbook, it contains worksheets or sheets. Understanding the concept of Worksheets in VBA is important because we all work with worksheets. In a normal Excel file, we call it sheets, but in VBA terminology, it is called a "Worksheet." All the collections of a worksheet are called "Worksheets."

In VBA, a Worksheet is an object. Therefore, there are two ways of referring to the worksheet, one using the "Worksheet" object and another using the "Sheets" object.

We know your question is what the difference between them is. We can see two sheets in Excel: regular worksheets and chart sheets.

The "worksheet" tab in excel considers only the worksheets in the workbook except for chart sheets. On the other hand, "Sheets" considers all the worksheets in the workbook, including the chart sheet. For example, look at the below image.

In the above, we have a total of 5 sheets. Of these 5 sheets, 3 are worksheets, and 2 are chart sheets.

Here, the "Worksheet" count is 3, and the "Sheets" count is 2.

Now, look at the below image.

All the sheets are worksheets, so the count of both "Worksheets" and "Sheets" is 3.

So, as part of the code, if you want to use worksheets, and objects, remember this point.

As we said, the worksheet is an object variable. However, this has syntax too.

The index is nothing that is the worksheet number we are referring to. However, as you can see in the end, it is referred to as an Object.

For example, Worksheet(1).Select means to select the first worksheet of the workbook. It doesn't matter what the worksheet's name is; whatever the worksheet inserted first in the workbook will be selected.

We can also refer to the worksheet by its name. We need to mention the complete as it is a worksheet name in double quotes.

For example, Worksheet("Sales Sheet").Select means select the sheet named "Sales Sheet." Here it doesn't matter what the number of the worksheet it always selects is.

Example #1

Assume you have a total of 5 sheets in your workbook. The name of those worksheets is "Worksheet 1", "Worksheet 2", "Worksheet 3", "Chart Sheet 1", and "Chart Sheet 2."

If we use the numbering to select the worksheet, then we can use the number as the worksheet reference.

Worksheet(2). Select means it will select the second worksheet of the workbook.

Code:

Sub Worksheet_Example1()

  Worksheets(2).Select

End Sub

We will run this code using the F5 key or manually and see the result.

Now, we will change the sheet number to 3.

Code:

Sub Worksheet_Example1()

  Worksheets(3).Select

End Sub

Now, see what happens when you run the code manually or using the F5 key code.

If you look at the above image, it selected the 4th worksheet when we asked to select the 3rd one.

That is because we have used the Worksheet object, not the Sheets object. As we told earlier, the "Worksheets" object considers only worksheets, not chart sheets.

Use the Sheets object to select the third sheet of all the sheets in the workbook.

Code:

Sub Worksheet_Example1()

Sheets(3).Select

End Sub

Now, it will select the exact third sheet.

Example #2 - Select Worksheets by Name

Selecting the sheets by their name is an accurate way of referring to the sheet. For example, if we want to select the sheet "Worksheet 3," you can use the code below.

Code:

Sub Worksheet_Example2()

   Worksheets("Worksheet 3").Select

End Sub

It will select the exact sheet. It doesn't matter where placed in the workbook.

But if you try to access the chart sheet with the "Worksheets" object, we will get a “Subscript out of range error.”

Code:

Sub Worksheet_Example2()

  Worksheets("Chart Sheet 1").Select

End Sub

Run this code through the F5 key or manually and see the result.

Example #3 - Problem with Worksheet Name

There is one more problem with referring to the sheets by their name. If someone changes the worksheet's name, we will get the "Subscript out of range error."

To solve this issue go to the basic visual editor by pressing the ALT + F11 key.

Select the sheet name and press the F4 key to see the "Properties" window.

The window changes the worksheet's name to your name in these properties.

One interesting thing is that even though we have changed the worksheet's name from "Worksheet 1" to "WS1," we can still see the same name in the workbook.

Now, we can refer to this sheet by the "WS1" name.

Code:

Sub Worksheet_Example2()
 
  Worksheets("WS1").Select

End Sub

Now, it doesn't matter who changes the name of the worksheet. Still, our code refers to the same sheet as long as it is not changing in the Visual Basic Editor.

Example #4 - Get the Count of Total Sheets in the Workbook

A worksheet is an object. We can use all the properties and methods associated with it. So what do we do with worksheets?

We insert worksheets. We rename worksheets. We delete worksheets and many other things we do with them.

Enter the object "Worksheets" and put a dot to see all the options with them.

To get the count of the worksheets, use VBA Count Property.

Code:

Sub Worksheet_Example3()

  Dim i As Long

  i = Worksheets.Count

  MsgBox i

End Sub

It will show the count of the worksheets.

Even though there are 5 sheets, we got the count as 3 because the other 2 sheets are chart sheets.

To get the overall count of sheets, use the "Sheets" object.

Code:

Sub Worksheet_Example3()

  Dim i As Long

  i = Sheets.Count

  MsgBox i

End Sub

It will show the full count of the sheets.

Example #5 - Methods Using Worksheet Object

After entering the worksheet object, we can access all the associated properties and objects. For example, we can add a new sheet. We can delete, etc.

To Add New Sheet.

Worksheet.Add

To Delete Worksheet

Worksheet(“Sheet Name”).Delete

To Change the Name of the Worksheet

Worksheet(“Sheet Name”).Name = “New Name”