Nếu bạn mới bắt đầu với 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à bạn đ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 một thế giới tự động hóa toàn diện. Bạn có thể tự động tính toán trong Excel, các 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.
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 kết quả trở lại bảng tính
Sau đây là những cách sử dụng phổ biến nhất của VBA trong Excel.
- Nhập dữ liệu và thực hiện tính toán
- Tính toán kết quả khi 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 có thể viết nhiều 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 điều 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 tính toán và xuất các giá trị đó sang một 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ộ quy trình. Ví dụ:nếu bạn có một tệp CSV mới được tải xuống thư mục trên máy tính của mình vào Thứ Hai hàng tuần, bạn có thể định cấu hình mã VBA của mình để 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:\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 trong Excel và chọn đối tượng Sheet1. Từ các hộp thả xuống đối tượng và phương thức, hãy chọn Trang tính và Kích hoạt . Thao tác này sẽ chạy mã mỗi khi bạn mở bảng tính.
Thao tác này sẽ tạo Sub Worksheet_Activate () chức năng. Dán đoạn mã trên vào hàm đó.
Điều này đặt trang tính hoạt động thành Trang tính1 , xóa trang tính, kết nối với tệp bằng đường dẫn tệp bạn đã xác định với strFile và sau đó là Với vòng lặp chuyển động qua mọi 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 rằng dữ liệu tệp CSV được nhập vào bảng tính trống của bạn, trong Trang tính1 .
Nhập chỉ là bước đầu tiên. Tiếp theo, bạn muốn tạo một tiêu đề mới cho cột sẽ 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 phải thực hiện là:
- Tạo cột kết quả mới được gọi là thuế .
- Lặp lại các đơn vị đã bán 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 trang tính.
Đ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 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ã này tìm hàng cuối cùng trong trang 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ã lặp qua từng ô đó, thực hiện tính 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ị trong cột E.
Giờ đây, mỗi khi bạn mở trang tính Excel của mình, trang tính này 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 trang tính. Bạn không phải làm bất cứ điều gì theo cách thủ công nữa!
Tính toán kết quả từ lần 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 tính toán 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 các 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 vùng và thuế suất 7% cho vùng khác thì sao?
Bạn có thể cho phép cùng một mã nhập CSV tự động chạy, nhưng hãy để 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 và chọn Chèn từ Điều khiển nhóm trong dải băng. Chọn nút nhấn Điều khiển ActiveX từ menu thả xuống.
Vẽ nút bấm vào bất kỳ phần nào của trang tính cách xa vị trí bất kỳ dữ liệu nào sẽ chuyển đến.
Nhấp chuột phải vào nút nhấn và chọn Thuộc tính . Trong cửa sổ Thuộc tính, hãy thay đổi Chú thích thành những gì bạn muốn hiển thị cho người dùng. Trong trường hợp này, nó 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 nhấn. Đóng thuộc tính và bấm đúp vào chính nút bấm. Thao tác này sẽ mở cửa sổ trình soạn thảo mã và con trỏ của bạn sẽ ở bên trong hàm sẽ chạy khi người dùng nhấn nút.
Dán mã tính thuế từ phần trên vào chức năng này, giữ nguyên hệ số thuế suất là 0,05. Hãy nhớ bao gồm 2 dòng sau để xác định trang tính đang hoạt động.
Dim ws As Worksheet, strFile As String
Set ws = ActiveWorkbook.Sheets("Sheet1")
Bây giờ, lặp lại quá trình một lần nữa, tạo một nút nhấn thứ hai. Đặt chú thích Tính thuế 7% .
Nhấp đúp vào nút đó và dán cùng một mã, nhưng tạo hệ số thuế là 0,07.
Bây giờ, tùy thuộc vào nút bạn nhấn, cột thuế sẽ được tính toán tương ứng.
Sau khi hoàn tất, bạn sẽ có cả hai nút nhấn trên trang tính của mình. Mỗi người trong số họ sẽ bắt đầu một cách tính thuế khác nhau và sẽ ghi 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 và chọn Chế độ thiết kế tạo nhóm Điều khiển trong ruy-băng để tắt Chế độ thiết kế . Thao tác này sẽ kích hoạt các nút nhấn.
Thử chọn từng nút nhấ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 đó
Điều gì xảy ra nếu bạn muốn gửi kết quả trên bảng tính cho ai đó qua email?
Bạn có thể tạo một nút khác có tên là Trang gửi 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 Excel CDO để đị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.
Để bậ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 Microsoft CDO cho Thư viện Windows 2000 , bậ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ừ và 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." Tất nhiên, phần nội dung cần phải động 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 một dòng tại một thời điểm vào phần 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 thông qua máy chủ SMTP của mình. Nếu bạn sử dụng Gmail, đây thường là địa chỉ email Gmail, mật khẩu Gmail của bạn và 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 thế email@website.com 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 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 bạn gặp lỗi truyền tải khi cố gắng chạy mã này, thì có thể là do tài khoản Google của bạn đang chặn "các ứng dụng kém an toàn" chạy. Bạn 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 được bật, email của bạn sẽ được gửi. Đây là giao diện của người nhận được 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 có thể thực sự tự động hóa với Excel VBA. Hãy thử xem qua các đoạn mã mà bạn đã học được trong bài viết này và tạo tự động VBA độc đáo của riêng bạn.