Sử dụng excel khi làm việc bạn đã thấy vô cùng tiện lợi. Một tập hợp các hàm của excel (như SUM, AVERAGE, IF, ...) giúp bạn giải quyết được vô khối vấn đề. Tuy nhiên khi bạn biết tạo macro với những câu lệnh lập trình trong VBA bạn sẽ thấy rõ sức mạnh của excel. Bạn sẽ nâng cao hiệu suất công việc một cách đáng kể khi thực hiện macro với lập trình VBA.
Nếu ở trường phổ thông bạn đã làm quen với lập trình TP với những câu lệnh While, For, If v.v... việc học viết một macro với các câu lệnh của VBA không mấy khó khăn. Trong nhiều trường hợp thay vì bạn phải hì hục làm trên bảng tính excel thì chỉ với vài câu lệnh bạn sẽ giải quyết công việc một cách nhẹ nhàng. Trong bài viết này (cùng với một số bài viết khác của blog này) tôi muốn cùng các bạn chúng ta CÙNG NHAU HỌC để nắm lấy những câu lệnh có ích trong VBA với excel (ở đây nói với excel 2010, với bản 2007 thì tương tự, các phiên bản cũ hơn có thể khác tý chút).
Bài 1: Tạo một macro đơn giản.
Mở một file excel, nháy chuột vào thẻ developer (xem thêm bài "Thêm thẻ developer ..." cũng trong blog này, tại đây) bạn sẽ nhìn thấy nút lệnh Record Macro với một nút tròn màu đỏ. Nháy chuột vào nút lệnh này sẽ xuất hiện một hộp thoại với tên mặc định là macro1. Nếu nháy Ok trong hộp thoại này nghĩa là bạn đã chấp nhận mở một macro với tên là macro1, bây giờ nút lệnh Record Macro chuyển thành nút màu xanh, vuông với tên mới là Stop Recording. Từ lúc này mọi thao tác của bạn trên bảng tính sẽ được excel ghi lại. Để kết thúc ghi, ta nháy chuột vào nút lệnh Stop Recording.
Bây giờ chúng ta hãy xét một ví dụ cụ thể như sau (thực hiện theo các thao tác như dưới đây):
-Khởi động excel, mở một trang tính.
-Nháy chuột vào nút lệnh Record Macro rồi nháy Ok trong hộp thoại vừa xuất hiện (chú ý nhìn tên macro trong ô Macro name, thông thường nó sẽ là macro1)
-Thực hiện trên trang tính:
+Chọn ô B2 (nháy chuột vào B2), gõ số 5
+Chọn ô B4, gõ số 10
+Chọn A4
-Nháy chuột vào nút lệnh Stop Recording để kết thúc.
Như vậy chúng ta đã tạo ra một macro có tên là macro1 có nội dung như dưới đây:
Sub Macro1()
'
' Macro1 Macro
'
'
Range("B2").Select
ActiveCell.FormulaR1C1 = "5"
Range("B4").Select
ActiveCell.FormulaR1C1 = "10"
Range("A4").Select
End Sub
Bài 1: Tạo một macro đơn giản.
Mở một file excel, nháy chuột vào thẻ developer (xem thêm bài "Thêm thẻ developer ..." cũng trong blog này, tại đây) bạn sẽ nhìn thấy nút lệnh Record Macro với một nút tròn màu đỏ. Nháy chuột vào nút lệnh này sẽ xuất hiện một hộp thoại với tên mặc định là macro1. Nếu nháy Ok trong hộp thoại này nghĩa là bạn đã chấp nhận mở một macro với tên là macro1, bây giờ nút lệnh Record Macro chuyển thành nút màu xanh, vuông với tên mới là Stop Recording. Từ lúc này mọi thao tác của bạn trên bảng tính sẽ được excel ghi lại. Để kết thúc ghi, ta nháy chuột vào nút lệnh Stop Recording.
Bây giờ chúng ta hãy xét một ví dụ cụ thể như sau (thực hiện theo các thao tác như dưới đây):
-Khởi động excel, mở một trang tính.
-Nháy chuột vào nút lệnh Record Macro rồi nháy Ok trong hộp thoại vừa xuất hiện (chú ý nhìn tên macro trong ô Macro name, thông thường nó sẽ là macro1)
-Thực hiện trên trang tính:
+Chọn ô B2 (nháy chuột vào B2), gõ số 5
+Chọn ô B4, gõ số 10
+Chọn A4
-Nháy chuột vào nút lệnh Stop Recording để kết thúc.
Như vậy chúng ta đã tạo ra một macro có tên là macro1 có nội dung như dưới đây:
Sub Macro1()
'
' Macro1 Macro
'
'
Range("B2").Select
ActiveCell.FormulaR1C1 = "5"
Range("B4").Select
ActiveCell.FormulaR1C1 = "10"
Range("A4").Select
End Sub
Để xem được nội dung của macro1 (như trên) bạn nháy chuột vào nút lệnh Macros (chú ý rằng chúng ta vẫn đang ở thẻ developer). Một hộp thoại xuất hiện, trong ô Macro name con trỏ đang nhấp nháy tại tên macro1, vạch sáng xanh đang định vị tại dòng có chữ macro1. Bạn nháy chuột vào nút lệnh Edit trong hộp thoại sẽ thấy mở ra cửa sổ soạn thảo với nội dung của macro1 (như trên). Chúng ta thấy dòng đầu tiên có từ Sub, dòng cuối cùng có từ End Sub, đó là các từ khóa. Các dòng thứ 2, 3, 4, 5 có dấu nháy ở đầu dòng, đó là các dong chú thích. Dòng tiếp theo có câu lệnh Range("B2").Select đó là câu lệnh định vị đến ô B2.
Như vậy chúng ta đã biết cách tạo ra một macro, xem nội dung của macro vừa tạo.
Bây giờ theo cách như trên, bạn hãy tạo ra một macro (chẳng hạn với tên là macro2 hoặc bạn có thể đặt là ViDu cũng được). Tạo xong rồi mở ra xem để trải nghiệm nhé.
Cứ tuần tự theo cách đó bạn hãy tạo một số macro xem sao.
Bài 2: Chạy một macro.
Giả sử chúng ta có bảng tính (01 file) có ba sheet lần lượt là sheet1, sheet2, sheet3. Trong mỗi sheet này chúng ta phải kẻ một bảng trong vùng A3:H10. Ô A3 có chữ TT (thứ tự), ô B3 có chữ Họ và tên, các ô C3, D3, E3, F3, G3, H3 lần lượt là các chữ Ngày sinh, ĐT (điểm toán), ĐV (điểm văn), ĐL, ĐH, ĐTB.
Chúng ta đang ở sheet1 để tạo macro. Sau khi hoàn tất ta chuyển đến sheet2 và chạy macro, chẳng hạn macro vừa tạo có tên là macro2, như sau:
Nháy nút lệnh macro (chú ý rằng đang ở thẻ developer), hộp thoại macro xuất hiện, vạch sáng xanh đang định vị ở dòng có tên macro ta muốn chạy, nháy nút Run trong hộp thoại lập tức macro được thực hiện, một bảng giống như trong sheet1 xuất hiện.
Như vậy ta thấy macro đã giúp làm tăng năng suất công việc rất nhiều. Nếu chúng ta phải làm hàng chục bảng tương tự như nhau thì với cách tạo macro công việc của ta sẽ trở nên hết sức thuận tiện phải không thưa bạn.
Bài 3: Tạo một macro với các câu lệnh của VBA.
Bây giờ chúng ta nói tới cách tạo một macro bằng câu lệnh VBA.
Nháy nút lệnh Macros, hộp thoại Macro xuất hiện, trong ô Macro name ta đặt tên cho macro, chẳng hạn VD1, khi đó ta sẽ thấy ở phía bên phải của hộp thoại nút Create sáng lên, nháy chuột vào nút đó hoặc gõ enter ta sẽ thấy cửa sổ soạn thảo mở ra như sau:
Sub VD1()
End Sub
Con trỏ soạn thảo đang nhấp nháy ở phía dưới dòng Sub VD1() để đợi ta gõ vào các dòng lệnh. Ta gõ vào lệnh sau:
If Cells(2, 2) >= 8 Then Cells(2, 3) = "Tot"
Câu lệnh trên có nghĩa là: Nếu ở ô có địa chỉ dòng 2, cột 2 (tức là ô B2) có giá trị lớn hơn hoặc bằng 8 thì điền vào ô có địa chỉ dòng 2, cột 3 (tức là ô C2) chữ Tot.
Bạn hãy chạy (thực hiện) macro VD1 với điều kiện trong ô B2 có 8 hoặc 9 để kiểm nghiệm, tiếp tục chạy với điều kiện trong ô B2 có số nhỏ hơn 8 để kiểm nghiệm.
➤Ở trên ta đã biết các từ Sub, End Sub là các từ khóa. Nói thêm về tên macro. Ở ví dụ trên macro có tên là VD1. Khi đặt tên macro ta phải bắt đầu bằng 1 chữ cái, trong tên không được chứa dấu cách (dấu khoảng trắng), không được chứa các ký tự đặc biệt.
➤Bây giờ ta mở cửa sổ soạn thảo có chứa macro VD1 nói trên bằng cách nháy chuột vào nút lệnh Macros để làm xuất hiện hộp thoại Macro, nháy chuột vào dòng có từ VD1 rồi nháy chuột vào nút lệnh Edit ở phía bên phải thoại macro.
Khi cửa sổ soạn thảo đã mở rồi, bạn thực hiện chọn toàn bộ phần văn bản của macro VD1 (như trong word) rồi copy đến vị trí tiếp theo, sửa tên VD1 thành ViDu1, sửa câu lệnh If thành như sau:
If Cells(2, 2) >= 8 Then Cells(2, 3) = "Tot" else Cells(2,3)="Khong phai loai Tot"
Thế là chúng ta có thêm một macro nữa, chỉ khác macro VD1 có chút síu thôi. Ý nghĩa của câu lệnh trên là: Nếu ô B2 (có địa chỉ là dòng 2, cột 2) có giá lớn hơn hoặc bằng 8 thì in vào ô C2 chữ Tot, ngược lại thì in chữ Khong phai loai Tot
➤ Đến đây bạn đã sơ bộ biết cách tạo ra một macro với câu lệnh VBA, ta hãy nói tới cách lưu file excel có chứa macro. Với excel từ đời 2007 trở lên khi lưu file có chứa macro bạn phải chọn kiểu file có đuôi .xlsm.
Chúng ta hãy cùng xét thêm một số ví dụ nữa.
Ví dụ 1: Bạn là một giáo viên, bạn có một file điểm kiểm tra trong đó điểm được ghi ở cột 9 (cột I) từ dòng thứ 8 đến dòng thứ 18. Cần tạo một macro đếm xem có bao nhiêu điểm từ 8 trở lên. Kết quả ghi vào ô có địa chỉ dòng 2, cột 9 (ô I2).
macro được viết như dưới đây:
Sub ViDu1()
Dim i, kq As Integer
kq = 0
For i = 8 To 18
If Cells(i, 9) >= 8 Then kq = kq + 1
Next i
Cells(2, 9) = kq
End Sub
Bây giờ theo cách như trên, bạn hãy tạo ra một macro (chẳng hạn với tên là macro2 hoặc bạn có thể đặt là ViDu cũng được). Tạo xong rồi mở ra xem để trải nghiệm nhé.
Cứ tuần tự theo cách đó bạn hãy tạo một số macro xem sao.
Bài 2: Chạy một macro.
Giả sử chúng ta có bảng tính (01 file) có ba sheet lần lượt là sheet1, sheet2, sheet3. Trong mỗi sheet này chúng ta phải kẻ một bảng trong vùng A3:H10. Ô A3 có chữ TT (thứ tự), ô B3 có chữ Họ và tên, các ô C3, D3, E3, F3, G3, H3 lần lượt là các chữ Ngày sinh, ĐT (điểm toán), ĐV (điểm văn), ĐL, ĐH, ĐTB.
Chúng ta đang ở sheet1 để tạo macro. Sau khi hoàn tất ta chuyển đến sheet2 và chạy macro, chẳng hạn macro vừa tạo có tên là macro2, như sau:
Nháy nút lệnh macro (chú ý rằng đang ở thẻ developer), hộp thoại macro xuất hiện, vạch sáng xanh đang định vị ở dòng có tên macro ta muốn chạy, nháy nút Run trong hộp thoại lập tức macro được thực hiện, một bảng giống như trong sheet1 xuất hiện.
Như vậy ta thấy macro đã giúp làm tăng năng suất công việc rất nhiều. Nếu chúng ta phải làm hàng chục bảng tương tự như nhau thì với cách tạo macro công việc của ta sẽ trở nên hết sức thuận tiện phải không thưa bạn.
Bài 3: Tạo một macro với các câu lệnh của VBA.
Bây giờ chúng ta nói tới cách tạo một macro bằng câu lệnh VBA.
Nháy nút lệnh Macros, hộp thoại Macro xuất hiện, trong ô Macro name ta đặt tên cho macro, chẳng hạn VD1, khi đó ta sẽ thấy ở phía bên phải của hộp thoại nút Create sáng lên, nháy chuột vào nút đó hoặc gõ enter ta sẽ thấy cửa sổ soạn thảo mở ra như sau:
Sub VD1()
End Sub
Con trỏ soạn thảo đang nhấp nháy ở phía dưới dòng Sub VD1() để đợi ta gõ vào các dòng lệnh. Ta gõ vào lệnh sau:
If Cells(2, 2) >= 8 Then Cells(2, 3) = "Tot"
Câu lệnh trên có nghĩa là: Nếu ở ô có địa chỉ dòng 2, cột 2 (tức là ô B2) có giá trị lớn hơn hoặc bằng 8 thì điền vào ô có địa chỉ dòng 2, cột 3 (tức là ô C2) chữ Tot.
Bạn hãy chạy (thực hiện) macro VD1 với điều kiện trong ô B2 có 8 hoặc 9 để kiểm nghiệm, tiếp tục chạy với điều kiện trong ô B2 có số nhỏ hơn 8 để kiểm nghiệm.
➤Ở trên ta đã biết các từ Sub, End Sub là các từ khóa. Nói thêm về tên macro. Ở ví dụ trên macro có tên là VD1. Khi đặt tên macro ta phải bắt đầu bằng 1 chữ cái, trong tên không được chứa dấu cách (dấu khoảng trắng), không được chứa các ký tự đặc biệt.
➤Bây giờ ta mở cửa sổ soạn thảo có chứa macro VD1 nói trên bằng cách nháy chuột vào nút lệnh Macros để làm xuất hiện hộp thoại Macro, nháy chuột vào dòng có từ VD1 rồi nháy chuột vào nút lệnh Edit ở phía bên phải thoại macro.
Khi cửa sổ soạn thảo đã mở rồi, bạn thực hiện chọn toàn bộ phần văn bản của macro VD1 (như trong word) rồi copy đến vị trí tiếp theo, sửa tên VD1 thành ViDu1, sửa câu lệnh If thành như sau:
If Cells(2, 2) >= 8 Then Cells(2, 3) = "Tot" else Cells(2,3)="Khong phai loai Tot"
Thế là chúng ta có thêm một macro nữa, chỉ khác macro VD1 có chút síu thôi. Ý nghĩa của câu lệnh trên là: Nếu ô B2 (có địa chỉ là dòng 2, cột 2) có giá lớn hơn hoặc bằng 8 thì in vào ô C2 chữ Tot, ngược lại thì in chữ Khong phai loai Tot
➤ Đến đây bạn đã sơ bộ biết cách tạo ra một macro với câu lệnh VBA, ta hãy nói tới cách lưu file excel có chứa macro. Với excel từ đời 2007 trở lên khi lưu file có chứa macro bạn phải chọn kiểu file có đuôi .xlsm.
Chúng ta hãy cùng xét thêm một số ví dụ nữa.
Ví dụ 1: Bạn là một giáo viên, bạn có một file điểm kiểm tra trong đó điểm được ghi ở cột 9 (cột I) từ dòng thứ 8 đến dòng thứ 18. Cần tạo một macro đếm xem có bao nhiêu điểm từ 8 trở lên. Kết quả ghi vào ô có địa chỉ dòng 2, cột 9 (ô I2).
macro được viết như dưới đây:
Sub ViDu1()
Dim i, kq As Integer
kq = 0
For i = 8 To 18
If Cells(i, 9) >= 8 Then kq = kq + 1
Next i
Cells(2, 9) = kq
End Sub
Ở macro trên ta thấy có dòng khai báo biến. Đó là dòng bắt đầu bằng từ khóa Dim, tiếp theo là các tên biến được ngăn cách với nhau bằng dấu phảy. Tiếp theo các tên biến là từ khóa As và cuối cùng là kiểu biến. Trong macro trên ta có thêm câu lệnh For đã được nói tới trong blog này (tại đây)
Ví dụ 2: Vẫn với yêu cầu như trên, với macro như trên ta thay câu lệnh For bằng câu lệnh bằng câu lệnh Do (xem câu lệnh Do tại đây). Ta có như sau:
Sub ViDu2()
Dim i, kq As Integer
kq = 0
i = 8
Do While Cells(i, 9) <> ""
If Cells(i, 9) >= 8 Then kq = kq + 1
i = i + 1
Loop
Cells(2, 9) = kq
End Sub
Ví dụ 2: Vẫn với yêu cầu như trên, với macro như trên ta thay câu lệnh For bằng câu lệnh bằng câu lệnh Do (xem câu lệnh Do tại đây). Ta có như sau:
Sub ViDu2()
Dim i, kq As Integer
kq = 0
i = 8
Do While Cells(i, 9) <> ""
If Cells(i, 9) >= 8 Then kq = kq + 1
i = i + 1
Loop
Cells(2, 9) = kq
End Sub
Với câu lệnh Do chúng ta không phải bó gọn từ dòng 8 đến dòng 18 như ở ví dụ 1 mà là từ dòng 18 đến khi nào gặp một ô không chứa dữ liệu được quy định bởi điều kiện Cells(i,9)<>"" của câu lệnh Do.
Ví dụ 3: Vẫn với file điểm như ở ví dụ 1, bây giờ chúng ta phải thống kê theo yêu cầu: có bao nhiêu điểm giỏi (từ 8 đến 10), bao nhiêu điểm khá (từ 6.5 đến dưới 8), bao nhiêu điểm trung bình (từ 5 đến dưới 6.5), bao nhiêu điểm yếu (từ 3.5 đến dưới 5) và bao nhiêu điểm kém (từ 0 đến dưới 3.5). Ta có như sau:
Sub ViDu2()
Dim i, Gioi, Kha, Tb, Yeu, Kem As Integer
Gioi = 0
Kha = 0
Tb = 0
Yeu = 0
Kem = 0
i = 8
Do While Cells(i, 9) <> ""
If (Cells(i, 9) >= 8) And (Cells(i, 9) <= 10) Then Gioi = Gioi + 1
If (Cells(i, 9) >= 6.5) And (Cells(i, 9) < 8) Then Kha = Kha + 1
If (Cells(i, 9) >= 5) And (Cells(i, 9) < 6.5) Then Tb = Tb + 1
If (Cells(i, 9) >= 3.5) And (Cells(i, 9) < 5) Then Yeu = Yeu + 1
If (Cells(i, 9) >= 0) And (Cells(i, 9) < 3.5) Then Kem = Kem + 1
i = i + 1
Loop
Cells(2, 9) = Gioi
Cells(2, 10) = Kha
Cells(2, 11) = Tb
Cells(2, 12) = Yeu
Cells(2, 13) = Kem
End Sub
Ở macro trên ta đã khai báo các biến Gioi, Kha, Tb, Yeu, Kem. Giá trị của các biến được ghi vào các ô I2, J2, K2, L2, M2.
Chạy macro bằng cách gán một nút lệnh.
Đến đây bạn đã cơ bản biết cách tạo ra một macro để sử dụng cho cong việc của mình.
Trong các ví dụ trên các bạn thấy mỗi khi muốn thực hiện một macro lại phải qua mấy thao tác. Vậy có cách nào để chỉ cần một cú nháy chuột là ta có thể thực hiện ngay macro vừa tạo?
Để làm được như vậy tôi xin hướng dẫn các bạn như sau:
Trong cửa sổ của file excel bạn vừa tạo macro, với chế độ mở thẻ developer, bạn nháy chuột vào nút lệnh insert rồi chọn nút button (nút lệnh đã được khoanh bằng vòng tròn đỏ trong hình dưới đây)
Sau đó bạn đưa chuột vào vùng bảng tính rồi kéo thả chuột để vẽ nút. Khi rời nút chuột bạn sẽ thấy xuất hiện hộp thoại Assign Macro. Trong hộp thoại này bạn chọn tên macro cần gán cho nút lệnh rồi nháy Ok, thế là bạn đã tạo được nút lệnh để chạy macro. Tên mặc định trên nút là button. Muốn thay tên này bằng tên mới (chẳng hạn ViDu2, tức là tên của macro) bạn nháy chuột phải vào nút lệnh này và chọn Edit Text để thay đổi tên nút lệnh theo ý muốn.
Vậy là tôi đã trình bày hết phần 1, phần cơ bản.
Hẹn gặp lại các bạn ở phần 2. Chúc các bạn thành công.
Ví dụ 3: Vẫn với file điểm như ở ví dụ 1, bây giờ chúng ta phải thống kê theo yêu cầu: có bao nhiêu điểm giỏi (từ 8 đến 10), bao nhiêu điểm khá (từ 6.5 đến dưới 8), bao nhiêu điểm trung bình (từ 5 đến dưới 6.5), bao nhiêu điểm yếu (từ 3.5 đến dưới 5) và bao nhiêu điểm kém (từ 0 đến dưới 3.5). Ta có như sau:
Sub ViDu2()
Dim i, Gioi, Kha, Tb, Yeu, Kem As Integer
Gioi = 0
Kha = 0
Tb = 0
Yeu = 0
Kem = 0
i = 8
Do While Cells(i, 9) <> ""
If (Cells(i, 9) >= 8) And (Cells(i, 9) <= 10) Then Gioi = Gioi + 1
If (Cells(i, 9) >= 6.5) And (Cells(i, 9) < 8) Then Kha = Kha + 1
If (Cells(i, 9) >= 5) And (Cells(i, 9) < 6.5) Then Tb = Tb + 1
If (Cells(i, 9) >= 3.5) And (Cells(i, 9) < 5) Then Yeu = Yeu + 1
If (Cells(i, 9) >= 0) And (Cells(i, 9) < 3.5) Then Kem = Kem + 1
i = i + 1
Loop
Cells(2, 9) = Gioi
Cells(2, 10) = Kha
Cells(2, 11) = Tb
Cells(2, 12) = Yeu
Cells(2, 13) = Kem
End Sub
Ở macro trên ta đã khai báo các biến Gioi, Kha, Tb, Yeu, Kem. Giá trị của các biến được ghi vào các ô I2, J2, K2, L2, M2.
Chạy macro bằng cách gán một nút lệnh.
Đến đây bạn đã cơ bản biết cách tạo ra một macro để sử dụng cho cong việc của mình.
Trong các ví dụ trên các bạn thấy mỗi khi muốn thực hiện một macro lại phải qua mấy thao tác. Vậy có cách nào để chỉ cần một cú nháy chuột là ta có thể thực hiện ngay macro vừa tạo?
Để làm được như vậy tôi xin hướng dẫn các bạn như sau:
Trong cửa sổ của file excel bạn vừa tạo macro, với chế độ mở thẻ developer, bạn nháy chuột vào nút lệnh insert rồi chọn nút button (nút lệnh đã được khoanh bằng vòng tròn đỏ trong hình dưới đây)
Vậy là tôi đã trình bày hết phần 1, phần cơ bản.
Hẹn gặp lại các bạn ở phần 2. Chúc các bạn thành công.