Lesson 17 Excel VBA Methods

An Excel VBA 365 object contains methods. A method usually performs certain operations. For example, ClearContents is a method that clears the contents of a cell or a range of cells.

Example 17.1

'Clear contents from cells A1 to A6

Private Sub CommandButton1_Click()
 Range("A1:A6").ClearContents
End Sub

For example, you can let the user select his/her own range of cells and clear the contents by using the InputBox function, as shown in Example 17.2

Example 17.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

However, if you only want to clear the formats of an entire worksheet, you can use the following syntax:

Sheet1.Cells.ClearFormats

In order 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 as follows:

Range("A1:A5").Select
Example 17.3
Private Sub CommandButton1_Click()
 Range("A1:A5″).Select
End Sub

Example 17.4

The following 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 17.5

In this example, we insert two command buttons. The first button is used to select the range while the second button 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. The items may include numbers, days of the week, months of year and more. The syntax is

Expression.AutoFill(Destination, Type)

*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 the type of series, such as days of the week, the month of year and more. The AutoFill type constant is something like xlFillWeekdays, XlFillDays, XlFillMonths and more.

Example 17.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 17.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 17.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