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 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 full 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 (ie. 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 (eg. 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 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 (eg. 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 (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 a 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 (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 (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")

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("Data.xlsx")

The Active Object

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. Therefore, if you wish to refer to range A1:B10 on worksheet "WS1", within workbook "WB1" if WB1 is the current Active workbook and WS1 is the currently active worksheet, you can simply type

Range("A1:B10")

Changing the Current Active Object

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 are discussed in more detail below.


Object Properties

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, some of the properties can have values assigned to them. 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"

Object Methods

VBA objects also have methods. These are functions or subroutines that are associated to the specific object type. For example, the Workbook object has the methods 'Activate', 'Close', 'Save', and many more. These methods 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 functions and subroutines, 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.


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.

The code below 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.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 (collection) 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'.

The code also shows an example of the Value property of the Range object being accessed and also being changed.

' 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).Value = 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-2015 ExcelFunctions.net