Nếu bạn chỉ 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 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 và bạn đang tìm kiếm những thứ nâng cao hơn 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 sẽ mở ra cả một thế giới của tự động hóa. Bạn có thể tự động hóa các tính toán 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.
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 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.
Với ba ví dụ này, bạn nên có thể viết nhiều loại mã VBA Excel nâng cao của riêng bạn.
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 cho đang thực hiện các tính toán 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 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.
In_content_1 all: [300x250] / dfp : [640x360]->Với VBA, bạn có thể tự động hóa toàn bộ quy trình. Ví dụ: nếu bạn có 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 mỗi thứ Hai, bạn có thể định cấu hình mã VBA để chạy khi lần đầu mở bảng tính 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 StringSet 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ừ hộp thả xuống của đối tượng và phương thức, chọn Bảng tínhvà 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 hàm Sub Worksheet_Activate (). Dán mã ở trên vào hàm đó.
Điều này đặt bảng tính hoạt động thành Trang tính 1, 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 biến strFile, sau đó Vớichu kỳ vòng lặp 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 dữ liệu tệp CSV được nhập vào bảng tính trống của bạn, trong Trang tính 1.
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 thuế 5% đã trả cho việc 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à:
Đoạn mã sau sẽ thực hiện tất cả các bước sau.
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 bảng dữ liệu của bạn và 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ã vòng 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 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.
Bây giờ, mỗi khi bạn mở bảng tính Excel, nó sẽ tự động ra ngoài và lấy bản sao dữ liệu mới nhất từ tệp CSV. Sau đó, nó sẽ thực hiện các tính toán và viết kết quả vào bảng. Bạn không phải làm bất cứ điều gì thủ công nữa!
Tính kết quả từ nút Nhấn
Nếu bạn muốn kiểm soát trực tiếp hơn khi chạy tính toán , 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 tính toán 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 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 tab Nhà phát triểnvà chọn Chèntừ nhóm Điều khiểntrong ruy-băng. Chọn nút ấnĐiều khiển ActiveX từ menu thả xuống.
Vẽ nút ấn lên bất kỳ phần nào của trang tính từ nơi mọi dữ liệu sẽ đi.
Nhấp chuột phải vào nút ấn và chọn Thuộc tính. Trong cửa sổ Thuộc tính, 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 sổ thuộc tínhvà nhấp đúp vào nút ấ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.
Dán mã tính thuế từ phần trên vào chức năng này, giữ cho 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 tính hoạt động.
Dim ws As Worksheet, strFile As String
Set 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. Tạo 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 số nhân thuế 0,07.
Bây giờ, tùy thuộc vào nút nào bạn nhấn, cột thuế sẽ được tính toán phù hợp.
Khi bạn đã 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 một cách tính thuế khác nhau và sẽ viết các kết quả khác nhau vào cột kết quả.
Để nhắn tin này, chọn menu Nhà phát triểnvà chọn Chế độ thiết kếtạo thành nhóm Điều khiển trong ruy-băng để 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ách thay đổi cột kết quả thuế Thuế vụ.
Kết quả tính toán email cho ai đó
Điều gì 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 Trang tính email cho ông chủbằng cách 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 của Excel để định cấu hình cài đặt email SMTP và gửi email kết quả theo đị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 cho 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 = "[email protected]"
strTo = "[email protected]"
strCc = ""
strBcc = ""
strBody = "The following is the breakdown of taxes paid on sales this quarter."
Tất nhiên, cơ thể cần phải năng động tùy thuộc vào kết quả nào 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 thân.
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ủ 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 với 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 đây.
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 thấy lỗi vận chuyển khi cố chạy mã này, 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 hơn của LINE chạy. Bạn cần phải truy cập trang cài đặt ứng dụng kém an toàn và bật tính năng này.
Sau khi bậ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.