VBA Objects in Excel

Objects are created from the class. If we take some real-life examples then let’s say a Dog is a class where it has its properties and methods like breed, age, and color whereas we can say Dog1 or Dog2 are objects having different breeds, ages, and colors. We can simply say the object is a grouping of data and functions. In VBA “Collection” is a class where we can create its object using “New” so that we can use its in-built methods like Add, Remove and Count. 

Object Components

Properties 

It is used to read or write a value where we can read the value from the object or we can write a value to the object but we can’t update the value in the object

 

Methods

Some actions are performed by this method in the object collection is an object where we can perform many actions with the help of methods like Add, Count, and Remove. The following code is shown to add a value in the collection object using the “Add” method

 

Events

When an event occurs a function executes called events in the object. Events are like clicking a button, opening a workbook, or activating a worksheet. The following code is shown to display a message by “MsgBox” after an event Workbook_Open() takes place

 

Creating a VBA Object

To create an object we have to follow three steps

1. A variable should be declared
2. A new object should be created 
3. The new object should be assigned to the variable

These three steps can be done in many ways which are discussed in the following,

Using Dim with New

In a single line, we can declare, create and assign the object. We can’t use the declare variable to create multiple objects. In the following code, we are creating an object of Collection and storing it in a variable “Collection_12”.

 

Using Set with New

We are declaring the variable to store the object using Dim in one line than in another line we can create and assign the object.

 

Using Let 

It is used to assign a value to the variable of an inbuilt object like String, Long, Integer, or Double. Let is used to store the value whereas set is used to store the address. In the following code, a variable is assigned by a value and that variable is a data type of string which is also an inbuilt object.

 

When New is Not Required

When we want to create an object of a workbook or worksheet at that time we usually don’t use New without using New we can create an object.

 

In VBA there are four important objects,

• Application Object
• Workbook Object
• Worksheet Object
• Range Object
• Shape Object

Application Object

The entire Excel Application is referred to as Application Objects. The application object contains the workbook object which we can use to manipulate the data present in the application object. The following code is to change the color of the font to red in the application.

 

Workbook Object

All worksheets which are currently open in excel are referred to as workbook objects. We can use the workbook object to add a sheet to the workbook or to save the existing sheets in the workbook.

 

Worksheet Object

It refers to the present sheet of excel which is in the active state, we can use it to manipulate the active sheet. In the following code, we will change the orientation of the active sheet to the landscape.

 

Range Object

It is used to refer to a group of cells or a single cell so that we can manipulate it according to our desire. In the following code, we are selecting a range of cells from A1 to C6.

 

Shape Object

It is used to add a shape or to change the dimension of the active sheet. In the following code, a rounded rectangle shape is added in the active sheet where the value of the rounded portion of the rectangle will be 100 and 200 in the top right and left part whereas the value of the bottom right and left will be 50.