Excel Automation Cho Tester: Viết Macro VBA Tạo Test Data Tự Động

6 tháng trước · 13 phút đọc

Tại sao tester cần biết VBA Macro trong Excel?

Mình nhớ lần đầu nhận task chuẩn bị test data cho module đăng ký tài khoản - 200 bộ dữ liệu: tên, email, số điện thoại, địa chỉ, ngày sinh. Ngồi nhập tay 3 tiếng, xong đến hôm sau dev báo schema đổi, phải làm lại từ đầu. Đó là lúc mình bắt đầu tìm hiểu VBA Macro.

VBA (Visual Basic for Applications) là ngôn ngữ lập trình tích hợp sẵn trong Excel. Bạn không cần cài thêm gì, không cần biết code sâu. Chỉ cần hiểu logic cơ bản là viết được macro tạo dữ liệu hàng loạt.

342482 Từ nhập tay 3 tiếng xuống còn 30 giây - đó là lý do cần VBA

Với tester, VBA hữu ích nhất ở 3 việc:

Tạo test data random hàng trăm dòng theo đúng format yêu cầu
Import/export test case giữa Excel và các tool như Jira, TestRail
Tự động hóa báo cáo bug summary sau mỗi sprint

Nhiều JD tester trên TopDev hiện nay ghi "Excel advanced" là yêu cầu. Biết VBA là điểm cộng rõ ràng so với ứng viên chỉ biết filter và pivot table. Bài này mình hướng dẫn từng bước, không cần nền tảng lập trình.

Bật VBA Editor và viết macro đầu tiên

Trước khi viết bất kỳ dòng code nào, bạn cần bật tab Developer trong Excel. Mặc định tab này bị ẩn.

Bật tab Developer:

Mở Excel → File → Options → Customize Ribbon
Ở cột bên phải, tích vào ô Developer
Bấm OK

Bây giờ bạn thấy tab Developer trên thanh ribbon. Bấm vào Visual Basic để mở VBA Editor (hoặc nhấn Alt + F11).

342483 Tab Developer và nút Visual Basic - cửa ngõ vào thế giới macro

Cửa sổ VBA Editor mở ra. Nhìn có vẻ đáng sợ nhưng bạn chỉ làm việc ở 1 vùng: Code Window (vùng trắng lớn bên phải).

Thử viết macro đầu tiên - đơn giản nhất:

Sub HelloTester()
    MsgBox "VBA hoạt động rồi!"
End Sub

Dán code này vào Code Window, bấm F5 hoặc nút Run (tam giác xanh). Nếu xuất hiện hộp thoại thông báo - chúc mừng, bạn vừa chạy macro đầu tiên.

Thấy khó ở bước này bình thường. Mình cũng mất cả buổi loay hoay với giao diện VBA Editor lần đầu. Cứ làm theo từng bước, không cần hiểu tất cả ngay.

Viết macro tạo test data random cho tính năng đăng ký

Đây là phần thực chiến nhất. Mình sẽ hướng dẫn viết macro tạo 100 bộ test data cho form đăng ký tài khoản - gồm: STT, Họ tên, Email, Số điện thoại, Ngày sinh.

Tạo sheet mới tên "TestData", sau đó mở VBA Editor và dán code sau:

Sub TaoTestData()
    ' Khai báo biến
    Dim ws As Worksheet
    Dim i As Integer
    Dim soLuong As Integer
    
    ' Trỏ đến sheet TestData
    Set ws = ThisWorkbook.Sheets("TestData")
    
    ' Xóa dữ liệu cũ từ dòng 2 trở xuống
    ws.Range("A2:E101").ClearContents
    
    ' Tạo header
    ws.Range("A1").Value = "STT"
    ws.Range("B1").Value = "Ho Ten"
    ws.Range("C1").Value = "Email"
    ws.Range("D1").Value = "So Dien Thoai"
    ws.Range("E1").Value = "Ngay Sinh"
    
    ' Mảng tên ngẫu nhiên
    Dim danhSachTen(9) As String
    danhSachTen(0) = "Nguyen Van A"
    danhSachTen(1) = "Tran Thi B"
    danhSachTen(2) = "Le Van C"
    danhSachTen(3) = "Pham Thi D"
    danhSachTen(4) = "Hoang Van E"
    danhSachTen(5) = "Bui Thi F"
    danhSachTen(6) = "Dang Van G"
    danhSachTen(7) = "Do Thi H"
    danhSachTen(8) = "Vu Van I"
    danhSachTen(9) = "Ngo Thi K"
    
    soLuong = 100
    
    ' Vòng lặp tạo dữ liệu
    For i = 1 To soLuong
        ' STT
        ws.Cells(i + 1, 1).Value = i
        
        ' Họ tên random từ mảng
        ws.Cells(i + 1, 2).Value = danhSachTen(Int(Rnd() * 10))
        
        ' Email tự động theo STT
        ws.Cells(i + 1, 3).Value = "testuser" & i & "@testmail.com"
        
        ' SĐT: 09X + 7 số random
        ws.Cells(i + 1, 4).Value = "09" & Int(Rnd() * 8) & _
            Format(Int(Rnd() * 10000000), "0000000")
        
        ' Ngày sinh random từ 1990-2000
        Dim ngay As Integer, thang As Integer, nam As Integer
        ngay = Int(Rnd() * 28) + 1
        thang = Int(Rnd() * 12) + 1
        nam = 1990 + Int(Rnd() * 11)
        ws.Cells(i + 1, 5).Value = Format(ngay, "00") & "/" & _
            Format(thang, "00") & "/" & nam
    Next i
    
    MsgBox "Tao xong " & soLuong & " bo test data!"
End Sub

342484 100 dòng test data hoàn chỉnh - tạo trong chưa đầy 2 giây

Bấm F5 chạy macro. Sheet TestData sẽ tự động điền 100 dòng dữ liệu. Mỗi lần chạy lại cho ra bộ số điện thoại và ngày sinh khác nhau.

Điểm quan trọng: Rnd() trong VBA tạo số ngẫu nhiên. Int() làm tròn xuống thành số nguyên. Kết hợp Int(Rnd() * N) cho ra số từ 0 đến N-1. Hiểu 2 hàm này là hiểu 80% logic tạo test data random.

Macro xuất test case ra file CSV để import vào Jira

Tạo test data xong rồi, bước tiếp theo nhiều team yêu cầu là export test case sang CSV để import vào Jira hoặc TestRail. Làm thủ công thì copy-paste từng sheet, rất mất thời gian và dễ sai format.

Macro dưới đây đọc toàn bộ test case trong sheet "TestCase" và xuất ra file CSV cùng thư mục với file Excel:

Sub ExportTestCaseCSV()
    Dim ws As Worksheet
    Dim filePath As String
    Dim fileNum As Integer
    Dim i As Long, j As Integer
    Dim dongCuoi As Long
    Dim cotCuoi As Integer
    Dim dongData As String
    
    ' Trỏ đến sheet chứa test case
    Set ws = ThisWorkbook.Sheets("TestCase")
    
    ' Đường dẫn file CSV (cùng thư mục với file Excel)
    filePath = ThisWorkbook.Path & "\TestCase_Export_" & _
        Format(Now(), "yyyymmdd_hhmmss") & ".csv"
    
    ' Tìm dòng cuối và cột cuối có dữ liệu
    dongCuoi = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    cotCuoi = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    ' Mở file để ghi
    fileNum = FreeFile()
    Open filePath For Output As #fileNum
    
    ' Ghi từng dòng vào CSV
    For i = 1 To dongCuoi
        dongData = ""
        For j = 1 To cotCuoi
            ' Thêm dấu ngoặc kép để tránh lỗi dấu phẩy trong dữ liệu
            dongData = dongData & """" & ws.Cells(i, j).Value & """"
            If j < cotCuoi Then dongData = dongData & ","
        Next j
        Print #fileNum, dongData
    Next i
    
    ' Đóng file
    Close #fileNum
    
    MsgBox "Export xong! File lưu tại: " & filePath
End Sub

342485 Flow export test case từ Excel sang CSV rồi import vào Jira - 3 bước tự động

Tại sao dùng dấu ngoặc kép quanh mỗi ô? Vì test case thường có dấu phẩy trong mô tả, ví dụ: "Nhập username, password hợp lệ". Nếu không đặt trong ngoặc kép, CSV sẽ hiểu dấu phẩy đó là separator và cắt dữ liệu nhầm. Bug này mình từng bỏ qua, kết quả import vào Jira bị lệch hết cột.

Mỗi lần export, file CSV tự động đặt tên theo timestamp - không bao giờ bị ghi đè file cũ. Thói quen nhỏ nhưng tránh được rất nhiều phiền phức.

Macro đọc test case từ file CSV bên ngoài vào Excel

Import ngược lại - đọc CSV vào Excel - cũng cần thiết không kém. Tình huống thường gặp: team lead gửi file CSV chứa test case template, bạn cần load vào Excel để điền kết quả test.

Sub ImportTestCaseCSV()
    Dim filePath As String
    Dim fileNum As Integer
    Dim ws As Worksheet
    Dim dongData As String
    Dim mang() As String
    Dim i As Long
    
    ' Mở hộp thoại chọn file
    filePath = Application.GetOpenFilename( _
        FileFilter:="CSV Files (*.csv), *.csv", _
        Title:="Chon file CSV test case")
    
    ' Người dùng bấm Cancel
    If filePath = "False" Then Exit Sub
    
    ' Tạo sheet mới để chứa dữ liệu import
    Set ws = ThisWorkbook.Sheets.Add
    ws.Name = "Import_" & Format(Now(), "mmdd_hhmm")
    
    ' Đọc file CSV
    fileNum = FreeFile()
    Open filePath For Input As #fileNum
    
    i = 1
    Do While Not EOF(fileNum)
        Line Input #fileNum, dongData
        
        ' Tách dữ liệu theo dấu phẩy
        mang = Split(dongData, ",")
        
        Dim j As Integer
        For j = 0 To UBound(mang)
            ' Bỏ dấu ngoặc kép nếu có
            ws.Cells(i, j + 1).Value = _
                Replace(mang(j), Chr(34), "")
        Next j
        
        i = i + 1
    Loop
    
    Close #fileNum
    
    ' Format header dòng đầu
    ws.Rows(1).Font.Bold = True
    ws.Rows(1).Interior.Color = RGB(70, 130, 180)
    ws.Rows(1).Font.Color = RGB(255, 255, 255)
    
    ' Auto-fit cột
    ws.Columns.AutoFit
    
    MsgBox "Import xong " & (i - 1) & " dong du lieu!"
End Sub

342486 Kết quả import: sheet mới tự tạo, header được format tự động - không cần chỉnh tay

Điểm mình thích nhất ở macro này: tự động tạo sheet mới đặt tên theo giờ import, tự format header màu xanh, tự auto-fit cột. Ba việc này thường mất 2-3 phút làm tay, giờ chạy xong trong 3 giây.

Chr(34) là mã ASCII của dấu ngoặc kép ". Dòng Replace(mang(j), Chr(34), "") xóa dấu ngoặc kép thừa sau khi tách chuỗi - không hiểu ngay cũng không sao, chỉ cần nhớ đặt vào để tránh dữ liệu bị thừa dấu ngoặc kép.

Template Excel sẵn dùng và cách gán macro vào nút bấm

Viết xong macro mà mỗi lần dùng phải mở VBA Editor để F5 thì khá bất tiện. Cách chuyên nghiệp hơn: gán macro vào nút bấm ngay trong sheet.

Tạo nút bấm:

Tab Insert → Shapes → chọn hình chữ nhật bo góc
Vẽ nút lên sheet, nhập tên: "Tạo Test Data"
Click chuột phải vào nút → Assign Macro → chọn TaoTestData

Từ giờ chỉ cần bấm nút là macro chạy. Bạn có thể tạo 3-4 nút cho các macro khác nhau trên cùng 1 sheet control.

342487 Dashboard macro với các nút bấm - trông professional hơn nhiều khi demo cho HR

Mình đã tạo sẵn 1 file Excel template gồm:

Sheet Control: 4 nút bấm (Tạo Test Data, Export CSV, Import CSV, Xóa Dữ Liệu)
Sheet TestData: nơi test data được tạo ra
Sheet TestCase: nơi viết test case theo format chuẩn
Sheet Config: tùy chỉnh số lượng dữ liệu, prefix email, format SĐT

Tải template tại đây: Tải file Excel Macro Template (demo HTML)

Lưu file đúng format: File Excel có macro phải lưu dạng .xlsm (Excel Macro-Enabled Workbook), không phải .xlsx. Lưu .xlsx là mất sạch code VBA, lỗi này mình mắc phải 2-3 lần trước khi nhớ.

Ngoài ra, bạn có thể tải bộ template test case chuẩn (CSV + Excel): Tải bộ Test Case Template chuẩn

Xử lý lỗi thường gặp khi chạy macro

Ba lỗi phổ biến nhất mình gặp khi mới học VBA - và cách fix:

Lỗi 1: "Subscript out of range" khi chạy macro

Nguyên nhân: tên sheet trong code không khớp với tên sheet thực tế. VBA phân biệt hoa thường và khoảng trắng.

Fix: kiểm tra lại tên sheet. Sheets("TestData") sẽ lỗi nếu sheet đặt tên là "Test Data" (có dấu cách) hoặc "testdata" (chữ thường).

Lỗi 2: Macro bị disable, không chạy được

Nguyên nhân: Excel block macro vì lý do bảo mật.

Fix: File → Options → Trust Center → Trust Center Settings → Macro Settings → chọn "Enable all macros". Khi share file cho người khác, nhắc họ enable macro trước khi dùng.

Lỗi 3: File lưu xong, mở lại không thấy macro

Nguyên nhân: lưu file dạng .xlsx thay vì .xlsm.

Fix: File → Save As → chọn Excel Macro-Enabled Workbook (*.xlsm).

342488 3 lỗi hay gặp và cách fix - bookmark lại để tra khi cần

Nếu gặp lỗi khác, mình recommend copy thông báo lỗi (màu đỏ trong VBA Editor) và paste vào Google - cộng đồng VBA rất lớn, phần lớn câu hỏi VBA trên Stack Overflow có câu trả lời được chấp nhận.

Đừng nản nếu macro lỗi lần đầu. Lập trình mà không gặp lỗi mới lạ. Quan trọng là đọc thông báo lỗi kỹ - VBA thường chỉ ra đúng dòng bị lỗi, không phải đoán mò.

Bước tiếp theo: từ Excel VBA đến automation test thực sự

Biết VBA Macro là bước đầu tốt. Nhưng khi đã quen với tư duy tự động hóa, bạn sẽ tự nhiên muốn đi xa hơn.

Path phổ biến mà nhiều tester mình biết đã đi:

Excel VBA (bài này) - tạo data, export/import
Selenium + Python/Java - automation UI testing
Postman + Newman - automation API testing
Cypress hoặc Playwright - end-to-end testing hiện đại

Bạn không cần học hết cùng lúc. Thành thạo VBA trước, sau đó tìm hiểu Selenium khi công ty yêu cầu.

342489 Lộ trình tester từ Excel VBA đến automation tool chuyên nghiệp

Nếu muốn có nền tảng bài bản từ đầu - từ manual testing, viết test case, bug report, đến các kỹ năng Excel và SQL cho tester - khóa Kiểm thử phần mềm cover khá đầy đủ các phần này, phù hợp cho bạn đang chuyển ngành hoặc mới bắt đầu.

Về Excel VBA, mình cũng tổng hợp checklist các kỹ năng cần biết ở giai đoạn đầu:

Tải Checklist Kỹ Năng Excel Cho Tester

Test kỹ 1 lần tốt hơn fix lỗi nhiều lần. Tự động hóa test data cũng vậy - tốn 1-2 giờ viết macro, tiết kiệm hàng chục giờ làm tay về sau. Comment bên dưới nếu bạn gặp lỗi ở bước nào, mình trả lời từng bạn.