Đối tượng Range trong Excel VBA
Trong bài viết này, Blog Học Excel Online sẽ cùng các bạn tìm hiểu đối tượng Range trong Excel VBA. Đối tượng Range trong Excel VBA là đối tượng được sử dụng thường xuyên nhất khi bạn làm việc với VBA trong Excel.
Đối tượng Range trong Excel là gì?
Đối tượng Range trong Excel có thể là một ô (Cell), một vùng gồm nhiều ô (Cells), một hàng, hoặc một cột trên bảng tính Excel.
Cách khai báo và tham chiếu tới đối tượng Range trong Excel
Trong VBA, chúng ta có thể tham chiếu tới đối tượng Range của Excel như sau:
Dim myRange as Range
set myRange = Range("A1") 'Tham chiếu tới ô A1Nếu các bạn làm việc với nhiều Sheets trên bảng tính, thì chúng ta nên tham chiếu cụ thể hơn như sau:
Dim myRange as Range
set myRange = Sheets("Sheet1").Range("A1") 'Tham chiếu tới ô A1 của Sheet1Việc tham chiếu cụ thể và chi tiết tới đâu, tùy thuộc vào việc bạn đang làm việc với những đối tượng nào trong dự án lập trình VBA của mình. Bạn có thể tham khảo bài viết Hệ thống các đối tượng trong Excel VBA để có thể hiểu rõ hơn về vấn đề này.
Để khai báo và tham chiếu tới các vùng khác, các bạn có thể làm việc này tương tự như cách tham chiếu tới các vùng sử dụng trong một công thức Excel, có nghĩa là, các tham chiếu sau đây tới đối tượng Range đều hợp lệ:
Set myRange = Range("A1:C5") ' Tham chiếu tới vùng A1:C5
Set myRange = Range("A:A") ' Tham chiếu tới cột A
Set myRange = Range("1:1") ' Tham chiếu tới dòng thứ nhất
Cách tham chiếu tới đối tượng Range trong VBA như phía trên thuộc kiểu tham chiếu A1, ngoài ra, chúng ta còn có kiểu tham chiếu ít phổ biến hơn, gọi là tham chiếu R1C1.
Cách tham chiếu tới đối tượng Range thông qua Cells
Ngoài cách tham chiếu tới đối tượng Range trong Excel như trên, khi làm việc với đối tượng Range trong VBA, bạn còn có thể tham chiếu tới 1 vùng thông qua đối tượng Cells, bằng cách định nghĩa ô đầu tiên, phía trên bên tay trái và ô cuối cùng, phía dưới bên phải của vùng tham chiếu, ví dụ với tham chiếu và code VBA như sau
Range( Cells(1,1), Cells(5,4) ).Value = 234thì kết quả sẽ như sau:
Tất nhiên, bạn cũng có thể tham chiếu như sau
Range( Range("A1"), Range("D5") ).Value = 234Với kiểu tham chiếu như trên, thì bạn sẽ thấy rõ được rằng ô đầu tiên của vùng tham chiếu là A1, ô cuối cùng của vùng tham chiếu là D5
Ứng dụng của đối tượng Range trong Excel VBA
Trong bài viết này, mình sẽ không chủ động đi liệt kê hết các phương thức và thuộc tính của đối tượng Range trong Excel VBA. Nếu bạn muốn tìm hiểu các phương thức (Methods) và thuộc tính (Properties) của đối tượng này, hãy tham khảo cách sử dụng Object Browser trong trình soạn thảo VBA ở bài viết Hệ thống các đối tượng trong Excel VBA
Trong phần này, chúng ta sẽ đi tìm hiểu một số ví dụ ứng dụng của đối tượng Range vào công việc.
Xóa dòng thỏa mãn điều kiện
Trong ví dụ ứng dụng này, chúng ta cần xóa dòng mà cột B có dấu “x”. Trong ví dụ này, các bạn có thể dễ dàng viết ra đoạn code với vòng lặp như sau để có thể xóa dòng mà cột B có chứa dấu “x”, là dòng id = 0002, id = 0007 và id = 0008
Đoạn code như sau:
Sub xoa_dong_trong_1()
For i = 2 To 10
If Range("B" & i).Value = "x" Then
Range("B" & i).EntireRow.Delete
End If
Next i
End SubĐây là một đoạn code khá đơn giản, phải không các bạn? Tuy nhiên, khi chạy đoạn code này, bạn sẽ nhận được kết quả không như mong muốn, bởi vì giữa biến i và dòng cần xóa đã bị mất đồng bộ, khi xóa đi thì tham chiếu tới các dòng phía dưới cũng thay đổi dẫn đến việc xóa dòng không được chính xác nữa. Để khắc phục điều này, chúng ta cần tham chiếu tới các dòng / các vùng cần xóa từ dưới lên như sau:
Sub xoa_dong_trong_2()
For i = 10 To 2 Step -1
If Range("B" & i).Value = "x" Then
Range("B" & i).EntireRow.Delete
End If
Next i
End SubNhư vậy, qua ví dụ này, các bạn biết được thuộc tính .EntireRow và phương thức .Delete và ứng dụng của hai thuộc tính này trong việc chọn toàn bộ dòng và xóa dòng theo điều kiện.
Tô màu vùng dữ liệu thỏa mãn điều kiện
Cùng với dữ liệu như ở phần trên, chúng ta cùng đi thay đổi đề bài một chút, đó là hãy tô màu dòng mà cột B có giá trị là “x”. Để làm được điều này, chúng ta có rất nhiều cách khác nhau, đoạn code VBA sau đây là một cách để làm điều đó:
Sub highlight_1()
For i = 2 To 10
If Range("B" & i).Value = "x" Then
Range("B" & i).Offset(0, -1).Resize(1, 2).Interior.Color = RGB(0, 255, 0)
End If
Next i
End SubTrong đoạn code trên, với phương thức .Offset(0, -1) bạn di chuyển từ Range("B" & i) về Range("A" & i) sau đó sử dụng phương thức .Resize(1, 2) để chọn 2 ô ở cột A, cột B cùng ở dòng i, sau đó tô màu nền bằng .Interior.Color
Điền công thức vào Range với code VBA
Cũng với dữ liệu như ở ví dụ trên, chúng ta muốn điền một công thức vào trong ô B11 để đếm xem trong cột B có bao nhiêu giá trị “x” thì chúng ta có thể giải quyết đề bài này thế nào?
Đầu tiên, để đếm theo điều kiện, các bạn có thể sử dụng hàm COUNTIF vậy bước đầu tiên, chúng ta sẽ đi viết hàm COUNTIF trong ô B11 như sau:
=COUNTIF(B2:B10,"x")Với hàm COUNTIF như trên, bạn có thể chuyển thành code VBA để điền công thức vào một vùng sử dụng thuộc tính .Formula như sau:
Sub enter_formula_1()
Range("B11").Formula = "=COUNTIF(B2:B10,"x")"
End SubTuy nhiên, sau khi điền như vậy, bạn sẽ gặp lỗi:
Lý do cho lỗi này là, trong công thức có ký tự “x”, được bao ngoài bởi dấu ngoặc kép, dấu ngoặc kép này cũng là dấu báo cho VBA biết dữ liệu là kiểu String, do đó, VBA không biết đâu là chuỗi, đâu là những thành phần khác, … và báo lỗi. Rất may cho chúng ta, cách xử lý trường hợp này rất đơn giản, với mỗi dấu nháy kép, bạn chỉ cần thêm 1 dấu nháy kép nữa bên cạnh. Dấu nháy kép thêm vào này, được gọi là ký tự escape – ký tự sử dụng để báo cho VBA biết chúng ta sẽ viết vào sau ký tự escape 1 ký tự đặc biệt. Đoạn code không có lỗi sẽ như sau:
Sub enter_formula_1()
Range("B11").Formula = "=COUNTIF(B2:B10,""x"")"
End SubQua ví dụ này, chúng ta đã làm quen với thuộc tính .Formula của đối tượng Range trong Excel VBA.
Qua bài viết này, Thanh mong rằng đã mang lại cho bạn một chút khai niệm cơ bản nhất về đối tượng Range trong ngôn ngữ lập trình VBA của Excel. Hãy tham khảo khóa học VBA cơ bản, ở trong đó chúng ta sẽ có cơ hội để làm quen và thực hành nhiều ví dụ hơn nữa: