Print

User Rating: 4 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Inactive
 

 

Excel VBA Objects; Excel Object Model; Access a Workbook, Worksheet or Range Object; Set Object Properties & Call its Methods

 

-----------------------------------------------------------------------------------

Contents:

VBA Objects

The Excel Object Model

Active Object

Access an Object / Access a Single Object from its Collection

Properties and Methods of Objects

Working with Objects in Excel VBA

-----------------------------------------------------------------------------------

 

An object is a thing which contains data and has properties and methods. Properties are the characteristics or attributes that describe the object, a Method is an action performed by an object. While writing vba code in Microsoft Office Excel, you will be using the objects provided by the Excel object model. The object model is a large hierarchy of all the objects used in VBA. All applications like Excel, Access, Word or PowerPoint, which use VBA, have their own object model. An object is a thing which contains data and has properties and methods. To manipulate an Object you will Set its Properties and Call its Methods.

 

For more articles related to Excel VBA objects: Excel VBA Application Object, the Default Object in Excel; Excel VBA Workbook Object, working with Workbooks in Excel; Microsoft Excel VBA - Worksheets; Excel VBA Range Object, Referencing Cells and Ranges; Excel VBA Custom Classes and Objects.

 

 

Visual Basic is not truly an Object-Orientated Programming (OOP) Language

 

Visual Basic (Visual Basic 6) is not truly an Object-Orientated Programming (OOP) Language whereas its successor Visual Basic.NET (part of the .NET platform) is a full-fledged Object Oriented programming language meeting the criteria of encapsulation, inheritance and polymorphism, where everything in Visual Basic.NET can be treated as an object. Visual Basic has many (but not all) elements of an Object-Orientated Programming (OOP) language. VBA deals with objects but is not truly an Object-Orientated Programming language.

 

 

 

VBA Objects

 

An object is a thing which contains data and has properties and methods. Properties are the characteristics or attributes that describe the object (like name, color, size) or define an object's behaviour (viz. if visible or enabled). An object's data or information can be accessed with properties (viz. Value property, Name property). A Method is an action performed by an object. Calling a Method will execute a vba code which will cause the object to perform an action. You can associate objects with nouns, properties with adjectives and methods with verbs. An object could be a house, car, table or pen. Properties of a car include its color or size, which describe it. A car can perform actions of moving, accelerating or turning which are its methods. Examples of objects in Excel are workbook, worksheet, range, command button, font, etc. A Range object has "Value" as one of its properties and "Select" as one of its methods. Similarly a worksheet has, among others, a "Name" property, a "Delete" method, and a "Copy" method having arguments which contain information in respect of the worksheet to be copied.

 

Objects also have event procedures attached to them. Events are actions performed, or occurrences, which trigger a VBA code or macro. An event procedure (ie. a vba code) is triggered when an event occurs such as opening / closing / saving / activating / deactivating the workbook, selecting a cell or changing cell selection in a worksheet, making a change in the content of a worksheet cell, selecting or activating a worksheet, when a worksheet is calculated, and so on. Excel provided built-in event procedure - an Event Procedure is automatically invoked when an object recognizes the occurrence of an event. Event procedures are attached to objects like Workbook, Worksheet, Charts, Application, UserForms or Controls.  Event Procedures are triggered by a predefined event and are installed within Excel having a standard & predetermined name viz. like the Worksheet change procedure is installed with the worksheet - "Private Sub Worksheet_Change(ByVal Target As Range)". In the Worksheet Change event procedure, the Worksheet object is associated with the Change event, which means that with the worksheet change event, a sub-procedure containing customized code runs automatically when you change the contents of a worksheet cell. Custom Events - you can also define your own events in custom classes (class modules), and create event procedures that run when those events occur.

 

A Collection Object in vba refers to a group of related items, as a single object. Many objects are present both in single form as well as in multiples. For example: (i) Workbook & Workbooks - all open Workbook objects in Excel are referred to as the Workbooks collection; (ii) Worksheet & Worksheets - A Worksheets Collection object refers to all Worksheets contained in a workbook. All elements (items) of a collection share the same properties and methods, though they do not need to be of the same data type. You can either create your own collection using the vba Collection class or use the Excel VBA built-in collections such as Worksheets (the Worksheets Collection Object includes all Worksheets in a workbook). With a Collection Object you can work with all objects (which are its elements) as a group as against working with a single object. The basic ways of working with elements of a collection include: adding an element using the Add method, removing an element using the Remove method, determining the number of elements contained in a collection using the Count property, accessing a specific element using the Item property, enumerate each element of a collection using the For Each...Next Statement, and so on.

 

 

 

The Excel Object Model

 

All applications like Excel, Access, Word or PowerPoint, which use VBA, have their own object model. While writing vba code in Microsoft Office Excel, you will be using the objects provided by the Excel object model. The object model is a large hierarchy of all the objects used in VBA. When you use vba in an Office Application, say PowerPoint, a reference to the PowerPoint Object Library is set by default. When you Automate to work with PowerPoint objects from another application, say Excel, you can add a reference to the PowerPoint object library in Excel (your host application) by clicking Tools-References in VBE, which will enable using PowerPoint's predefined constants - the PowerPoint objects, properties, and methods will appear in the Object Browser and the syntax will be checked at compile time.

 

All objects, and their associated Properties and Methods, available in Excel VBA can be viewed in the Object Browser in the VBE code window - in VBE click on View>Object Browser or press F2. On the top-left of the window is the Project/Library box, which by default mentions <All Libraries>, wherein you can choose Excel from the list to view all Excel objects. On the left pane of the window the available Classes (Objects) are listed, and on the right pane are displayed all Members (ie. properties, methods, events & constants) associated with the selected object in the Classes list. Global members (ie. properties, methods, events & constants) are those in respect of which the object name can be omitted. In the Excel Object Library, those properties and methods whose use does not require specifying the Application object qualifier are considered "global". Refer Image 1 to view the Object Browser.

 

 

The Object Model of the Application (Excel) refers to and contains its programming objects which are related to each other in a hierarchy. The entire Excel application is represented by the Application Object which is at the top of the Excel object hierarchy and moving down you can access the objects from Application to Workbook to Worksheet to Range (Cells) and further on, by connecting the objects with a period (dot). Excel objects are accessed through 'parent' objects - Worksheet is the parent of the Range Object, and the Workbook is the parent of the Worksheet object, and the Application object is the parent of the Workbook object.

 

Example - Start at the top of the hierarchy with the Application object, then move down to the workbook, worksheet, range and font objects, as follows:

Application.ActiveWorkbook.Worksheets(1).Range("A1").Font

 

The Excel Object Model hierarchy - the most used objects:

 

Excel Objects Hierarchy
 
Application Object
Workbook Object
Worksheet Object
Range Object

 

 

 

 

 

 

 

 

 

 

The Application Object refers to the host application of Excel, and the entire Excel application is represented by it. The Workbook Object, appears next below the Application Object in Excel object hierarchy, and represents a single workbook within the Excel application. A workbook is also referred to as an Excel file. The Workbooks Collection Object includes all currently open Workbooks in Excel. The Worksheet Object, appears next below the Workbook Object in Excel object hierarchy, and represents a single worksheet within the workbook. The Worksheets Collection Object includes all Worksheets in a workbook. A Range Object refers to a cell or a range of cells. It can be a row, a column or a selection of cells comprising of one or more rectangular / contiguous blocks of cells (when the Range is a union of multiple blocks of cells it is referred as a non-contiguous range of cells). The Range object is usually used maximum within the Excel application.

 

The Application object is the Default Object, Excel assumes it even when it is not specified. The Application qualifier is mostly not required to be used in vba code, because the default application is Excel itself, unless you want to refer to other outside applications (like Microsoft Word or Access) in your code or you want to refer to Excel from another application like Microsoft Word. In your VBA code, both the expressions Application.ActiveWorkbook.Name and ActiveWorkbook.Name will have the same effect of returning the Active Workbook's name. However, there are some instances when the Application qualifier is required to be used, viz. generally when using properties & methods which relate to the Excel window's appearance, or which relate to how the excel application behaves as a whole.

 

 

The Active Object

 

If no Workbook or Worksheet is specified, Excel refers to the current Active Workbook or Worksheet by default. In your vba code you can also refer the current Active Workbook or Sheet as ActiveWorkbook or ActiveSheet. Both the expressions Worksheets(1).Name and ActiveWorkbook.Worksheets(1).Name will return the name of the first worksheet in the Active Workbook which also becomes the default object in this case. Similarly, both the expressions Range("A1").Value = 56 and ActiveSheet.Range("A1").Value = 56 will enter the value 56 in cell A1 of the Active Worksheet in the Active Workbook. This is a general rule that omitting reference to a Workbook or Worksheet refers to the current Active Workbook or Worksheet by default, but this rule is subject to below conditions.

 

Note: (i) omitting reference to a Worksheet when your vba code is entered in Sheet Modules (viz. Sheet1, Sheet2, ...) will reference the specific sheet in whose module your code is entered and NOT the Active Sheet; and (ii) omitting reference to a Workbook when your vba code is entered in the Workbook module (ThisWorkbook) will reference the workbook in which your code is entered and NOT the Active Workbook. This means: (i) omitting reference to a Worksheet will default to ActiveSheet when your vba code is entered in Standard Code Modules (Module1, Module2, …) or the Workbook module (ThisWorkbook) and NOT when your vba code is entered in Sheet Modules (viz. Sheet1, Sheet2, ...) or UserForms or any Class modules you create; and (ii) omitting reference to a Workbook will default to ActiveWorkbook when your vba code is entered in Standard Code Modules (Module1, Module2, …) or in the Sheet Modules (viz. Sheet1, Sheet2, ...) and NOT when your vba code is entered in the Workbook module (ThisWorkbook).

 

 

 

Access an Object / Access a Single Object from its Collection

 

Access a Workbook Object

 

Workbook Object and Workbooks Collection: All open Workbook objects in Excel are referred to as the Workbooks collection. You can access a single Workbook from the Workbooks Collection by using the workbook index. This index is either the workbook name or an index number, and is used as Workbooks(index). To activate a workbook named "VbaProject", use Workbooks("VbaProject").Activate. To activate the first workbook, use Workbooks(1).Activate. The index number starts at 1, which indicates the first workbook which is created or opened, and the last workbook number will be returned by Workbooks.Count (which counts the number of open workbooks). The activate the second workbook use Workbooks(2).Activate, to activate the last workbook use Workbooks(Workbooks.Count).Activate.

 

 

Access a Worksheet Object

 

Worksheet Object and Worksheets Collection: A Worksheets Collection object refers to all Worksheets contained in a workbook. Similar to a Workbook object, you can access a single Worksheet from the Worksheets Collection by using the worksheet index. The index can be the worksheet name or an index number, and is used as Worksheets(index). To activate a worksheet named "Sheet1", use Worksheets("Sheet1").Activate. To activate the first worksheet, use Worksheets(1).Activate. The index number starts at 1, which indicates the first worksheet, and the last worksheet number will be returned by Worksheets.Count (which counts the number of worksheets in a workbook). The activate the second worksheet use Worksheets(2).Activate, to activate the last worksheet use Worksheets(Worksheets.Count).Activate.

 

 

Access a Sheet Object

 

Sheet Object and Sheets Collection: A Sheets Collection object refers to all sheets contained in a workbook, which includes chart sheets and worksheets. You can access a single Sheet from the Sheets Collection by using the sheet index viz. Sheets(index), similar to accessing a Worksheet. The Sheet index can be the sheet name viz. Sheets("Sheet1").Activate, or index number viz. Sheets(1).Activate or Sheets(Sheets.Count).Activate.

 

 

Access a Range Object

 

A Range Object refers to a cell or a range of cells. It can be a row, a column or a selection of cells comprising of one or more rectangular / contiguous blocks of cells. You can refer to a range by using the following expressions.

 

Referencing a single cell:

 

Enter the value 10 in the cell A1 of the worksheet named "Sheet1":

Worksheets("Sheet1").Range("A1").Value = 10

 

Enter the value of 10 in range C2 of the active worksheet - using Cells(row, column) where row is the row index and column is the column index:

ActiveSheet.Cells(2, 3).Value = 10

 

Referencing a range of cells:

 

Enter the value 10 in the cells A1, A2, A3, B1, B2 & B3 (wherein the cells refer to the upper-left corner & lower-right corner of the range) of the active sheet:

ActiveSheet.Range("A1:B3").Value = 10

ActiveSheet.Range("A1", "B3").Value = 10

ActiveSheet.Range(Cells(1, 1), Cells(3, 2)) = 10

 

Enter the value 10 in the cells A1 & B3 of worksheet named "Sheet1":

Worksheets("Sheet1").Range("A1,B3").Value = 10

 

Set the background color (red) for cells B2, B3, C2, C3, D2, D3 & H7 of worksheet named "Sheet3":

ActiveWorkbook.Worksheets("Sheet3").Range("B2:D3,H7").Interior.Color = vbRed

 

Enter the value 10 in the Named Range "Score" of the active worksheet, viz. you can name the Range("B2:B3") as "Score" to insert 10 in the cells B2 & B3:

Range("Score").Value = 10

ActiveSheet.Range("Score").Value = 10

 

Select all the cells of the active worksheet:

ActiveSheet.Cells.Select

Cells.Select

 

Set the font to "Times New Roman" & the font size to 11, for all the cells of the active worksheet in the active workbook:

ActiveWorkbook.ActiveSheet.Cells.Font.Name = "Times New Roman"

ActiveSheet.Cells.Font.Size = 11

Cells.Font.Size = 11

 

Referencing Row(s) or Column(s):

 

Select all the Rows of active worksheet:

ActiveSheet.Rows.Select

 

Enter the value 10 in the Row number 2 (ie. every cell in second row), of worksheet named "Sheet1":

Worksheets("Sheet1").Rows(2).Value = 10

 

Select all the Columns of the active worksheet:

ActiveSheet.Columns.Select

Columns.Select

 

Enter the value 10 in the Column number 3 (ie. every cell in column C), of the active worksheet:

ActiveSheet.Columns(3).Value = 10

Columns("C").Value = 10

 

Enter the value 10 in Column numbers 1, 2 & 3 (ie. every cell in columns A to C), of worksheet named "Sheet1":

Worksheets("Sheet1").Columns("A:C").Value = 10

 

Relative Referencing:

 

Inserts the value 10 in Range C5 - reference starts from upper-left corner of the defined Range:

Range("C5:E8").Range("A1") = 10

 

Inserts the value 10 in Range D6 - reference starts from upper-left corner of the defined Range:

Range("C5:E8").Range("B2") = 10

 

Inserts the value 10 in Range E6 - offsets 1 row & 2 columns, using the Offset property:

Range("C5").Offset(1, 2) = 10

 

Inserts the value 10 in Range("F7:H10") - offsets 2 rows & 3 columns, using the Offset property:

Range("C5:E8").Offset(2, 3) = 10

 

 

 

Properties and Methods of Objects

 

As explained above, to manipulate an Object you can Set its Properties and Call its Methods.

 

To access the property of an object, connect the Object Name to the Property by inserting a period (full stop or dot) between them viz. Worksheets(1).Name, returns the name of the first worksheet. Some objects have default properties viz. a Range object's default property is Value and you can omit to mention Value. In this case using Range("A1").Value or only  Range("A1") is the same and will return the value or content of the Cell A1, and the expressions can be used alternatively. Properties can be: (i) a Read-only property, which means you can read or access but cannot change it; or (ii) a Read-write property, in which case your VBA code can both read or change value.

 

To access the method of an object, connect the Object Name to the Method by inserting a period (full stop or dot) between them viz. Worksheets(1).Activate, activates the first worksheet by calling the Activate method. A Method may or may not have argument(s). An argument is a value supplied to a method to enable it to perform an action. To use the Calculate & Activate Method on a Worksheet object, you need not supply an argument viz. Worksheets("Sheet1").Calculate or Worksheets("Sheet1").Activate. To use the Add Method on a Worksheets Collection Object, you need to supply multiple arguments.

 

An Object's Method is a procedure that acts on it. A Method can have Arguments which are required to be specified and/or it can have Optional Arguments which you can omit to specify. Arguments which are displayed in square brackets in the method's syntax, are optional while others are required. The arguments can be supplied in the order of the position in which they are defined in the method syntax, each argument value being separated with a comma even for optional arguments which may not be specified. Alternatively the arguments can be supplied by the argument name (referred as named arguments) in which case the position in which they are specified becomes irrelevant. Each Named argument will also be separated with a comma, but not for optional arguments which are not specified. While specifying named arguments, you specify the argument name followed by a colon and an equal sign (:=) which is followed by the argument value, viz. ArgumentName:= "ArgumentValue". Using named arguments will facilitate keeping a track of the arguments which have been specified and those which have been omitted.

 

Examples of using an Object's Method:

 

The Activate Method applied to a Worksheet Object, activates the specified worksheet and makes it current. This method has no argument(s).

Worksheets("Sheet1").Activate

 

The Add Method applied to a Worksheets Collection Object, creates a new worksheet. It has multiple arguments, all of which are Optional. Syntax: Worksheets.Add(Before, After, Count, Type).

 

Using the Add Method without specifying any argument - adds a new worksheet before the Active Worksheet because both the Before & After arguments are omitted (note that the default value of Count argument is 1):

Worksheets.Add

 

Using the Add Method specifying one named argument of After - adds a new worksheet after the Worksheet named "Sheet2" (note that the default value of Count argument is 1):

Worksheets.Add After:=Worksheets("Sheet2")

 

Using the Add Method specifying two named arguments of After & Count - adds 3 new worksheets after the Worksheet named "Sheet2":

Worksheets.Add After:=Worksheets("Sheet2"), Count:=3

 

Using the Add Method specifying two positional arguments of After & Count - adds 2 new worksheets after the Worksheet named "Sheet2":

Worksheets.Add , Worksheets("Sheet2"), 2

 

Using the Add Method specifying two positional arguments of Before & Count - adds 2 new worksheets before the Worksheet named "Sheet2":

Worksheets.Add Worksheets("Sheet2"), , 2

 

Using the Add Method specifying one named argument of Before - adds a new worksheet before the Worksheet named "Sheet2", and using the Name property, names the new worksheet "NewSheet":

Worksheets.Add(Before:=Worksheets("Sheet2")).Name = "NewSheet"

 

 

 

Working with Objects in Excel VBA

 

Excel VBA IntelliSense

 

 

While writing vba code, when you type an Object followed by the period (dot), all the methods and properties of the object will appear in a pop-up list (Excel VBA IntelliSense). Ensure that the Excel VBA IntelliSense is turned on: in VBE, Tools>Options>Editor, 'Auto List Members' should be selected/checked. For instance, the IntelliSense will pop up after you type range followed by a period viz. range.[Intellisense for a Range object Pops Up]. Refer Image 2 - properties of the Range object are indicated by the fingers and methods of the Range object are indicated by the green boxes/bricks. You can either type or else select from this pop-up list, the method or property you want to connect with the object.

 

 

Using With…End With Statement to refer to Objects

 

As explained earlier, to access an Object and its properties & methods, you have to use the object name. In your vba code you will often need to refer to an object multiple times, and each time you will have to use its name. Instead of using the object name every time, you can execute multiple code lines which repeatedly refer to an object, by using the With…End With Statement. You start the block with the first line as:- type the With keyword followed by the Object Name. Insert one or more code lines after the first line:- access the object's members (its properties, methods, etc) by typing a period (dot) followed by the property or method name, and you need not specify the object name each time. Terminate the block with the end line:- "End With". Refer to the below example, which shows how to make your vba code more readible using a With Block, and with Nesting ie. Block within a Block:

 

'use the With ... End With statement to refer to a Range object
With Worksheets("Sheet1").Range("A1")

'use the Value property of the Range object, to set the value for the range:
.Value = 11
'use the Name property, of the Range object, to set the range name:
.Name = "Score"

'use the Font Property of the Range object that returns a Font object, and then use the With ... End With statement to refer to the Font object
With .Font

'note that because you are using the With ... End With statement to refer to the Font object within the Range object, you will not refer to both the range or font objects below:
'use the Name property of the Font object to set the font name:
.Name = "Arial"
'use the Bold property of the Font object to set the font to bold:
.Bold = True
'use the Color property of the Font object to set the font color:

.Color = vbRed

End With

'use the Borders property of the Range object to return all four borders (Borders collection object), and then use the LineStyle property of the Borders object to add a double border:
.Borders.LineStyle = xlDouble

'the Clear Method of the Range object, clears the range (clears the contents, formulas and formatting):

.Clear

End With

 

 

Using Variables in VBA

 

A variable is a named storage location used to store temporary values or information for use in execution of the code. In your vba program, a variable stores data and its content is used or changed later while executing the code. By declaring a variable for use in your code, you tell the Visual Basic compiler the variable's data type (type of value it represents viz. integer, decimal, text, boolean, etc.) and other information such as its scope/level (what code can access it - variables can be Procedure Level, Module Level or can have a Public scope). Variables must be explicitly declared using the Dim, Private, Public, ReDim, or Static statements. When you declare variables by using a Dim statement (Dim is short for dimension): for declaring a variable to hold an Integer value, use "Dim rowNumber As Integer"; for declaring a variable to hold text values, use "Dim strEmployeeName As String"; and so on.

 

 

Keywords in VBA

 

Keywords are reserved words that VBA uses as part of its programming language. Keywords are words or commands that are recognized by VBA and can be used in vba code only as part of the vba language (like in a statement, function name, or operator) and not otherwise (like sub-procedure or variable names). Examples of keywords are: Sub, End, Dim, If, Next, And, Or, Loop, Do, Len, Close, Date, ElseIf, Else, Select, and so on. To get help on a particular keyword, insert your mouse cursor within the keyword (in your vba code in VBE ) and press F1. Note that Keywords get capitalized in the vba code indicating that they have been written correctly viz. typing next will automatically appear as Next.

 

 

Assign an Object to a Variable, using the Set Keyword

 

In VBA, you use the Set keyword to assign an object reference. To assign an object to a variable in your vba code, you need to use the Set keyword as shown below. Note that using the Dim, Private or Public statements you only declare a variable as to its data type (type of value it represents viz. integer, text, etc.) and other information such as its scope/level (what code can access it). The actual object is assigned or referred to it only by using the Set statement. It is shorter to use an object variable & also more convenient because its data type will be known by VBA so that when you type the variable followed by the period (dot), all the methods and properties of the object will appear in a pop-up list (Excel VBA IntelliSense).

 

Example: With the following code, we declare a variable (myRange) of Range data type, and then assign the Range object to this variable using the Set keyword, so that every time you want to refer to the specific Range, you can do so by using the variable. The following will enter the value 10 in cells A1 to C3 in the worksheet named "Sheet1".

 

Dim myRange As Range

Set myRange = Worksheets("Sheet1").Range("A1:C3")
myRange.Value = 10