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
- A variable should be declared
- A new object should be created
- 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.
.png)
