Hướng dẫn VBA nâng cao cho MS Excel

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(VBA guide for beginners) 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 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(Advanced VBA Guide For 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(Import) dữ liệu và thực hiện tính toán
  • Tính toán(Calculate) kết quả khi người dùng nhấn nút
  • Gửi(Email) 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 mã VBA Excel nâng cao của riêng mình.(Excel VBA)

Nhập dữ liệu và thực hiện các phép tính(Importing Data and Performing Calculations)

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 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(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(Monday) 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(Tuesday) .

Mã nhập sau sẽ chạy và nhập tệp CSV(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(Excel VBA) và chọn đối tượng Sheet1 . Từ hộp thả xuống đối tượng và phương thức, hãy chọn Trang tính(Worksheet)Kích hoạt(Activate) . 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 một hàm Sub Worksheet_Activate () . Dán đoạn mã trên vào hàm đó.

Thao tác này đặt trang tính đang hoạt động thành Sheet1 , xóa trang tính, kết nối với tệp bằng đường dẫn tệp mà bạn đã xác định với biến strFile , sau đó vòng lặp Với(With) sẽ chạy 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(CSV) được nhập vào bảng tính trống của bạn, trong Trang(Sheet1) tính 1 .

Nhập khẩu 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à:

  1. Tạo cột kết quả mới được gọi là thuế(taxes) .
  2. Lặp qua cột đơn vị đã bán(units sold) và tính thuế doanh thu.
  3. 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.

Bây giờ, mỗi khi bạn mở trang tính Excel của mình , 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(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ừ nút bấm(Calculate Results From Button Press)

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(Control) 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 tab Nhà phát triển và chọn (Developer)Chèn(Insert) từ nhóm Điều khiển(Controls) trong ruy-băng. Chọn nút nhấn (push button) ActiveX Control từ menu thả xuống.

Vẽ nút bấm lên 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.

Bấm chuột phải vào nút ấn và chọn Thuộc tính(Properties) . Trong cửa sổ Thuộc tính(Properties) , 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à Calculate 5% Tax .

Bạn sẽ thấy văn bản này được phản ánh trên chính nút nhấn. Đóng cửa sổ thuộc tính(properties) và nhấp đú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 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ữ 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 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ạo chú thích Calculate 7% Tax .

Bấm đúp vào(Double-click) 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ập văn bản này, hãy chọn menu Nhà phát triển(Developer) và chọn Chế độ thiết kế(Design Mode) , tạo thành nhóm Điều khiển(Controls) trong ruy-băng để tắt Chế độ thiết kế(Design Mode) . Thao tác này sẽ kích hoạt các nút nhấn. 

Hãy 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 đó(Email Calculation Results to Someone)

Điều gì sẽ 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 tới Sếp(Email Sheet to Boss) 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 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à Tham khảo(Tools and References) . Cuộn xuống Thư viện Microsoft CDO cho Windows 2000(Microsoft CDO for Windows 2000 Library) , 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ừ(From) , 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, 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 (Gmail) máy chủ (Gmail)SMTP Gmail(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 các 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 ý(Note) : Nếu bạn gặp lỗi truyền tải khi cố gắng 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" chạy. Bạn cần truy cập trang cài đặt ứng dụng kém an toàn hơn(less secure apps settings page) 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 đượ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 trong bài viết này và tạo các tự động VBA độc đáo của riêng bạn .



About the author

Tôi là nhà phát triển phần mềm miễn phí và là người ủng hộ Windows Vista / 7. Tôi đã viết hàng trăm bài báo về các chủ đề khác nhau liên quan đến hệ điều hành, bao gồm các mẹo và thủ thuật, hướng dẫn sửa chữa và các phương pháp hay nhất. Tôi cũng cung cấp các dịch vụ tư vấn liên quan đến văn phòng thông qua công ty của tôi, Dịch vụ Bộ phận Trợ giúp. Tôi hiểu sâu về cách hoạt động, các tính năng của Office 365 và cách sử dụng chúng hiệu quả nhất.



Related posts