<<Lesson 17>> [Contents] <<Lesson 19>>
In lesson 13, we have learned the concepts of Excel VBA objects. You have also learned that an Excel VBA object has properties and methods. We have already dealt with properties and we shall learn about methods in this lesson.
18.1: Methods
A method of an Excel VBA object normally do something or perform certain operations. For example, ClearContents is a method of the range object that clears the contents of a cell or a range of cells. For example, You can write the following code to clear the contents of certain range:
Example 18.1
‘Clear contents from cells A1 to A6
Private Sub CommandButton1_Click()
Range(“A1:A6”).ClearContents
End Sub
You can also let the user select his own range of cells and clear the contents by using the InputBox function, as shown in Example 18.2
Example 18.2
Private Sub CommandButton1_Click()
Dim, selectedRng As String
selectedRng = InputBox(“Enter your range”)
Range(selectedRng).ClearContents
End Sub
In order to clear the contents of the entire worksheet, you can use the following code:
Sheet1.Cells.ClearContents
But if you only want to clear the formats of an entire worksheet, you can use the following syntax:
Sheet1.Cells.ClearFormats
To select a range of cells, you can use the Select method. This method selects a range of cells specified by the Range object. The syntax is
Range(“A1:A5”).Select
Example 18.3
Private Sub CommandButton1_Click()
Range(“A1:A5”).Select
End Sub
Example 18.4
This example allows the user to specifies the range of cells to be selected.
Private Sub CommandButton1_Click()
Dim selectedRng As String
selectedRng = InputBox(“Enter your range”)
Range(selectedRng).Select
End Sub
To deselect the selected range, we can use the Clear method.
Range(“CiRj:CmRn”).Clear
Example 18.5
In this example, we insert two command buttons, the first one is to select the range and the second one is to deselect the selected range.
Private Sub CommandButton1_Click()
Range(“A1:A5”).Select
End Sub
Private Sub CommandButton2_Click()
Range(“A1:A5”).Clear
End Sub
Instead of using the Clear method, you can also use the ClearContents method.
Another very useful method is the Autofill method. This method performs an autofill on the cells in the specified range with a series of items including numbers, days of the week, months of year and more. The syntax is
Expression.AutoFill(Destination, Type)
Where Expression can be an object or a variable that returns an object. Destination means the required Range object of the cells to be filled. The destination must include the source range. Type means a type of series, such as days of the week, a month of the year and more. The AutoFill type constant is something like xlFillWeekdays, XlFillDays, XlFillMonths and more.
Example 18.6
Private Sub CommandButton1_Click()
Range(“A1”)=1
Range(“A2”)=2
Range(“A1:A2”).AutoFill Destination:=Range(“A1:A10”)
End Sub
In this example, the source range is A1 to A2. When the user clicks on the command button, the program will first fill cell A1 with 1 and cell A2 will 2, and then automatically fills the Range A1 to A10 with a series of numbers from 1 to 10.
Example 18.7
Private Sub CommandButton1_Click()
Cells(1, 1).Value = “Monday”
Cells(2, 1).Value = “Tuesday”
Range(“A1:A2”).AutoFill Destination:=Range(“A1:A10”), Type:=xlFillDays
End Sub
Example 18.8
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
