Nếu bạn mới bắt đầu VBA, bạn nên bắt đầu học từ hướng dẫn VBA cho người mới. Nhưng nếu bạn chuyên gia VBA dày dạn và tìm điều nâng cao hơn làm được với VBA trong Excel, hãy tiếp tục đọc.
Khả năng dùng mã VBA trong Excel mở ra toàn bộ thế giới tự động hóa. Bạn tự động hóa tính toán, nút bấm (pushbuttons), thậm chí gửi email. Có nhiều khả năng tự động hóa công việc hàng ngày với VBA hơn bạn nhận ra.
Hướng Dẫn VBA Nâng Cao Cho Microsoft Excel
Mục tiêu chính viết mã VBA trong Excel là trích xuất thông tin từ bảng tính, thực hiện nhiều tính toán, và ghi kết quả trở lại bảng tính.
Sau là các trường hợp dùng VBA phổ biến nhất trong Excel.
- Nhập dữ liệu và thực hiện tính toán
- Tính kết quả từ người dùng nhấn nút
- Gửi kết quả tính toán qua email
Với 3 ví dụ này, bạn có thể viết nhiều mã VBA nâng cao Excel riêng.
Nhập Dữ Liệu Và Thực Hiện Tính Toán
Một việc phổ biến nhất dùng Excel là tính toán trên dữ liệu tồn tại bên ngoài Excel. Nếu không dùng VBA, bạn phải nhập dữ liệu thủ công, chạy tính toán, xuất giá trị ra trang tính hoặc báo cáo khác.
Với VBA, tự động hóa toàn bộ quy trình. Ví dụ, nếu file CSV mới tải vào thư mục máy tính mỗi thứ Hai, bạn cấu hình mã VBA chạy khi mở bảng tính thứ Ba.
Mã nhập sau sẽ chạy và nhập file CSV vào bảng tính Excel.
Dim ws As Worksheet, strFile As String
Set ws = ActiveWorkbook.Sheets("Sheet1")
Cells.ClearContents
strFile = "c:\temp\purchases.csv"
With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh
End With
Mở công cụ chỉnh sửa VBA Excel và chọn đối tượng Sheet1. Từ menu thả xuống đối tượng và phương thức, chọn Worksheet và Activate. Điều này chạy mã mỗi khi mở bảng tính.
Điều này tạo hàm Sub Worksheet_Activate(). Dán mã trên vào hàm đó.
Đoạn mã thiết lập trang tính hoạt động là Sheet1, xóa trang, kết nối file dùng đường dẫn biến strFile, và vòng lặp With duyệt qua mọi dòng file đặt dữ liệu vào trang từ ô A1.
Chạy mã, bạn thấy dữ liệu CSV nhập vào bảng tính trống, ở Sheet1.
Nhập chỉ là bước đầu. Tiếp, tạo tiêu đề mới cho cột chứa kết quả tính toán. Ví dụ, muốn tính thuế 5% trên bán mỗi mặt hàng.
Thứ tự hành động mã nên làm:
- Tạo cột kết quả mới tên taxes (thuế).
- Duyệt cột units sold (đơn vị bán) tính thuế bán.
- Ghi kết quả tính vào hàng thích hợp trong trang.
Mã sau thực hiện tất cả bước.
Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell As Range
Dim fltTax As Double
Set StartCell = Range("A1")
'Find Last Row and Column
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4))
rowCounter = 2
Cells(1, 5) = "taxes"
For Each cell In rng
fltTax = cell.Value * 0.05
Cells(rowCounter, 5) = fltTax
rowCounter = rowCounter + 1
Next cell
Mã tìm hàng cuối trang dữ liệu, thiết lập phạm vi ô (cột giá bán) theo hàng đầu/cuối dữ liệu. Sau mã duyệt từng ô, tính thuế, ghi kết quả vào cột mới (cột 5).
Dán mã VBA trên dưới mã trước, chạy script. Bạn thấy kết quả hiện cột E.
Bây giờ, mỗi khi mở bảng tính Excel, nó tự động lấy bản sao mới nhất từ file CSV. Sau đó, tính toán và ghi kết quả. Bạn không phải làm thủ công nữa!
Tính Kết Quả Từ Nhấn Nút (Button Press)
Nếu muốn kiểm soát trực tiếp khi nào tính toán chạy, thay vì tự chạy khi mở trang, dùng nút điều khiển (control button).
Nút điều khiển hữu ích nếu muốn kiểm soát tính toán nào dùng. Ví dụ, trường hợp trên, nếu muốn dùng thuế 5% cho vùng, 7% cho vùng khác?
Cho phép mã nhập CSV chạy tự động, nhưng để mã tính thuế chạy khi nhấn nút thích hợp.
Dùng cùng bảng tính, chọn thẻ Developer (Nhà phát triển), chọn Insert từ nhóm Controls (Điều khiển) Ribbon. Chọn push button (Nút bấm) ActiveX Control từ menu thả xuống.
Vẽ nút bấm lên phần trang tính xa nơi dữ liệu sẽ đi.
Chuột phải nút bấm, chọn Properties (Thuộc tính). Cửa sổ Properties, đổi Caption (Chú thích) thành muốn hiển thị. Trong trường hợp này Calculate 5% Tax (Tính thuế 5%).
Bạn thấy văn bản phản ánh trên nút. Đóng cửa sổ properties, nhấp đúp nút bấm. Mở cửa sổ chỉnh mã, con trỏ ở trong hàm chạy khi người dùng nhấn nút.
Dán mã tính thuế từ phần trên vào hàm, giữ hệ số thuế 0.05. Nhớ bao gồm 2 dòng sau định nghĩa trang hoạt động.
Dim ws As Worksheet, strFile As String
Set ws = ActiveWorkbook.Sheets("Sheet1")
Lặp lại, tạo nút bấm thứ 2. Làm Caption Calculate 7% Tax.
Nhấp đúp nút đó và dán cùng mã, nhưng hệ số thuế 0.07.
Bây giờ, tùy nút bạn nhấn, cột thuế tính theo.
Khi xong, bạn có 2 nút bấm trên trang. Mỗi cái khởi tạo tính thuế khác và ghi kết quả khác vào cột kết quả.
Để test, chọn menu Developer, chọn Design Mode (Chế độ thiết kế) từ nhóm Controls Ribbon để tắt Design Mode. Điều này kích hoạt nút bấm.
Thử chọn từng nút xem cột kết quả "taxes" thay đổi như thế nào.
Gửi Kết Quả Tính Toán Qua Email
Điều gì nếu muốn gửi kết quả trên bảng tính cho ai qua email?
Bạn tạo nút khác tên Email Sheet to Boss (Gửi trang cho sếp) dùng cùng thủ tục. Mã nút này liên quan dùng đối tượng Excel CDO cấu hình cài đặt SMTP email, và gửi kết quả định dạng dễ đọc.
Để bật tính năng, chọn Tools and References (Công cụ và Tham chiếu). Cuộn xuống Microsoft CDO for Windows 2000 Library, bật, chọn OK.
Có 3 phần chính mã cần tạo để gửi email nhúng kết quả bảng tính.
Phần 1 thiết lập biến chứa chủ đề, địa chỉ To/From, nội dung email.
Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String
Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell As Range
Dim fltTax As Double
Set ws = ActiveWorkbook.Sheets("Sheet1")
strSubject = "Taxes Paid This Quarter"
strFrom = "MyEmail@gmail.com"
strTo = "BossEmail@gmail.com"
strCc = ""
strBcc = ""
strBody = "The following is the breakdown of taxes paid on sales this quarter."
Nội dung (body) cần động tùy kết quả trong trang, nên thêm vòng lặp duyệt phạm vi, trích dữ liệu, ghi từng dòng vào body.
Set StartCell = Range("A1")
'Find Last Row and Column
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4))
rowCounter = 2
strBody = strBody & vbCrLf
For Each cell In rng
strBody = strBody & vbCrLf
strBody = strBody & "We sold " & Cells(rowCounter, 3).Value & " of " & Cells(rowCounter, 1).Value _
& " for " & Cells(rowCounter, 4).Value & " and paid taxes of " & Cells(rowCounter, 5).Value & "."
rowCounter = rowCounter + 1
Next cell
Phần 2 thiết lập cài đặt SMTP để gửi email qua máy chủ SMTP. Nếu dùng Gmail, thường là địa chỉ Gmail, mật khẩu Gmail, máy chủ SMTP Gmail (smtp.gmail.com).
Set CDO_Mail = CreateObject("CDO.Message")
On Error GoTo Error_Handling
Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1
Set SMTP_Config = CDO_Config.Fields
With SMTP_Config
.Item("https://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("https://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("https://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("https://schemas.microsoft.com/cdo/configuration/sendusername") = "email@website.com"
.Item("https://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
.Item("https://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Item("https://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Update
End With
With CDO_Mail
Set .Configuration = CDO_Config
End With
Thay email@website.com và password bằng chi tiết tài khoản.
Cuối, khởi tạo gửi email, chèn mã sau.
CDO_Mail.Subject = strSubject CDO_Mail.From = strFrom CDO_Mail.To = strTo CDO_Mail.TextBody = strBody CDO_Mail.CC = strCc CDO_Mail.BCC = strBcc CDO_Mail.Send Error_Handling: If Err.Description <> "" Then MsgBox Err.Description
Lưu ý: Nếu thấy lỗi transport (vận chuyển) chạy mã, có thể tài khoản Google chặn "less secure apps" (ứng dụng kém an toàn). Bạn cần vào trang cài đặt less secure apps bật tính năng ON.
Sau bật, email gửi. Đây hình ảnh người nhận email kết quả tự động.
Như thấy, có rất nhiều tự động hóa thực sự với Excel VBA. Thử chơi với đoạn mã học trong bài và tạo tự động hóa VBA riêng.