Excel VBA Tutorial Part 8 - Excel Objects
Home »
Excel-VBA-Tutorial »
Excel-Objects
Search this site:
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 chart shows the structure of some of the more commonly used Excel objects.
In the chart, the objects are positioned below the other objects that they are accessed from.
It is stressed that the list below is just a selection of the Excel objects and is, by no means,
a complete list of Excel objects.
The Excel Objects in the above chart are discussed in turn below.
| 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
|
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:
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
|
