Excel VBA 2010 Lesson 17: Introduction to Excel VBA 2010 Objects Part 2

 [Lesson 16][Table of Contents][Lesson 18]

In lesson 16, we have learned the concepts of Excel VBA 2010 objects. You have also learned that an Excel VBA 2010 object has properties and methods. We have already dealth with properties and we shall leaarn about methods in this lesson.

17.1: Methods

A method of an Excel VBA 2010 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 17.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 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

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 17.3

Private Sub CommandButton1_Click()
Range(“A1:A5″).Select
End Sub

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

 [Lesson 16][Table of Contents][Lesson 18]

 

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn