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's properties include the Worksheet's Name, Protection, Visible Property, Scroll Area, etc. Therefore, if during the execution of a macro, we wanted to hide an Excel worksheet, we could do this by accessing the Worksheet object, and altering the 'Visible' property.
Excel VBA has a special type of object, called 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. For a more comprehensive list of Excel VBA objects, see the Microsoft Office Developer website.
| Object Type | Description |
| Application | The current Excel Application. |
| 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 (i.e. Workbooks(1) or Workbooks("Book1")). |
| Workbook |
A Workbook object can be accessed from the Workbooks Collection by using a Workbook index number or a
Workbook name (e.g. 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 you can also access 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 (i.e. Sheets(1) or Sheets("Sheet1")). |
| Worksheets | The WorkSheets object is a collection of all the WorkSheets in a Workbook (i.e. 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 (i.e. Worksheets(1) or Worksheets("Wksheet1")). |
| Worksheet |
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 (e.g. Sheets(1), Worksheets(1), Sheets("Sheet1")
or Worksheets("Wksheet1")). You can also use 'ActiveSheet' to access the current active Sheet.
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 (i.e. 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 (i.e. Columns(1)) |
| Range |
The Range object represents any number 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 a Worksheet, using the Cells property (i.e. Worksheet.Cells(1,1)). Alternatively, a range can be referenced by specifying either a cell range or a start and end cell (e.g. 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 (e.g. Worksheet.Range("A1") OR Worksheet.Range(Cells(1,1)), this will return a range that consists of only one cell. |
The above table shows how you can 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") |
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("Data.xlsx") |
At any one time, Excel will have an Active Workbook, which is the workbook that is currently selected. Similarly, there will be an Active Worksheet and an Active Range, etc.
The current Active Workbook or Sheet can be referred to, in your vba code as ActiveWorkbook, or ActiveSheet, and the current active range can be accessed by referring to Selection.
If, in your VBA code, you refer to a worksheet, without referring to a specific workbook, Excel defaults to the current Active Workbook. Similarly, if you refer to a range, without referring to a specific workbook or worksheet, Excel defaults to the current Active Worksheet in the current Active Workbook.
Therefore, if you wish to refer to range A1:B10 on the currently Active Worksheet, within the currently Active Workbook, you can simply type:
| Range("A1:B10") |
If, during the execution of your code, you wish to change the current Active Workbook, Worksheet, Range, etc, this can be done using the 'Activate' or 'Select' methods as follows:
|
Workbooks("Book1.xlsm").Activate Worksheets("Data").Select Range("A1", "B10").Select |
Object Methods such as the 'Activate' and 'Select' methods used above, are discussed in more detail below.
VBA objects have related Properties associated to them. For example, the Workbook object has the properties 'Name', 'RevisionNumber', 'Sheets', and many more. These properties can be accessed by referring to the object name followed a dot and then the property name. For example, the name of the current active Workbook can be accessed by referring to ActiveWorkbook.Name. Therefore, to assign the current active Workbook name to the variable wbName, we could use the following code:
|
Dim wbName As String wbName = ActiveWorkbook.Name |
We previously illustrated how the Workbook object can be used to access a Worksheet using the command:
| Workbooks("WB1").Worksheets("WS1") |
This is because the Worksheets collection is a property of the Workbook object.
Some object properties are read-only, meaning that you cannot change their values. However, you can change the value of some object properties. For example, if you wanted to change the name of the current active sheet to "my worksheet", this could be done by simply assigning the name "my worksheet" to the active sheet's 'Name' property, as follows:
| ActiveSheet.Name = "my worksheet" |
VBA objects also have Methods that perform specific actions. Object methods are procedures that are associated to a specific object type. For example, the Workbook object has the methods 'Activate', 'Close', 'Save', and many more.
An Object Method can be called by referring to the object name followed a dot and then the method name. For example, the current active Workbook can be saved using the code:
| ActiveWorkbook.Save |
Like any other procedures, methods can have arguments that are supplied when the method is called. For example, the Workbook 'Close' method has three optional arguments which are used to provide information to the method such whether the Workbook is to be saved before closing, etc.
The method arguments are supplied to the method by following the call to the method with the argument values, separated by commas. For example, if you wanted to save the current active workbook as a .csv file called "Book2", you would call the Workbook SaveAs method with the Filename argument set to "Book2" and the FileFormat argument set to xlCSV:
| ActiveWorkbook.SaveAs "Book2", xlCSV |
To make your code more readable, you can use named arguments when calling a method. In this case, you type the argument name followed by the assignment operator := and then the value. Therefore, the above call to the Workbook SaveAs method could be written as:
| ActiveWorkbook.SaveAs Filename:="Book2", [FileFormat]:=xlCSV |
A list of Excel objects, with their properties and methods are provided in the Object Browser within the Visual Basic Editor. To display this, simply press F2 from within the Visual Basic Editor.
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
|
The following VBA code illustrates how you can access Worksheets and Ranges in different Workbooks. It also illustrates 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.
This example also shows the PasteSpecial method being called for the Range object. This method sets the 'Paste' argument to the value 'xlPasteValues'.
|
' 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.xlsm")
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
Workbooks("CurrWb").Sheets("Results").Range("A1").PasteSpecial Paste:=xlPasteValues
' the current Workbook. Note that, as CurrWb is not the current Active ' Workbook, we need to specify this Workbook. |
The following VBA code shows how the Columns (collection) object can be accessed from the Worksheet object. It is also seen that, when a cell or cell range on the current active Worksheet is accessed, the reference to the Worksheet can be omitted. Again the code provides an example of the Set keyword, which is used to assign a Range object to the variable 'Col'.
This code also includes an example of how to access and change the Range object's Value property.
|
' 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
Cells(i, 1).Value = dVal' of the current Active Worksheet - no need to specify ' the Worksheet name as it is the active Worksheet. i = i + 1 |