Lesson 18: Excel VBA Methods

18.1: Methods

Besides having properties, Excel VBA objects also comprise methods. Methods are used to perform certain operations. For example, ClearContents is a method of the range object. It clears the contents of a cell or a range of cells.

Example 18.1

You can write the following Excel VBA code to clear the contents of a range of cells in a spreadsheet:

Private Sub CommandButton1_Click()

Range(“A1:A6”).ClearContents

End Sub

You can also write Excel VBA code to let the user select his or her 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 seleted.

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 week, months of year and more. The format is

Expression.AutoFill(Destination, Type)

Where Expression can be an object or a variable that returns and object. Destination means the required Range object of the cells to be filled. The destination must include the source range. Type means type of series, such as days of week, month of 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