Excel VBA Objects,Methods and Properties

What are VBA Objects,Methods and Properties

Most programming languages deals with objects, a concept called object oriented programming. Excel VBA is not a truly object oriented programming language, but deal with objects.

----------------------------------------------------------------------
Objects : 

Objects are the fundamental building blocks of Visual Basic. An object is a special type of variable that contains both data and codes. A collection is a group of objects of the same class.

Workbooks is a collection of all Workbook objects.  Worksheets is a collection of Worksheet objects.

The Workbook object represents a workbook, the Worksheet object represents a worksheet, the Sheet object represents a worksheet or chartsheet, and the Range object represents a range of cells.

Example :
An Excel Worksheet is an object, cell in a worksheet is an object, range of cells is an object, font of a cell is an object, a command button is an object, and a text box is an object and more…

The following figure shows some objects:

How to refer Workbook and Worksheet Objects in VBA :

The workbook is the same as an Excel file.  The Workbook collection contains all the workbooks that are currently opened.  Inside of a workbook contains at least one worksheet.

In VBA, a worksheet can be referenced as followed:

Worksheets("Sheet3")

Worksheets("Sheet3") is the worksheet that named "Sheet3."Another way to refer to a worksheet is to use number index like the following:

Worksheets(3)

The above refers to the first worksheet in the collection.  

Remainder:

Worksheets(1) is not necessary the same sheet as Worksheets("Sheet1").

Sheets is a collection of worksheets and chart sheets.  A sheet can be indexed just like a worksheet.  Sheets(1) is the first sheet in the workbook.

To refer sheets (or other objects) with the same name, you have to qualify the object as follows

Workbooks("Book1").Worksheets("Sheet3")

Workbooks("Book2").Worksheets("Sheet3")

Range Object and Cells Property:
Range represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-D range. We will show you some examples on how Range object can be used.

The following example places text "AB" in range A1:B5, on Sheet2.

Worksheets("Sheet2").Range("A1:B5") = "AB"

Note that, Worksheets.Range("A1", "B5") = "AB" will yield the same result as the above example.The following place "AAA" on cell A1, A3, and A5 on Sheet2.

Worksheets("Sheet2").Range("A1, A3, A5") = "AAA"

Range object has a Cells property. This property is used in every VBA projects on this website (very important). The Cells property takes one or two indexes as its parameters. 

Example:

Cells(index) or Cells(row, column)

Here row is the row index and column is the column index.The following three statements are interchangeable:

ActiveSheet.Range.Cells(1,1)
Range.Cells(1,1)
Cells(1,1)

The following returns the same outcome:

Range("A1") = 123 and Cells(1,1) = 123

Range object has an Offset property that can be very handy when one wants to move the active cell around. The following examples demonstrate how the Offset property can be implemented (assume the current cell before the move is E5):

ActiveCell.Offset(1,0) = 1  'Place a "1" one row under E5 (on E6)

ActiveCell.Offset(0,1) = 1  'Place a "1" one column to the right of E5 (on F5)

ActiveCell.Offset(0,-3) = 1 'Place a "1" three columns to the left of E5 (on B5)

----------------------------------------------------------------------
METHODS and PROPERTIES:
Each object contains its own methods and properties.A Property represents a built-in or user-defined characteristic of the object. A method is an action that you perform with an object. Below are examples of a method and a property for the Workbook Object:

Workbooks.Close 

Close method close the active workbook

Workbooks.Count

Count property returns the number of workbooks that are currently opened.Some objects have default properties. For example, Range's default property is Value. 

The following yields the same outcome. 

Range("A1") = 1 and Range("A1").Value = 1

Set and to get a Range property value:
The following sets the value of range A1 or Cells(1,1) as "2005". It actually prints "2005" on A1.

Range("A1").Value = 2005

The following gets the value from range A1 or Cells(1,1).

X = Range("A1").Value

Method can be used with or without argument(s). 

Examples:

Methods That Take No Arguments:

Worksheets("Sheet").Column("A:B").AutoFit

Methods That Take Arguments:

Worksheets("Sheet1").Range("A1:A10").Sort
Worksheets("Sheet1").Range("A1")
Here the Range("A1:A10") is sort by Worksheets("Sheet1").Range("A1") is the Key (or column) .

Thanks, TAMATAM