In VBA, the worksheet object represents a single worksheet that is a part of the workbook’s worksheets (or sheets) collection. Using the worksheet object, you can refer to the worksheet in a VBA code, and refer to a worksheet you can also get access to the properties, methods, and events related to it.
Here’s a small snapshot of where a worksheet stands in the Excel object hierarchy.
Application ➪ Workbook ➪ Worksheets ➪ Worksheet
In this tutorial, we will learn about using and referring to a worksheet in Excel using a VBA code.
Sheets Vs. Worksheets
First thing first. This is important to understand the difference between a worksheet and a sheet. In Excel, you have types of sheets that you can insert into a workbook, and a worksheet is one of those types. As you can see in the below snapshot when you insert a new sheet Excel asks you to select the sheet type.
Here’s the point to understand: When you use the word “Sheets” you are referring to all the sheets (Worksheets, Macro Sheets, and Chart Sheets), but when you use the word “Worksheet” you are referring only to the worksheets (see also).
Accessing a Worksheet (Sheet) using VBA
VBA gives you different ways to access a worksheet from a workbook, and ahead, we will see different ways to do that.
1. Refer to a Sheet using the Name
Every sheet has its name to identify it, and you can use it to refer to that sheet as well. Let’s say you want to refer to the “Sheet1”, the code would be:
Both above codes refer to the “Sheet1”.
2. Refer to Sheet using Number
You can also use a sheet’s number to refer to it. Let’s if a sheet is at the fifth position in the workbook then you can use this number to refer to it.
Sheets (5) Worksheets (5)
Now here above two lines of code work in two different ways. The first line refers to the 5th sheet, and the second line refers to the 5th worksheet in the workbook.
3. Refer to the ActiveSheet
If a sheet is already active, then you can refer to it, using the keyword “Activesheet” instead of its name.
If you want to perform an activity in the ActiveSheet, you can use the “Activesheet” object, but if you skip using it, VBA will still perform the activity in the active sheet.
Read: Select a Range using VBA
4. Refer to a Sheet using Code Window Name
Each sheet has its code window, and there’s a name to that code window. Usually, a user can change the sheet name from the tab, but the name that you have in the code window can’t be changed unless you do it from the properties.
Open the Visual Basic Editor from the Developer Tab, and in the properties section, you can see the name of the sheet that you have selected.
And you can also change this name from the properties section.
Now you can refer to it by using the code window name.
5. Refer to More than One Sheet
You can also refer to more than one sheet in one go using a single line of code. For this, you can use an array, just like the following code.
This code refers to “Sheet1” and “Sheet2”, but, there’s one thing that you need to understand when you refer to more than one sheet, there are a few methods and properties that you can’t use.
6. Refer to Sheet in a Different Workbook
A worksheet or a sheet is a part of the worksheets collection in a workbook, and if you want to refer to a specific sheet other than the active workbook, then you need to refer to that workbook first.
To run this code, you need to have “Book1” open.
Properties, Methods, and Events Related to a Sheet or a Worksheet
In VBA, each Excel object has some properties, methods, and events that you can use, and in the same way, you can access the properties and methods that come with it. Once you specify a worksheet, type a dot (.), and you’ll get the list.
In this list, all the icons where you can see a hand are properties, and where you have green brick are methods.
Let’s say you want to change the color of the worksheet’s tab, in this case, you can use the TAB property of the worksheet.
mySheet.Tab.ThemeColor = xlThemeColorAccent2
In the above line of code, you have the tab property and further theme color property to change the tab color of the worksheet.
In the same way, you can use the methods that come with worksheets. One of the most common methods is the “Select” method that you can use to select a sheet.
The moment you run this code, it selects the “mySheet” from the active workbook.
Some events are associated with a worksheet. For example, when you activate a sheet, that’s an event, and in the same way, when you change something within the sheet. See the following code where you have code to run when an event (change in the worksheet) happens.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A1").Value = Range("A1").Value + 1 End Sub
This code enters a value in the cell A1 of the sheet every time you make a change in the worksheet.
Declaring a Worksheet Object
You can also declare a variable as a worksheet, making it easy to use that worksheet in a VBA code. First, use the DIM keyword, and then the name of the variable. After that, specify the object type as a worksheet.