Cách tính Range trong Excel

Trong bài này mình sẽ giới thiệu đến đối tượng Range trong VBA, Range là đối tượng được sử dụng nhiều nhất khi lập trình VBA bởi nó giúp ta trao đổi dữ liệu cũng như thiết lập thuộc tính cho các cell.

Bài viết này được đăng tại freetuts.net, không được copy dưới mọi hình thức.

Ở bài trước mình đã giới thiệu sơ lược về Workbook và Worksheet, và bạn cũng đã biết mỗi workbook chứa nhiều worksheet, mỗi worksheet chứa nhiều range. Và đúng ra mình muốn gộp nội dung bài này vào bài trước luôn, nhưng vì nó quá dài nên mình tách ra để các bạn đọc dễ hiểu hơn.

1. Range trong VBA là gì?

Range là đối tượng dùng để tham chiếu đến một vùng chọn trong Excel. Nếu trong Excel ta dùng chuột để tạo vùng chọn thì trong VBA lại khác, vì nó là ngôn ngữ máy tính nên cần sử dụng đối tượng Range.

Về cú pháp tham chiếu vùng chọn thì không khác gì trong Excel, ta vẫn sử dụng tham chiếu CộtHàng.

Bài viết này được đăng tại [free tuts .net]

Tham chiếu đến ô B3.

Tham chiếu đến dãy ô từ A1 đến F10.

2. Cách sử dụng Range trong VBA

Ta sẽ làm một vài ví dụ để bạn hiểu rõ hơn về đối tượng này nhé.

Trước tiên hãy tạo một Command Buttons, sau đó viết những đoạn code sau trong sự kiện click của button đó.

Ví dụ 1: Thiết lập giá trị cho ô B3 là 2

Kết quả khi bạn click vào button như sau:

Ví dụ 2: Thiết lập giá trị cho dãy ô từ A1:A4 là 5

Kết quả

Ví dụ 3: Thiết lập giá trụ của hai dãy A1:A2 và B3:C4 là 10

Range["A1:A2,B3:C4"].Value = 10

Kết quả

Lưu ý: Nếu bạn đã đặt Named Range cho vùng chọn thì có thể sử dụng named range để thay thế.

Range["Prices"].Value = 15

3. Kết hợp đối tượng Cells và Range

Ngoài cách sử dụng đối tượng Range thì bạn có thể sử dụng Cells để thay thế. Tham số truyền vào Cells là số thứ tự của hàng và cột.

Cells [Số thứ tự hàng, Số thứ tự cột]

Ví dụ 1: Thiết lập giá trị ô B3 là 2.

Ta thấy ô B3 thuộc hàng 3 và cột 2 nên cách làm như sau:

Vi dụ 2: Thiết lập giá trị từ A1 đến A4 là 5.

• A1 thuộc hàng 1 cột 1 => Cells [1, 1]
• A4 thuộc hàng 4 cột 1 => Cells [4, 1]

Range[Cells[1, 1], Cells[4, 1]].Value = 5

Kết quả:

4. Khai báo một biến chứa đối tượng Range

Ta sử dụng từ khóa Dim và Set để khai báo một biến chứa đối tượng Range.

// Khai báo biến Example kiểu Range Dim example As Range // Thiết lập giá trị cho biến example Set example = Range["A1:C4"] // Lúc này biến example chính là Range["A1:C4"] example.Value = 8

Kết quả:

5. Phương thức Select của Range Object

Đối tượng Range có một phương thức tên là Select, khi chạy phương thức này thì nó giống như bạn dùng chuột để kéo vùng chọn vậy.

Ví dụ: Viết chức năng khi click vào Button thì tạo ra một vùng chọn A1:C4.

Dim example As Range Set example = Range["A1:C4"] example.Select

Kết quả:

* Lưu ý: Để chọn các ô trên một trang tính khác thì trước tiên bạn phải kích hoạt trang tính đó.

Ví dụ: Các dòng code dưới đây chọn ô B7 trên trang tính thứ ba tính từ bên trái.

// Chọn trang tính Worksheets[3].Activate Worksheets[3].Range["B7"].Select // Tạo vùng chọn Dim example As Range Set example = Range["A1:C4"] example.Select

6. Phương thức Rows của Range

Phương thức Rows có một tham số truyền vào, đó chính là hàng mà bạn muốn chọn trong phạm vi của đối tượng Range.

Dim example As Range Set example = Range["A1:C4"] example.Rows[3].Select

Kết quả:

Như bạn thấy, hàng thứ 3 đã được chọn và độ dài của nó nằm trong phạm vi của Range.

7. Phương thức Columns của Range

Tương tự như phương thức Rows, phương thức Columns sẽ có một tham số và đó chính là số thứ tự của hàng mà bạn muốn chọn trong phạm vi của Range.

Dim example As Range Set example = Range["A1:C4"] example.Columns[2].Select

Kết quả:

8. Phương thức Copy và Paste của Range

Phương thức Copy dùng để copy một dãy range, và phương thức Paste dùng để dán vào một vị trí nào đó trong Worksheet.

Range["A1:A2"].Select Selection.Copy Range["C3"].Select ActiveSheet.Paste

Kết quả:

Mặc dù đoạn code trên chạy tốt trong Excel VBA, nhưng tốt hơn nữa là bạn nên sử dụng đoạn code bên dưới để thay thế.

Range["C3:C4"].Value = Range["A1:A2"].Value

8. Phương thức ClearContents của Range

Phương thức ClearContents dùng để xóa nội dung của Range.

Range["A1"].ClearContents

Hoặc bạn cũng có thể sử dụng đoạn code sau để thay thế:

Sự khác biệt duy nhất giữa hai đoạn code trên là: ClearContents sẽ xóa nội dung và các định dạng format của vùng chọn, còn đoạn code thứ hai chỉ xóa nội dung mà thôi.

9. Phương thức Count của Range

Phương thức Count dùng để đếm tổng số các ô của range.

TH1: Đếm tổng số các ô trong vùng chọn.

Dim example As Range Set example = Range["A1:C4"] MsgBox example.Count

TH2: Đếm số hàng [Rows]

Dim example As Range Set example = Range["A1:C4"] MsgBox example.Rows.Count

TH3: Đếm số cột [Columns]

Dim example As Range Set example = Range["A1:C4"] MsgBox example.Columns.Count

Và kết quả thu về là 3.

Trên là cách sử dụng đối tượng Range trong Excel VBA và những phương thức thường sử dụng nhất trong lập trình VBA. Bài này mình sẽ dừng ở đây, hẹn gặp lại bạn ở bài tiếp theo nhé.

Sub Test[] Dim i As Long, Rng As Range, rngAddr[] rngAddr = Array["$A$1:$A$3", " $A$5:$A$7", " $A$9:$A$11", " $A$13:$A$15", " $A$17:$A$19", _ " $A$21:$A$23", " $A$25:$A$27", " $A$29:$A$31", " $A$33:$A$35", " $A$37:$A$39", _ " $A$41:$A$43", " $A$45:$A$47", " $A$49:$A$51", " $A$53:$A$55", " $A$57:$A$59", _ " $A$61:$A$63", " $A$65:$A$67", " $A$69:$A$71", " $A$73:$A$75", " $A$77:$A$79", _ " $A$81:$A$83", " $A$85:$A$87", " $A$89:$A$91", " $A$93:$A$95", " $A$97:$A$99", _ " $A$101:$A$103", " $A$105:$A$107", " $A$109:$A$111", " $A$113:$A$115", " $A$117:$A$119", _ " $A$121:$A$123", " $A$125:$A$127", " $A$129:$A$131", " $A$133:$A$135", " $A$137:$A$139", _ " $A$141:$A$143", " $A$145:$A$147", " $A$149:$A$151", " $A$153:$A$155", " $A$157:$A$159", _ " $A$161:$A$163", " $A$165:$A$167", " $A$169:$A$171", " $A$173:$A$175", " $A$177:$A$179", _ " $A$181:$A$183", " $A$185:$A$187", " $A$189:$A$191", " $A$193:$A$195", " $A$197:$A$199"] Set Rng = Range[rngAddr[0]] For i = 1 To UBound[rngAddr] Set Rng = Union[Rng, Range[rngAddr[i]]] Next Rng.Select End Sub

Phần trước đã giới thiệu về tổng quan về Excel VBA, phần này xin giới thiệu với các bạn về một trong những đối tượng được sử dụng nhiều nhất trong khi tự động hóa bất kỳ tác vụ nào với VBA - Range.

Đối tượng Range

Chúng ta tham chiếu đến các Range khác nhau trong một Worksheet và thực hiện các hoạt động khác nhau trên Range trong Excel. Chúng ta có các thuộc tính và các phương thức khác nhau để xử lý các đối tượng Range trong Excel. Đối tượng Range là đại diện của một cell [hoặc cells] trên Worksheet, là đối tượng quan trọng nhất của Excel VBA.

Ví dụ 1: đặt một nút lệnh trên Worksheet và thêm dòng code sau:

Range["B3"].Value = 2

Kết quả:

Ví dụ 2: đặt một nút lệnh trên Worksheet và thêm dòng code sau:

Range["A1:A4"].Value = 88

Kết quả:

Đối tượng Cells

Thay vì Range, bạn có thể sử dụng Cells. Sử dụng Cells đặc biệt hữu ích khi bạn muốn lặp qua các ranges.

Ví dụ 3: đặt một nút lệnh trên Worksheet và thêm dòng code sau:

Cells[3, 2].Value = 2

Kết quả:

Ví dụ 4: đặt một nút lệnh trên Worksheet và thêm dòng code sau:

Range[Cells[1, 3], Cells[4, 3]].Value = 11

Kết quả:

Khai báo đối tượng Range

Bạn có thể khai báo một đối tượng Range bằng cách sử dụng các từ khoá Dim và Set.

Sub buttoRange1[] Dim rangeObj As Range Set rangeObj = Range["B1:E4"] rangeObj.Value = 8 End Sub

Kết quả:

Các phương thức của đối tượng Range

Một phương pháp quan trọng của đối tượng Range là phương thức Select. Phương thức Select được sử dụng để chọn một range. Ví dụ:

Dim rangeObj As Range Set rangeObj = Range["A1:C4"] rangeObj.Select

Kết quả:

Lưu ý: Để chọn các ô trên một Worksheet khác, bạn phải kích hoạt Worksheet này trước. Ví dụ, các dòng code sau đây chọn ô B7 trên Worksheet thứ ba từ bên trái.

Worksheets[3].Activate Worksheets[3].Range["B7"].Select

Phương thức Copy/Paste

Phương thức Copy và Paste sử dụng để sao chép một dãy và dán nó vào một nơi khác trên Worksheet.

Ví dụ:

Range["B1:B2"].Select Selection.Copy Range["D3"].Select ActiveSheet.Paste

Kết quả:

Mặc dù phương thức Copy/Paste được cho phép sử dụng trong Excel VBA, nhưng tốt hơn là bạn sử dụng dòng code dưới đây thay vì đoạn code trên.

Range["C3:C4"].Value = Range["A1:A2"].Value ActiveSheet.Paste

Phương thức ClearContents

Phương thức ClearContents được sử dụng để chỉ xóa nội dung của một Excel Range.

Ví dụ:

Range["A1"].ClearContents

Hoặc đơn giản hơn bằng cách sử dụng code sau:

Range["A1"].Value = ""

Phương thức ClearFormats

Phương thức ClearFormats được sử dụng để chỉ xóa định dạng của một Excel Range.

Phương thức Clear

Phương thức Clear được sử dụng để xóa một Excel Range hoàn toàn bao gồm các định dạng và dữ liệu.

Phương thức Formula

Phương thức Formula được sử dụng để thêm công thức cho Excel Range.

Ví dụ:

Range["A1"].Formula = "=Sum[B1:B5]"

Kết quả

Các thuộc tính của đối tượng Range

Thuộc tính Rows cho phép truy cập vào một hàng cụ thể của range.

Ví dụ:

Dim rangeObj As Range Set rangeObj = Range["A1:C4"] rangeObj.Rows[3].Select

Kết quả:

Thuộc tính Columns

Thuộc tính Columns cho phép truy cập vào một cột cụ thể của range.

Ví dụ:

Dim rangeObj As Range Set rangeObj = Range["A1:E4"] rangeObj.Columns[3].Select

Kết quả:

Thuộc tính Count

Thuộc tính Count được sử dụng để đếm số ô, hàng và cột của range.

Ví dụ 1:

Dim rangeObj As Range Set rangeObj = Range["A1:C4"] MsgBox rangeObj.Count

Kết quả:

Ví dụ 2:

Dim rangeObj As Range Set rangeObj = Range["A1:C4"] MsgBox rangeObj.Rows.Count

Kết quả: