Worksheet and Worksheet Objects in Microsoft Excel

The workbookand worksheetobjects are very useful Excel objects to use when you want to control the focus of your VBA code. Using these objects you can work with worksheets and different workbooks without the workbook and worksheets having the direct focus. For example, this could allow you to develop a VBA module that runs through all the worksheets in a workbook and combine the data into a single worksheet.

The Basics:

So how do you declare a workbook and worksheet object.

Code:

Dim WB As Workbook‘Declare a workbook object

Dim WS As Worksheet‘Declare a worksheet object

‘Initialize the variables

Set WB = ThisWorkBook ‘Assign the current workbook to the workbook object

Set WS = WB.Worksheet(“Sheet1”)‘Let’s assume I want to assign the worksheet Sheet1 to the worksheet object WS.

Now that you have declared and initialized the workbook and worksheet objects you can access the objects actions and properties.

Now, let’s look at an example. Let’s assume I want to determine the directory path or the name of the worksheet. We can easily accomplish that using the following code.

Code

MsgBox WB.Path‘Will return the path of the current workbook

MsgBox WS.Name‘Will return the name of the worksheet that we have assigned to the worksheet object.

We can also easily access cells in our worksheet object by using the following set of commands.

WS.Cells(Row, Column).value = “Test Value” ‘Write the value “Test Value” to the specified cell

OurVariable = WS.Cells(Row, Column).value‘Obtain the value for that specific cell and assigned it to the variable.