Nếu bạn mới bắt đầu sử dụng VBA thì bạn sẽ muốn bắt đầu nghiên cứu hướng dẫn VBA dành cho người mới bắt đầu của chúng tôi. Nhưng nếu bạn là một chuyên gia VBA dày dạn kinh nghiệm và đang tìm kiếm những thứ nâng cao hơn mà bạn có thể làm với VBA trong Excel thì hãy tiếp tục đọc.
Khả năng sử dụng mã hóa VBA trong Excel mở ra cả một thế giới tự động hóa. Bạn có thể tự động hóa các phép tính trong Excel, nút bấm và thậm chí gửi email. Có nhiều khả năng tự động hóa công việc hàng ngày của bạn với VBA hơn bạn có thể nhận ra.
Nội dung bài viết
Hướng dẫn VBA nâng cao cho Microsoft Excel
Mục tiêu chính của việc viết mã VBA trong Excel là để bạn có thể trích xuất thông tin từ bảng tính, thực hiện nhiều phép tính khác nhau trên đó và sau đó ghi lại kết quả vào bảng tính.
Sau đây là những cách sử 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ừ việc người dùng nhấn nút
- Gửi kết quả tính toán qua email cho ai đó
Với ba ví dụ này, bạn sẽ có thể viết nhiều loại mã VBA Excel nâng cao của riêng mình.
Nhập dữ liệu và thực hiện tính toán
Một trong những công việc phổ biến nhất mà mọi người sử dụng Excel là thực hiện các phép tính trên dữ liệu tồn tại bên ngoài Excel. Nếu bạn không sử dụng VBA, điều đó có nghĩa là bạn phải nhập dữ liệu theo cách thủ công, chạy các phép tính và xuất các giá trị đó sang trang tính hoặc báo cáo khác.
Với VBA, bạn có thể tự động hóa toàn bộ quá trình. Ví dụ: nếu bạn có tệp CSV mới được tải xuống một thư mục trên máy tính vào thứ Hai hàng tuần, bạn có thể định cấu hình mã VBA để chạy khi bạn mở bảng tính lần đầu tiên vào sáng Thứ Ba.
Mã nhập sau sẽ chạy và nhập tệp CSV vào bảng tính Excel của bạn.
Dim ws As Worksheet, strFile As String
Set ws = ActiveWorkbook.Sheets("Sheet1")
Cells.ClearContents
strFile = “c:temppurchases.csv”
With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh
End With
Mở công cụ soạn thảo Excel VBA và chọn đối tượng Sheet1. Từ hộp thả xuống đối tượng và phương thức, chọn Bảng tính Và Kích hoạt. Điều này sẽ chạy mã mỗi khi bạn mở bảng tính.
Điều này sẽ tạo ra một Bảng tính phụ_Kích hoạt() chức năng. Dán đoạn mã trên vào hàm đó.
Điều này đặt bảng tính đang hoạt động thành Trang 1xóa trang tính, kết nối với tệp bằng đường dẫn tệp bạn đã xác định bằng strFile biến, và sau đó là Với vòng lặp duyệt qua từng dòng trong tệp và đặt dữ liệu vào trang tính bắt đầu từ ô A1.
Nếu bạn chạy mã này, bạn sẽ thấy dữ liệu tệp CSV được nhập vào bảng tính trống của mình, trong Trang 1.
Nhập khẩu chỉ là bước đầu tiên. Tiếp theo, bạn muốn tạo tiêu đề mới cho cột chứa kết quả tính toán của bạn. Trong ví dụ này, giả sử bạn muốn tính 5% thuế phải trả khi bán từng mặt hàng.
Thứ tự các hành động mà mã của bạn nên thực hiện là:
- Tạo cột kết quả mới được gọi là thuế.
- Lặp qua đơn vị bán cột và tính thuế bán hàng.
- Viết kết quả tính toán vào hàng thích hợp trong bảng.
Đoạn mã sau sẽ thực hiện tất cả các bước này.
Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell As Range
Dim fltTax As DoubleSet 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ã này tìm hàng cuối cùng trong bảng dữ liệu của bạn, sau đó đặt phạm vi ô (cột có giá bán) theo hàng dữ liệu đầu tiên và cuối cùng. Sau đó, mã sẽ lặp qua từng ô đó, thực hiện tính toán thuế và ghi kết quả vào cột mới của bạn (cột 5).
Dán mã VBA ở trên vào bên dưới mã trước đó và chạy tập lệnh. Bạn sẽ thấy kết quả hiển thị ở cột E.
Giờ đây, mỗi khi bạn mở bảng tính Excel, nó sẽ tự động xuất hiện và nhận bản sao dữ liệu mới nhất từ tệp CSV. Sau đó, nó sẽ thực hiện các phép tính và ghi kết quả vào bảng tính. Bạn không cần phải làm bất cứ điều gì bằng tay nữa!
Tính kết quả từ việc nhấn nút
Nếu bạn muốn có nhiều quyền kiểm soát trực tiếp hơn khi các phép tính chạy, thay vì chạy tự động khi trang tính mở ra, bạn có thể sử dụng nút điều khiển thay thế.
Các nút điều khiển rất hữu ích nếu bạn muốn kiểm soát những phép tính nào được sử dụng. Ví dụ: trong trường hợp tương tự như trên, nếu bạn muốn sử dụng thuế suất 5% cho một khu vực và thuế suất 7% cho khu vực khác thì sao?
Bạn có thể cho phép cùng một mã nhập CSV chạy tự động nhưng để mã tính thuế chạy khi bạn nhấn nút thích hợp.
Sử dụng cùng một bảng tính như trên, chọn Nhà phát triển tab và chọn Chèn từ Điều khiển nhóm trong dải băng. Chọn nút ấn Điều khiển ActiveX từ menu thả xuống.
Vẽ nút ấn vào bất kỳ phần nào của trang tính cách xa nơi chứa dữ liệu.
Bấm chuột phải vào nút ấn và chọn Properties. bên trong Properties cửa sổ, hãy thay đổi Chú thích thành nội dung bạn muốn hiển thị cho người dùng. Trong trường hợp này có thể là Tính thuế 5%.
Bạn sẽ thấy văn bản này được phản ánh trên chính nút ấn. Đóng của cải cửa sổ và nhấp đúp vào chính nút nhấn đó. Thao tác này sẽ mở cửa sổ soạn thảo mã và con trỏ của bạn sẽ ở bên trong chức năng sẽ chạy khi người dùng nhấn nút ấn.
Dán mã tính thuế từ phần trên vào hàm này, giữ hệ số nhân thuế suất ở mức 0,05. Hãy nhớ bao gồm 2 dòng sau để xác định trang hoạt động.
Dim ws As Worksheet, strFile As StringSet ws = ActiveWorkbook.Sheets("Sheet1")
Bây giờ, lặp lại quy trình một lần nữa, tạo nút ấn thứ hai. Làm chú thích Tính thuế 7%.
Bấm đúp vào nút đó và dán cùng một mã, nhưng tạo hệ số nhân thuế là 0,07.
Bây giờ, tùy theo bạn nhấn nút nào mà cột thuế sẽ được tính tương ứng.
Sau khi hoàn tất, bạn sẽ có cả hai nút ấn trên trang tính của mình. Mỗi người trong số họ sẽ bắt đầu tính thuế khác nhau và sẽ ghi các kết quả khác nhau vào cột kết quả.
Để nhắn tin này, hãy chọn Nhà phát triển trình đơn và chọn Chế độ thiết kế tạo nhóm Điều khiển trong ribbon để tắt Chế độ thiết kế. Điều này sẽ kích hoạt các nút ấn.
Hãy thử chọn từng nút ấn để xem cột kết quả “thuế” thay đổi như thế nào.
Gửi kết quả tính toán qua email cho ai đó
Nếu bạn muốn gửi kết quả trên bảng tính cho ai đó qua email thì sao?
Bạn có thể tạo một nút khác gọi là Gửi tờ email cho sếp sử dụng quy trình tương tự ở trên. Mã cho nút này sẽ liên quan đến việc sử dụng đối tượng CDO Excel để định cấu hình cài đặt email SMTP và gửi kết quả qua email ở định dạng người dùng có thể đọc được.
Để kích hoạt tính năng này, bạn cần chọn Công cụ và tài liệu tham khảo. Cuộn xuống Thư viện Microsoft CDO cho Windows 2000kích hoạt nó và chọn OK.
Có ba phần chính trong mã bạn cần tạo để gửi email và nhúng kết quả bảng tính.
Đầu tiên là thiết lập các biến để giữ chủ đề, địa chỉ Đến và Từ cũng như 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 = "[email protected]"
strTo = "[email protected]"
strCc = ""
strBcc = ""
strBody = "The following is the breakdown of taxes paid on sales this quarter."
Tất nhiên, nội dung cần phải linh hoạt tùy thuộc vào kết quả có trong trang tính, vì vậy ở đây bạn sẽ cần thêm một vòng lặp đi qua phạm vi, trích xuất dữ liệu và ghi từng dòng vào nội dung.
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 tiếp theo liên quan đến việc thiết lập cài đặt SMTP để bạn có thể gửi email qua máy chủ SMTP của mình. Nếu bạn sử dụng Gmail thì đây thường là địa chỉ email Gmail, mật khẩu Gmail của bạn và máy chủ Gmail SMTP (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("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Update
End With
With CDO_Mail
Set .Configuration = CDO_Config
End With
Thay thế [email protected] và mật khẩu bằng chi tiết tài khoản của riêng bạn.
Cuối cùng, để bắt đầu gửi email, hãy chèn đoạ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.SendError_Handling:
If Err.Description <> "" Then MsgBox Err.Description
Ghi chú: Nếu bạn thấy lỗi truyền tải khi cố chạy mã này, có thể là do tài khoản Google của bạn đang chặn “ứng dụng kém an toàn” chạy. Bạn sẽ cần truy cập trang cài đặt ứng dụng kém an toàn hơn và BẬT tính năng này.
Sau khi kích hoạt, email của bạn sẽ được gửi. Đây là giao diện của người nhận email kết quả được tạo tự động của bạn.
Như bạn có thể thấy, có rất nhiều thứ bạn thực sự có thể tự động hóa bằng Excel VBA. Hãy thử thử nghiệm với các đoạn mã bạn đã tìm hiểu trong bài viết này và tạo tự động hóa VBA độc đáo của riêng bạn.
