Excel VBA Objects

Most programming languages today deal with objects, a concept called object oriented programming. Although Excel VBA is not a truly object oriented programming language, it does deal with objects. VBA object is something like a tool or a thing that has certain functions and properties, and can contain data. For 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.

An Excel VBA object has properties and methods. Properties are like the characteristics or attributes of an object. For example, Range is an Excel VBA object and one of its properties is value. We connect an object to its property by a period(a dot or full stop). The following example shows how we connect the property value to the Range object.


Example
Private Sub CommandButton1_Click()
Range("A1:A6").Value = 10
End Sub

In this example, by using the value property, we can fill cells A1 to A6 with the value of 10. However, because value is the default property, it can be omitted.

Example
This example allows the user to specifies the range of cells to be seleted.

Private Sub CommandButton1_Click()
Dim selectedRng As String
selectedRng = InputBox("Enter your range")
Range(selectedRng).Select
End Sub

Example
This example allows the user to select the range of cells to be automatically filled using the Autofill method. This can be achieved with the use of the InputBox. Since each time we want to autofill a new range, we need to clear the contents of the entire worksheet using the Sheet1.Cells.ClearContents statement.

Private Sub CommandButton1_Click()
Dim selectedRng As String
Sheet1.Cells.ClearContents
selectedRng = InputBox("Enter your range")
Range("A1") = 1
Range("A2") = 2
Range("A1:A2").AutoFill Destination:=Range(selectedRng)
End Sub