VBA là một phần của bộ Microsoft Office nhiều năm. Mặc dù không có đầy đủ chức năng và sức mạnh của ứng dụng VB hoàn chỉnh, VBA cung cấp người dùng Office sự linh hoạt tích hợp sản phẩm Office và tự động hóa công việc.
Một trong những công cụ mạnh mẽ nhất trong VBA là khả năng tải toàn bộ phạm vi dữ liệu vào một biến đơn gọi là mảng (array). Bằng cách tải dữ liệu theo cách này, bạn thao tác hoặc tính toán trên phạm vi dữ liệu đó theo nhiều cách.
Vậy mảng VBA là gì? Bài viết trả lời câu hỏi, và chỉ cách dùng trong script VBA của bạn.
Mảng VBA Là Gì?
Dùng mảng VBA trong Excel rất đơn giản, nhưng hiểu khái niệm mảng hơi phức tạp nếu chưa dùng.
Hãy tưởng tượng mảng như hộp có các ngăn bên trong. Mảng một chiều (one-dimensional) là hộp với một hàng ngăn. Mảng hai chiều (two-dimensional) là hộp với hai hàng ngăn.
Bạn đặt dữ liệu vào mỗi ngăn "hộp" này theo bất kỳ thứ tự.
Đầu script VBA, cần định nghĩa "hộp" bằng cách định nghĩa mảng VBA. Vì vậy, tạo mảng chứa một tập dữ liệu (mảng một chiều), viết dòng sau.
Dim arrMyArray(1 To 6) As String
Sau trong chương trình, đặt dữ liệu vào bất kỳ ngăn mảng bằng tham chiếu ngăn trong ngoặc đơn.
arrMyArray(1) = "Ryan Dube"
Bạn tạo mảng hai chiều dùng dòng sau.
Dim arrMyArray(1 To 6, 1 To 2) As Integer
Số đầu tiên đại diện hàng, số thứ hai cột. Mảng trên chứa phạm vi 6 hàng và 2 cột.
Bạn tải bất kỳ phần tử mảng với dữ liệu như sau.
arrMyArray(1, 2) = 3
Điều này tải số 3 vào ô B1.
Mảng chứa bất kỳ kiểu dữ liệu nào như biến thường: chuỗi (strings), boolean, số nguyên (integers), float, v.v.
Số trong ngoặc đơn cũng có thể là biến. Lập trình viên thường dùng vòng lặp For đếm qua tất cả ngăn mảng và tải ô dữ liệu bảng tính vào mảng. Bạn thấy cách làm sau.
Cách Lập Trình Mảng VBA Trong Excel
Xem chương trình đơn giản nơi muốn tải thông tin từ bảng tính vào mảng nhiều chiều.
Ví dụ, bảng tính bán sản phẩm, muốn kéo tên nhân viên bán (rep), mặt hàng (item), và tổng bán (total sale) từ bảng tính.
Lưu ý trong VBA tham chiếu hàng/cột, đếm từ góc trên trái bắt đầu 1. Vì vậy cột rep là 3, cột item là 4, cột total là 7.
Để tải 3 cột này qua 11 hàng, viết script sau.
Dim arrMyArray(1 To 11, 1 To 3) As String
Dim i As Integer, j As Integer
For i = 2 To 12
For j = 1 To 3
arrMyArray(i - 1, j) = Cells(i, j).Value
Next j
Next i
Bạn nhận ra để bỏ hàng tiêu đề (header), số hàng vòng For đầu cần bắt đầu 2 thay vì 1. Nghĩa là trừ 1 cho giá trị hàng mảng khi tải giá trị ô vào mảng dùng Cells(i, j).Value.
Đặt Script Mảng VBA Ở Đâu
Để đặt chương trình mảng VBA trong Excel, dùng trình soạn thảo VBA. Truy cập bằng chọn menu Developer và chọn View Code (Xem mã) phần Controls Ribbon.
Nếu không thấy Developer menu, cần thêm. Làm vậy, chọn File và Options mở cửa sổ Excel Options.
Đổi Choose commands from (Chọn lệnh từ) thả xuống thành All Commands. Chọn Developer menu trái và chọn nút Add (Thêm) chuyển sang khung phải. Tích hộp kiểm bật và chọn OK hoàn tất.
Khi cửa sổ Code Editor mở, chắc chắn trang tính chứa dữ liệu được chọn khung trái. Chọn Worksheet hộp thả xuống trái và Activate phải. Tạo subroutine mới Worksheet_Activate().
Hàm này chạy khi file bảng tính mở. Dán mã vào khung script trong subroutine này.
Script sẽ chạy qua 12 hàng và tải tên rep cột 3, mặt hàng cột 4, tổng bán cột 7.
Khi cả 2 vòng For xong, mảng hai chiều arrMyArray chứa tất cả dữ liệu chỉ định từ trang gốc.
Thao Tác Mảng Trong Excel VBA
Giả sử muốn áp dụng thuế bán 5% cho tất cả giá bán cuối, và ghi tất cả dữ liệu sang trang mới.
Bạn làm bằng thêm vòng For khác sau vòng đầu, với lệnh ghi kết quả sang trang mới.
For k = 2 To 12
Sheets("Sheet2").Cells(k, 1).Value = arrMyArray(k - 1, 1)
Sheets("Sheet2").Cells(k, 2).Value = arrMyArray(k - 1, 2)
Sheets("Sheet2").Cells(k, 3).Value = arrMyArray(k - 1, 3)
Sheets("Sheet2").Cells(k, 4).Value = arrMyArray(k - 1, 3) * 0.05
Next k
Điều này "giải nén" (unload) toàn bộ mảng vào Sheet2, với hàng thêm chứa tổng nhân 5% cho số thuế.
Trang kết quả trông như sau.
Như thấy, mảng VBA trong Excel cực kỳ hữu ích và đa dụng như mọi thủ thuật Excel.
Ví dụ trên dùng mảng rất đơn giản. Bạn tạo mảng lớn hơn và thực hiện sắp xếp (sorting), trung bình (averaging), hoặc nhiều hàm khác trên dữ liệu lưu trong mảng.
Nếu muốn sáng tạo, thậm chí tạo 2 mảng chứa phạm vi ô từ 2 trang khác nhau, và tính toán giữa phần tử mỗi mảng.
Ứng dụng chỉ giới hạn bởi trí tưởng tượng.