Excel VBA Tutorial Part 8 - Excel Objects

The term Excel Objects (collectively referred to as the Excel Object Model) refers to the entities that make up an Excel workbook, such as Worksheets, Rows, Columns, Cell Ranges, and the Excel Workbook itself. Each object in Excel has a number of Properties, which are stored as a part of that object.

For example, an Excel Worksheet object's properties would include the Worksheet's Name, Protection, Visible Property, Scroll Area, etc. Therefore, if during the execution of a macro, we needed to hide an Excel worksheet, we would need to access the Worksheet object, and alter the 'Visible' property.

Excel VBA also has a special type of object, which is known as a Collection. As the name suggests, a Collection refers to a group (or collection) of Excel objects. For example, the Rows collection is an object containing all the rows of a Worksheet.

The main Excel Objects can all be accessed (directly or indirectly) from the Workbooks object, which is a collection of all the currently open Workbooks. Each Workbook object contains the Sheets object (consisting of all the Worksheets and Chart sheets in the Workbook), and in turn, each Worksheet object contains a Rows object (consisting of all Rows in the Worksheet) and a Columns object (consisting of all Columns in the Worksheet), etc.

The following table lists some of the more commonly used Excel objects.

Object Type Description
Workbooks The Workbooks object is a collection of all of the open Excel Workbooks in the current Excel Application. An individual Workbook can be extracted from the Workbooks object by using an individual Workbook index number or name (ie. Workbooks(1) or Workbooks("Book1")).
Workbook The Workbook is a member of the Workbooks Collection. As previously mentioned, a Workbook object can be accessed from the Workbooks Collection by using a Workbook index number or a Workbook name (ie. Workbooks(1) or Workbooks("Book1")). You can also use 'ActiveWorkbook' to access the current active Workbook.

From the Workbook object, you can access the Sheets object, which is a collection of all the Sheets (Worksheets and Chart Sheets) in the Workbook, and also the Worksheets object, which is a collection of all the Worksheets in the Workbook.
Sheets The Sheets object is a collection of all the Sheets in a Workbook. These Sheets can be Worksheets or Charts. An individual Sheet can be extracted from the Sheets object by using an individual Sheet index number or name (ie. Sheets(1) or Sheets("Sheet1")).
Worksheets The WorkSheets object is a collection of all the WorkSheets in a Workbook (ie. all the Sheets, except the Charts). An individual Worksheet can be extracted from the Worksheets object by using an individual Worksheet index number or name (ie. Worksheets(1) or Worksheets("Wksheet1")).
Worksheet The Worksheet object is a member of the Worksheets collection, and also a member of the Sheets collection. As previously mentioned, a Worksheet object can be accessed from the Sheets or the Worksheets object by using a Sheet or Worksheet index number or a Sheet or Worksheet name (ie. Sheets(1), Worksheets(1), Sheets("Sheet1") or Worksheets("Wksheet1")). You can also use 'ActiveWorksheet' to access the current active Worksheet.

From the Worksheet object, you can access the Rows and Columns objects, which are collections of Range objects relating to the Rows and Columns of the Worksheet. You can also access an individual cell or any Range of contiguous cells on the Worksheet.
Rows The Rows object is a collection of all the Rows of a Worksheet. A Range object consisting of an individual Worksheet row can be accessed by using an index number (ie. Rows(1))
Columns The Columns object is a collection of all the Columns of a Worksheet. A Range object consisting of an individual Worksheet column can be accessed by using an index number (ie. Columns(1))
Range The Range object represents any number from of contiguous cells on a Worksheet. This can be just one cell or it can be all the cells on the Worksheet.

A range consisting of just one cell can be returned from the Worksheet, using the Cells property (ie. Worksheet.Cells(1,1)).

Alternatively, a range can be referenced by specifying either a cell range or a start and end cell (ie. Worksheet.Range("A1:B10") OR Worksheet.Range("A1", "B10") OR Worksheet.Range(Cells(1,1), Cells(10,2))).

Note that if the second cell reference is omitted from the Range (ie. Worksheet.Range("A1") OR Worksheet.Range(Cells(1,1)), this will return a range that consists of only one cell.


The above table describes how you access Excel objects via 'parent' objects. For example, a range of cells may be referenced by the expression:

Workbooks("WB1").Worksheets("WS1").Range("A1:B10")

However, if "WB1" is the current active Workbook, you don't need to include the reference to this. This is because, when no Workbook is specified, a reference to Worksheets automatically accesses the current active Workbook. Therefore, the following will return the required Range:

Worksheets("WS1").Range("A1:B10")

Similarly, if "WS1" is the current active Worksheet, you don't need to include the reference to this. This is because, when no Worksheet is specified, a call to Range automatically accesses the current active Worksheet. Therefore, the following simple statement will still return the required Range:

Range("A1:B10")

The Active Object

We have previously mentioned that, the current Active Workbook or Worksheet is selected by default if no Workbook or Worksheet is specified. Also, the current active Workbook, Worksheet or Sheet can be referred to, in your vba code as ActiveWorkbook, ActiveSheet or ActiveWorksheet, and the current selected range can be accessed by referring to Selection.

When a macro starts running, the current active Workbook and Worksheet are those that you call the macro from, and the current selected Range is that (if any) which have been selected just before the macro was run. However, if, during the running of a macro, you want to change the current selected Worksheet, Range, etc, this can be done by using the 'Select' command, as shown in the examples below:

Worksheets("Data").Select
Range("A1", "B10").Select

Assigning an Object to a Variable

Another point to note, when working with Excel objects is that, when an object is being assigned to a variable in your vba code, you must use the Set keyword as follows:

Dim DataWb As Workbook
Set DataWb = Workbooks.Open("C:\Data.xls", False, True)

Examples

Example 1

The following VBA code snippet was previously used to illustrate the use of the For Each loop. It is now useful to re-visit this code to examine the references to the Worksheets object (taken from the current active Workbook by default), and the reference to each individual Worksheet. Note that the Worksheet Name property is accessed, to display the name of each Worksheet.

' Cycle through each Worksheet in the current Workbook
' and display the Worksheet name in a message box

Dim wSheet As Worksheet

For Each wSheet in Worksheets
    MsgBox "Found Worksheet: " & wSheet.Name
Next wSheet

Example 2

The following section of VBA code has been included to illustrate how you might access Worksheets and Ranges from other Workbooks and how the current Excel Objects are accessed by default if no specific object is referenced. This example also illustrates the use of the Set keyword to assign an Excel object to a variable.

' Copy a range of cells from Sheet1 of another Workbook (named "Data.xlsx"),
' and paste the values into the "Results" Worksheet of the current Workbook
' (named "CurrWb.xlxm")

Dim dataWb As Workbook

Set DataWb = Workbooks.Open("C:\Data")

' Note that DataWb is the current Active Workbook.
' Therefore the following accesses the 'Sheets' Object in DataWb.


Sheets("Sheet1").Range("A1:B10").Copy

' Paste the values from the copied Range into the "Results" Worksheet of
' the current Workbook. Note that, as CurrWb is not the current Active
' Workbook, we need to specify this Workbook.


Workbooks("CurrWb").Sheets("Results").Range("A1").PasteSpecial Paste:=xlPasteValues

Example 3

The following section of VBA code provides an example of the Columns object and the way in which this is accessed from the Worksheet object. It is also seen how, when accessing a cell or cell range on the current active Worksheet, we can omit the reference to the Worksheet. Again the code provides an illustration of the use of the Set keyword to assign a Range object to the variable 'Col'.

' Loop through the values in Column A of the Worksheet "Sheet2",
' perform arithmetic operations on each value, and write the result into
' Column A of the current Active Worksheet ("Sheet1")


Dim i As Integer
Dim Col As Range
Dim dVal As Double

' Set the variable 'Col' to be Column A of Sheet 2

Set Col = Sheets("Sheet2").Columns("A")
i = 1

' Loop through each cell of the column 'Col' until
' a blank cell is encountered


Do Until IsEmpty(Col.Cells(i))

    ' Apply arithmetic operations to the value of the current cell

    dVal = Col.Cells(i).Value * 3 - 1

    ' The command below copies the result into Column A
    ' of the current Active Worksheet - no need to specify
    ' the Worksheet name as it is the active Worksheet.


    Cells(i, 1) = dVal
    i = i + 1

Loop
Go To Excel VBA Tutorial Part 9 - Excel Events

Return to the Excel VBA Tutorial Page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2013 ExcelFunctions.net