VBA sheets object in Excel
As the name indicates, this object represents a Microsoft Excel Worksheet.
The Worksheet object is a member of Worksheets Collection.Note that "Worksheet" is an object, whereas "Worksheets" is a collection.
This collection contains all the instance of Microsoft excel Worksheets
So, when there are more than one Worksheet, a specific Worksheet can be accessed with the help of index number.
The index numbers represent the order in which the Worksheets are arranged.
A Worksheet can also be accessed with the help of its name.
- Worksheets(1).Activate
- Worksheets(
).Activate
There are various properties of Worksheet which are often used along with this object.
Some of them are detailed as below
Name
The Worksheet.Name returns the name of the active Worksheet. This property can also be used to assign a name to a new sheet.
Activate
The Worksheet.Activate is used to access a Worksheet and its content by activating it.
Note that, only the active Worksheet can be accessed using Worksheet object.
Conversely, when a Worksheet is accessed, it becomes the active Worksheet
Range
The Sheets.Range() is used to access and work on a specified range.The range may be a group of cells or a single cell.
This method is most often used in VBA when working with sheets.
Cells
This is similar to Range except that, it can access only a single cell.
The syntax is Cells(row,col). Though this is helpful, Range method looks more meaningful.
Protect
This is used to protect a worksheet with a password
Visible
This method is used to hide or unhide a worksheet.
PrintOut
This method is used to print a worksheet.
PageSetup
This is used to configure the orientation of the sheet, either portrait or landscape.
The following example illustrates the WorkSheets Objects
For this purpose, let us add a new worksheet named "WS" and will test various actions in it
and finally, delete the worksheet.
- Sub worksheet_demo()
- Dim sht As Worksheet
- Set sht = Sheets.Add
- sht.Name = "WS"
- sht.Activate
- MsgBox "Range demo : put value in Cell A1 using Range object "
- sht.Range("A1") = "Hello"
- MsgBox "Range demo with multiple cells"
- sht.Range("A1:D5") = "Hi"
- MsgBox "Hide Worksheet"
- sht.Visible = False
- MsgBox "Unhide "
- sht.Visible = True
- sht.Activate
- sht.Delete
- End Sub
The screenshot from editor is as shown below