Objects, Properties and Methods – VBA

Although VBA is not purely Object Oriented Programming language, it has the use of Objects. In this article, we are going to learn about Objects, Properties, and Methods in VBA.

So let us start learning.

What are Objects?

In the real world, everything is an object. A House, Window, Door, and the Car in the Garage.

In Excel, an object represents an element of the Application. We can think of a Workbook, Worksheet, Cell, Chart, Pivot Table, Report Table, etc. An Object once identified, can have its properties as well as methods.

We can also use a Collection (also an object) that contains several objects that might be of the same type. For Example, the Sheets Collection contains all the sheets objects of the workbook object. Similarly, a Forms Collection contains all the Form Objects in the Application.

What are Properties?

A Property is an attribute of an Object. Properties define object characteristics like size or color or aspects of the behaviors like visible or enabled. If we need to change the characteristics of an object then we must change its properties.

For instance, a Car is an object and it can have different properties like Model, Engine, Fuel, Mileage, etc.

Similarly, the objects in Excel like Forms or Worksheets have their set of properties.

We can set the value of the property using the following syntax.

ObjectName.Property=Value

For example, we can change the Caption of a Form with this statement.

Myform.Caption = "Welcome"

We cannot set the values of all the properties of an object. A Read Write property allows us to change its value as well as read it. Some properties are Read Only and we cannot change their value. For example, we can only read the Address of a Cell Object. The Address Property cannot be changed so it is Read Only.

Sub sample()
MsgBox Range("A1").Address
End Sub

When you run this procedure, it prints the address of cell A1.

What are the Methods of an Object?

Methods refer to the action that can be performed using an object. A method might require arguments. Arguments provide additional information to the method about how we want to execute that functionality with the object. For example, we can call a number of methods of Range Object like Selecting a Range, Copying a Range, etc. In this example, we would copy the active cell and paste it to the destination cell within the current worksheet. Copy method of the Range object is used to copy the Range Object. We can also pass the Destination argument to Copy method. It specifies the range where we want to paste the Range Object Value.

Use the object.method. The Dot operator will show a list of all methods and properties for Range Object in VBE.

Sub Paste_single_cell()
'calling the Copy method of Range Object
Range("A1").Copy Destination:=Range("C1")            
End Sub

When we run this sub procedure, the contents of cell A1 is copied and pasted to cell C1.

Example – Adding Successive Dates as Sheet Name using VBA

We would now write a Sub Procedure that asks for a Start date. The name of the first sheet updates as per the start date and then the successive sheets are named with coming dates.

Let us say we have the following Six Worksheets in our Workbook.

Use the following Sub Procedure.

Sub name_dates()
'Procedure to Add Successive Dates as Sheet Name
'input the start date from user
Dim start As Date
start = InputBox("Enter the start date:")


Dim ws As Worksheet     'ws is a Worksheet object helps us loop through all worksheets of workbook
    
Dim i As Integer        'i is used to get successive dates after start date
For Each ws In Sheets
    ws.Name = start + i     'assigning dates to each worksheet name
    i = i + 1
Next ws
End Sub

• Press Alt and F8 keys and choose the Macro named name_dates and click on the Run button.
• Enter the start date as 10-10-2002. (Or any other date that you wish)

• After you click Ok, all the sheet name update as per the start date.

This was all about Objects, Properties, and Methods in VBA.

Thank you for reading.