Tổng Hợp Code Vba Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Có thể bạn quan tâm: Hướng Dẫn Chi Tiết Cách Tạo Thẻ Kho Trên Excel: Từ Cơ Bản Đến Nâng Cao

Mở Đầu

VBA (Visual Basic for Applications) là ngôn ngữ lập trình tích hợp sẵn trong Microsoft Excel, cho phép người dùng tự động hoá các công việc lặp đi lặp lại, tạo ra các chức năng tùy chỉnh và xây dựng các ứng dụng phức tạp ngay trong môi trường bảng tính. Với sức mạnh của VBA, bạn có thể:

• Tự động hoá quy trình nhập, xử lý và xuất dữ liệu.
• Tạo các form nhập liệu, báo cáo động và biểu đồ tùy chỉnh.
• Kết nối Excel với các nguồn dữ liệu khác như Access, SQL Server, hoặc các API web.
• Xây dựng các công cụ kiểm tra, xác thực dữ liệu và bảo mật.

Bài viết này sẽ tổng hợp hơn 1500 chữ các đoạn code VBA Excel thông dụng, kèm theo giải thích chi tiết, ví dụ thực tế và mẹo tối ưu hoá. Nội dung được chia thành các phần:

1. Cơ bản về VBA và môi trường phát triển
2. Các thủ tục (Sub) và hàm (Function) thường dùng
3. Xử lý dữ liệu trong Worksheet
4. Tự động hoá format và styling
5. Làm việc với Workbook và Worksheet
6. Sử dụng Form và Control
7. Kết nối tới cơ sở dữ liệu và API
8. Mẹo tối ưu hoá, debug và bảo mật
9. Dự án mẫu: Xây dựng hệ thống quản lý bán hàng

Có thể bạn quan tâm: Cách Tạo Qr Code Trong Excel: Hướng Dẫn Chi Tiết Từ A Đến Z

1. Cơ Bản Về VBA Và Môi Trường Phát Triển

1.1. Mở Trình Soạn Thảo VBA

• Phím tắt: Alt + F11 mở cửa sổ Microsoft Visual Basic for Applications.
• Project Explorer: hiển thị cấu trúc workbook (Modules, ThisWorkbook, Sheet1…).
• Properties Window: cho phép chỉnh sửa thuộc tính của object đang chọn.

1.2. Cấu Trúc Cơ Bản Của Một Macro

Sub TenMacro() ' Mô tả ngắn gọn về chức năng MsgBox "Hello, VBA!"
End Sub

• Sub bắt đầu một thủ tục không trả về giá trị.
• Function trả về giá trị và có thể được gọi trong công thức Excel.

1.3. Kiểu Dữ Liệu Cơ Bản

Có thể bạn quan tâm: Tạo Nút Tăng Giảm Trong Excel: Hướng Dẫn Chi Tiết Từ A Đến Z

2. Các Thủ Tục (Sub) và Hàm (Function) Thông Dụng

2.1. Thủ Tục Đọc Dữ Liệu Từ Ô

Sub DocDuLieu() Dim giaTri As Variant giaTri = ThisWorkbook.Sheets("Data").Range("A1").Value MsgBox "Giá trị ô A1 là: " & giaTri
End Sub

2.2. Hàm Tính Tổng Có Điều Kiện

Function TongCoDieuKien(rng As Range, dieuKien As String) As Double Dim cell As Range Dim tong As Double For Each cell In rng If cell.Offset(0, 1).Value = dieuKien Then tong = tong + cell.Value End If Next cell TongCoDieuKien = tong
End Function

Ví dụ: =TongCoDieuKien(A2:A100, "Bán") trả về tổng của cột A khi cột B = “Bán”.

2.3. Thủ Tục Định Dạng Ô

Sub DinhDangO() With ThisWorkbook.Sheets("Report").Range("B2:B20") .NumberFormat = "#,##0.00_);Red(#,##0.00)" .Font.Bold = True .Interior.Color = RGB(220, 230, 241) ' Màu nền nhạt xanh End With
End Sub

2.4. Hàm Kiểm Tra Email Hợp Lệ

Function KiemTraEmail(email As String) As Boolean Dim regex As Object Set regex = CreateObject("VBScript.RegExp") regex.Pattern = "^\w\.-+@\w\.-+\.\w{2,4}$" regex.IgnoreCase = True KiemTraEmail = regex.Test(email)
End Function

3. Xử Lý Dữ Liệu Trong Worksheet

3.1. Lọc Dữ Liệu Tự Động (AutoFilter)

Sub LocDuLieu() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Data") ws.AutoFilterMode = False ' Xóa filter cũ ws.Range("A1:D1000").AutoFilter Field:=3, Criteria1:=">1000"
End Sub

• Field là số cột trong vùng filter (cột C = 3).
• Criteria1 có thể là chuỗi, số, hoặc mảng.

3.2. Sao Chép Dữ Liệu Đã Lọc Sang Sheet Khác

Sub SaoChepDuLieuLoc() Dim src As Worksheet, dst As Worksheet Set src = ThisWorkbook.Sheets("Data") Set dst = ThisWorkbook.Sheets("Filtered") src.AutoFilterMode = False src.Range("A1:D1000").AutoFilter Field:=2, Criteria1:="=Hoạt động" src.Range("A1:D1000").SpecialCells(xlCellTypeVisible).Copy _ Destination:=dst.Range("A1") src.AutoFilterMode = False
End Sub

3.3. Xóa Dòng Trùng Lặp

Sub XoaDongTrung() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Data") ws.Range("A1").CurrentRegion.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End Sub

• Columns là mảng chỉ định các cột cần xét trùng lặp.

3.4. Tìm Kiếm Giá Trị Và Đánh Dấu

Sub TimVaDau() Dim ws As Worksheet, rng As Range, cell As Range Set ws = ThisWorkbook.Sheets("Data") Set rng = ws.Range("A2:A500") For Each cell In rng If cell.Value = "Cần kiểm tra" Then cell.Interior.Color = RGB(255, 199, 206) ' Đỏ nhạt End If Next cell
End Sub

4. Tự Động Hoá Format và Styling

4.1. Tạo Header Bảng Đẹp

Sub TaoHeader() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Report") With ws.Range("A1:E1") .Font.Name = "Calibri" .Font.Size = 12 .Font.Bold = True .Interior.Color = RGB(79, 129, 189) ' Xanh đậm .Font.Color = vbWhite .HorizontalAlignment = xlCenter End With
End Sub

4.2. Định Dạng Conditional Formatting (Định Dạng Điều Kiện)

Sub DinhDangDK() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Data") With ws.Range("C2:C100") .FormatConditions.Delete .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=5000" .FormatConditions(1).Interior.Color = RGB(255, 199, 206) ' Nền đỏ nhạt End With
End Sub

4.3. Thêm Border Cho Vùng Dữ Liệu

Sub ThemBorder() Dim rng As Range Set rng = ThisWorkbook.Sheets("Report").Range("A1:D20") With rng.Borders .LineStyle = xlContinuous .Weight = xlMedium .Color = RGB(0, 0, 0) End With
End Sub

5. Làm Việc Với Workbook và Worksheet

5.1. Mở Workbook Từ Đường Dẫn

Sub MoWorkbook() Dim wb As Workbook Dim duongDan As String duongDan = "C:\Data\Sales2025.xlsx" Set wb = Workbooks.Open(Filename:=duongDan, ReadOnly:=True) MsgBox "Workbook đã mở: " & wb.Name
End Sub

5.2. Lưu Workbook Với Tên Mới (Save As)

Có thể bạn quan tâm: Tất Cả Các Hàm Excel: Hướng Dẫn Toàn Diện Từ Cơ Bản Đến Nâng Cao

Sub LuuWorkbookMoi() Dim wb As Workbook Set wb = ThisWorkbook Dim tenMoi As String tenMoi = "Sales_Report_" & Format(Date, "yyyymmdd") & ".xlsx" wb.SaveAs Filename:=ThisWorkbook.Path & "\" & tenMoi, FileFormat:=xlOpenXMLWorkbook MsgBox "Đã lưu dưới tên: " & tenMoi
End Sub

5.3. Đóng Workbook Khi Hoàn Thành

Sub DongWorkbook() Dim wb As Workbook Set wb = Workbooks("Sales2025.xlsx") wb.Close SaveChanges:=False MsgBox "Workbook đã đóng."
End Sub

5.4. Thêm, Xóa, Đổi Tên Sheet

Sub QuanLySheet() Dim ws As Worksheet ' Thêm sheet mới Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) ws.Name = "Summary" ' Đổi tên sheet cũ ThisWorkbook.Sheets("Data").Name = "RawData" ' Xóa sheet tạm thời Application.DisplayAlerts = False ThisWorkbook.Sheets("Temp").Delete Application.DisplayAlerts = True
End Sub

6. Sử Dụng Form và Control

6.1. Tạo UserForm Đơn Giản

1. Trong VBA Editor, Insert > UserForm.
2. Thêm các control: Label, TextBox, CommandButton.

Mã cho nút “OK”:

Private Sub cmdOK_Click() Dim ten As String, tuoi As Long ten = Me.txtTen.Value tuoi = CLng(Me.txtTuoi.Value) If ten = "" Or tuoi <= 0 Then MsgBox "Vui lòng nhập đầy đủ thông tin!", vbExclamation Exit Sub End If ' Ghi dữ liệu vào Sheet1 With ThisWorkbook.Sheets("Data") Dim lastRow As Long lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 .Cells(lastRow, 1).Value = ten .Cells(lastRow, 2).Value = tuoi End With MsgBox "Đã lưu thông tin!", vbInformation Unload Me
End Sub

6.2. Gọi Form Từ Macro

Sub MoForm() UserForm1.Show
End Sub

6.3. Sử Dụng ActiveX Control Trong Worksheet

• Button: Insert > ActiveX Controls > Command Button.
• Gán macro:

Private Sub CommandButton1_Click() Call MoForm
End Sub

7. Kết Nối Tới Cơ Sở Dữ Liệu Và API

7.1. Kết Nối SQL Server Với ADODB

Sub KetNoiSQL() Dim conn As Object, rs As Object Dim sql As String Set conn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") conn.ConnectionString = "Provider=SQLOLEDB;Data Source=SERVERNAME;Initial Catalog=DBName;Integrated Security=SSPI;" conn.Open sql = "SELECT TOP 10 FROM dbo.Sales ORDER BY SaleDate DESC" rs.Open sql, conn, 1, 3 ' Ghi dữ liệu ra Sheet2 ThisWorkbook.Sheets("SQLData").Range("A2").CopyFromRecordset rs rs.Close conn.Close Set rs = Nothing Set conn = Nothing
End Sub

7.2. Gửi Yêu Cầu GET tới API Web

Sub GoiAPI() Dim http As Object, url As String, response As String Set http = CreateObject("MSXML2.XMLHTTP") url = "https://api.example.com/v1/customers?status=active" http.Open "GET", url, False http.setRequestHeader "Accept", "application/json" http.send If http.Status = 200 Then response = http.responseText ' Xử lý JSON (cần thư viện VBA-JSON) Dim json As Object Set json = JsonConverter.ParseJson(response) ' Ví dụ: ghi tên khách hàng vào Sheet Dim i As Long: i = 2 Dim item As Variant For Each item In json("data") ThisWorkbook.Sheets("APIData").Cells(i, 1).Value = item("id") ThisWorkbook.Sheets("APIData").Cells(i, 2).Value = item("name") i = i + 1 Next item Else MsgBox "Lỗi khi gọi API: " & http.Status & " - " & http.statusText End If
End Sub

Lưu ý: Để parse JSON, tải và import mô-đun JsonConverter.bas từ GitHub (VBA‑JSON).

7.3. Ghi Dữ Liệu Vào Access

Sub GhiVaoAccess() Dim conn As Object, sql As String Set conn = CreateObject("ADODB.Connection") conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\MyDB.accdb;" conn.Open sql = "INSERT INTO Customers (CustomerID, Name, City) VALUES (?, ?, ?)" Dim cmd As Object Set cmd = CreateObject("ADODB.Command") With cmd .ActiveConnection = conn .CommandText = sql .Parameters.Append .CreateParameter("p1", 3, 1, 10, "C001") ' adVarChar .Parameters.Append .CreateParameter("p2", 8, 1, 255, "Nguyễn Văn A") ' adVarWChar .Parameters.Append .CreateParameter("p3", 8, 1, 100, "Hà Nội") .Execute End With conn.Close Set conn = Nothing MsgBox "Đã ghi dữ liệu vào Access."
End Sub

8. Mẹo Tối Ưu Hoá, Debug Và Bảo Mật

8.1. Sử Dụng Option Explicit

Luôn đặt Option Explicit ở đầu mỗi module để buộc khai báo biến, tránh lỗi typo.

Option Explicit

8.2. Thêm Error Handling

Sub ViDuErrorHandling() On Error GoTo ErrHandler Dim wb As Workbook Set wb = Workbooks.Open("C:\Data\MissingFile.xlsx") ' Thực hiện công việc Exit Sub
ErrHandler: MsgBox "Có lỗi xảy ra: " & Err.Description, vbCritical ' Ghi log vào sheet Log With ThisWorkbook.Sheets("Log") Dim lr As Long lr = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 .Cells(lr, 1).Value = Now .Cells(lr, 2).Value = Err.Number .Cells(lr, 3).Value = Err.Description End With Resume Next
End Sub

8.3. Tối Ưu Tốc Độ Với ScreenUpdating và Calculation

Sub TocDo() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual ' Code nặng Dim i As Long For i = 1 To 100000 ThisWorkbook.Sheets("Data").Cells(i, 1).Value = i 2 Next i Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True
End Sub

8.4. Bảo Mật Mã VBA

• Mật khẩu dự án VBA: Tools > VBAProject Properties > Protection.
• Lock VBA project để ngăn người dùng xem/ sửa code.

8.5. Kiểm Tra Tốc Độ Thực Thi

Sub DongThoiGian() Dim t0 As Double, t1 As Double t0 = Timer ' Code cần đo Call TocDo t1 = Timer MsgBox "Thời gian thực thi: " & Format(t1 - t0, "0.00") & " giây"
End Sub

9. Dự Án Mẫu: Xây Dựng Hệ Thống Quản Lý Bán Hàng

9.1. Yêu Cầu

• Nhập đơn hàng qua UserForm.
• Lưu dữ liệu vào sheet Orders.
• Tự động cập nhật tồn kho trên sheet Inventory.
• Gửi email xác nhận đơn hàng.

9.2. Cấu Trúc Workbook

9.3. Mã VBA

9.3.1. UserForm: frmOrder

• Controls: ComboBox cmbCustomer, ComboBox cmbProduct, TextBox txtQty, Label lblPrice, Label lblTotal, CommandButton btnSave.

Private Sub UserForm_Initialize() ' Nạp danh sách khách hàng Dim ws As Worksheet, rng As Range, cell As Range Set ws = ThisWorkbook.Sheets("Customers") Set rng = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row) For Each cell In rng Me.cmbCustomer.AddItem cell.Value Next cell ' Nạp danh sách sản phẩm Set ws = ThisWorkbook.Sheets("Inventory") Set rng = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row) For Each cell In rng Me.cmbProduct.AddItem cell.Value Next cell
End Sub Private Sub cmbProduct_Change() Dim ws As Worksheet, row As Long Set ws = ThisWorkbook.Sheets("Inventory") row = Application.Match(Me.cmbProduct.Value, ws.Range("A:A"), 0) If Not IsError(row) Then Me.lblPrice.Caption = Format(ws.Cells(row, 2).Value, "#,##0.00") Call TinhTongTien End If
End Sub Private Sub txtQty_Change() Call TinhTongTien
End Sub Private Sub TinhTongTien() Dim price As Double, qty As Long price = Val(Me.lblPrice.Caption) qty = Val(Me.txtQty.Text) Me.lblTotal.Caption = Format(price qty, "#,##0.00")
End Sub Private Sub btnSave_Click() Dim wsOrders As Worksheet, wsInv As Worksheet Dim lastRow As Long, prodRow As Long Dim qty As Long, stock As Long If Me.cmbCustomer.Value = "" Or Me.cmbProduct.Value = "" Or Val(Me.txtQty.Text) <= 0 Then MsgBox "Vui lòng nhập đầy đủ thông tin.", vbExclamation Exit Sub End If Set wsOrders = ThisWorkbook.Sheets("Orders") Set wsInv = ThisWorkbook.Sheets("Inventory") ' Kiểm tra tồn kho prodRow = Application.Match(Me.cmbProduct.Value, wsInv.Range("A:A"), 0) stock = wsInv.Cells(prodRow, 3).Value qty = CLng(Me.txtQty.Text) If qty > stock Then MsgBox "Số lượng yêu cầu vượt quá tồn kho.", vbCritical Exit Sub End If ' Ghi đơn hàng lastRow = wsOrders.Cells(wsOrders.Rows.Count, "A").End(xlUp).Row + 1 wsOrders.Cells(lastRow, 1).Value = "ORD" & Format(Now, "yyyymmddhhmmss") wsOrders.Cells(lastRow, 2).Value = Now wsOrders.Cells(lastRow, 3).Value = Me.cmbCustomer.Value wsOrders.Cells(lastRow, 4).Value = Me.cmbProduct.Value wsOrders.Cells(lastRow, 5).Value = qty wsOrders.Cells(lastRow, 6).Value = wsInv.Cells(prodRow, 2).Value ' Giá wsOrders.Cells(lastRow, 7).Formula = "=E" & lastRow & "F" & lastRow ' Cập nhật tồn kho wsInv.Cells(prodRow, 3).Value = stock - qty ' Gửi email (sử dụng Outlook) Call GuiEmailXacNhan(Me.cmbCustomer.Value, wsOrders.Rows(lastRow)) MsgBox "Đơn hàng đã lưu và gửi email xác nhận.", vbInformation Unload Me
End Sub

9.3.2. Gửi Email Xác Nhận

Sub GuiEmailXacNhan(customerName As String, orderRow As Range) Dim outlook As Object, mail As Object Dim body As String Set outlook = CreateObject("Outlook.Application") Set mail = outlook.CreateItem(0) ' olMailItem body = "Kính gửi " & customerName & "," & vbCrLf & vbCrLf body = body & "Cảm ơn bạn đã đặt hàng. Thông tin đơn hàng:" & vbCrLf body = body & "Mã đơn: " & orderRow.Cells(1, 1).Value & vbCrLf body = body & "Ngày: " & Format(orderRow.Cells(1, 2).Value, "dd/mm/yyyy hh:nn") & vbCrLf body = body & "Sản phẩm: " & orderRow.Cells(1, 4).Value & vbCrLf body = body & "Số lượng: " & orderRow.Cells(1, 5).Value & vbCrLf body = body & "Thành tiền: " & Format(orderRow.Cells(1, 7).Value, "#,##0.00") & " VND" & vbCrLf & vbCrLf body = body & "Chúng tôi sẽ giao hàng trong thời gian sớm nhất." & vbCrLf & vbCrLf body = body & "Trân trọng," & vbCrLf & "Bộ phận bán hàng" With mail .To = "khachhang@example.com" ' Thay đổi theo dữ liệu thực tế .Subject = "Xác nhận đơn hàng " & orderRow.Cells(1, 1).Value .Body = body .Send ' Hoặc .Display để kiểm tra trước End With Set mail = Nothing Set outlook = Nothing
End Sub

9.3.3. Macro Khởi Động

Sub MoFormDonHang() frmOrder.Show
End Sub

9.4. Kiểm Tra và Bảo Trì

• Kiểm thử: Chạy MoFormDonHang, nhập dữ liệu, kiểm tra sheet Orders và Inventory.
• Log lỗi: Thêm On Error GoTo trong các hàm để ghi vào Log.
• Bảo mật: Đặt mật khẩu cho project VBA và bảo vệ sheet Inventory để người dùng không sửa trực tiếp.

Kết Luận

Với tổng hợp code VBA Excel ở trên, bạn đã nắm được:

• Cách tạo và quản lý macro cơ bản.
• Các kỹ thuật xử lý dữ liệu, định dạng, và tương tác giữa các sheet.
• Cách xây dựng giao diện người dùng bằng UserForm và ActiveX controls.
• Kết nối tới cơ sở dữ liệu, API và gửi email tự động.
• Các mẹo tối ưu hoá hiệu năng, debug và bảo mật dự án VBA.

Hãy áp dụng những đoạn code mẫu này vào dự án thực tế của bạn, tùy biến cho phù hợp với quy trình công việc. Khi gặp khó khăn, bạn luôn có thể mở Immediate Window (Ctrl+G) để kiểm tra giá trị biến, hoặc dùng Breakpoints để dừng thực thi và bước qua từng dòng. Chúc bạn thành công trong việc tự động hoá công việc với VBA Excel!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.