VBA Worksheets

 

Excel VBA Worksheets

Excel is a workbook and in that workbook, it contains worksheets or sheets. It is very important to understand the concept of Worksheets in VBA because all the time we work with worksheets. In normal excel file, we call it as sheets but in VBA terminology it is called as “Worksheet”. All the collections of a worksheet are called “Worksheets”.

In VBA Worksheet is an object. There are two ways of referring the worksheet one is using the “Worksheet” object and another one using the “Sheets” object.

I know your question is what the difference between them is. In excel we can see two kinds of sheets, one is regular worksheets and another one is chart sheets.

Worksheet tab in excel considers only the worksheets in the workbook except for chart sheets. “Sheets” considers all the worksheets in the workbook including chart sheet. For an example look at the below image.

In the above we have totally of 5 sheets, out of these 5 sheets 3 are worksheets and 2 are chart sheets.

Here “Worksheet” count is 3 and the “Sheets” count is 2.

Now, look at the below image.

Here all the sheets are worksheets so the count of both “Worksheet” and “Sheets” is 3.

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

Syntax of VBA Worksheets

As I told worksheet is an object variable. However, this has syntax too.

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

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

We can also refer to the worksheet by its name as well. 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 as “Sales Sheet”. Here it doesn’t matter what is the number of the worksheet it always selects the specified worksheet.

How to use Worksheets Object in VBA?

Example #1

For an example assume you have totally 5 sheets in your workbook and name of those worksheets are “Worksheet 1”, “Worksheet 2”, “Worksheet 3”, “Chart Sheet 1”, and “Chart Sheet 2”.

If I use the numbering to select the worksheet then I 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

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

Now I 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 has selected the 4^th worksheet when I asked to select the 3^rd worksheet.

This is because I have used Worksheet object not Sheets object. As I told earlier “Worksheets” object considers only worksheets, not chart sheets.

To select the third sheet of all the sheets in the workbook use Sheets object.

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 name their name is the accurate way of referring the sheet. For example, if we want to select the sheet “Worksheet 3” then you can use the below code.

Code:

Sub Worksheet_Example2()

   Worksheets("Worksheet 3").Select

End Sub

This will select the exact sheet, it doesn’t matter where it is placed in the workbook.

But if you tried to access the chart sheet with the “Worksheets” object we will get “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 the sheets by its name. If someone changes the name of the worksheet then again we will get the “Subscript out of range error”.

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

Now select the sheet name and press the F4 key to see the properties window.

In these properties, the window changes the name of the worksheet to your name.

One interesting thing here is even though we have changed the name of the worksheet from “Worksheet 1” to “WS1” we can still see the same name in the workbook.

Now we can refer this sheet by “WS1” name.

Code:

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

End Sub

Now it doesn’t matter whoever changes the name of the worksheet, still, our code refers to the same sheet as long as it is not changing the in the visual basic editor.

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

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

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

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

To get the count of the worksheets uses VBA Count Property.

Code:

Sub Worksheet_Example3()

  Dim i As Long

  i = Worksheets.Count

  MsgBox i

End Sub

This 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 uses the “Sheets” object.

Code:

Sub Worksheet_Example3()

  Dim i As Long

  i = Sheets.Count

  MsgBox i

End Sub

This will show the full count of the sheets.

Example #5 – Methods Using Worksheet Object

After entering the worksheet object we can access all the properties and objects associated with it. 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”

Recommended Articles

This has been a guide to VBA Worksheets. Here we learn how to use the VBA Worksheet object to find, select, and get the count of total worksheets in Excel along with some simple to advanced examples. Below are some useful excel articles related to VBA –

• GetObject in VBA
• VBA Delete All Excel Files
• Excel VBA Worksheet Function
• VBA New Line