VBA Worksheet Object -Working with Excel Worksheet in VBA

- Written by Puneet

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.

Helpful Links: Run a MacroMacro RecorderVisual Basic EditorPersonal Macro Workbook

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).

sheet-vs-worsheets

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:

refer-to-a-sheet-using-name
Sheets(“Sheet1”)
Worksheets(“Sheet1”)

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.

refer-to-a-sheet-using-number

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.

ActiveSheet

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.

refer-to-a-sheet-using-code

And you can also change this name from the properties section.

using-the-code-window-name

Now you can refer to it by using the code window name.

mySheet

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.

Sheets(Array("Sheet1", "Sheet2"))

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.

Workbooks("Book1").Sheets("Sheet1")

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.

events-related-with-a-sheet

In this list, all the icons where you can see a hand are properties, and where you have green brick are methods.

Property Example

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.

Method Example

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.

mySheet.Select

The moment you run this code, it selects the “mySheet” from the active workbook.

Event Example

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.

event-example
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.

declaring-a-worksheet-object