VBA đã là một phần của bộ Microsoft Office trong nhiều năm. Mặc dù nó không có đầy đủ chức năng và sức mạnh của một ứng dụng VB đầy đủ, nhưng VBA cung cấp cho người dùng Office sự linh hoạt để tích hợp các sản phẩm Office và tự động hóa công việc bạn thực hiện trong đó.
Một trong những công cụ mạnh mẽ nhất có sẵn trong VBA là khả năng tải toàn bộ phạm vi dữ liệu vào một biến duy nhất gọi là mảng. Bằng cách tải dữ liệu theo cách này, bạn có thể thao tác hoặc thực hiện các phép tính trên phạm vi dữ liệu đó theo nhiều cách khác nhau.
Vậy mảng VBA là gì? Trong bài viết này, chúng tôi sẽ trả lời câu hỏi đó và chỉ cho bạn cách sử dụng câu hỏi đó trong tập lệnh VBA của riêng bạn.
Nội dung bài viết
Mảng VBA là gì?
Sử dụng mảng VBA trong Excel rất đơn giản nhưng việc hiểu khái niệm mảng có thể hơi phức tạp nếu bạn chưa từng sử dụng mảng.
Hãy tưởng tượng mảng giống như một chiếc hộp có các phần bên trong. Mảng một chiều là một hộp có một dòng các phần. Mảng hai chiều là một hộp có hai dòng phần.
Bạn có thể đưa dữ liệu vào từng phần của “hộp” này theo bất kỳ thứ tự nào bạn muốn.
Khi bắt đầu tập lệnh VBA, bạn cần xác định “hộp” này bằng cách xác định mảng VBA của mình. Vì vậy, để tạo một mảng có thể chứa một bộ dữ liệu (mảng một chiều), bạn sẽ viết dòng sau.
Dim arrMyArray(1 To 6) As String
Sau này trong chương trình của bạn, bạn có thể đặt dữ liệu vào bất kỳ phần nào của mảng này bằng cách tham chiếu phần bên trong dấu ngoặc đơn.
arrMyArray(1) = "Ryan Dube"
Bạn có thể tạo mảng hai chiều bằng dòng sau.
Dim arrMyArray(1 To 6,1 to 2) As Integer
Số đầu tiên đại diện cho hàng và số thứ hai đại diện cho cột. Vì vậy, mảng trên có thể chứa một phạm vi có 6 hàng và 2 columns.
Bạn có thể tải bất kỳ phần tử nào của mảng này bằng dữ liệu như sau.
arrMyArray(1,2) = 3
Điều này sẽ tải số 3 vào ô B1.
Một mảng có thể chứa bất kỳ loại dữ liệu nào dưới dạng một biến thông thường, chẳng hạn như chuỗi, boolean, số nguyên, số float, v.v.
Số bên trong dấu ngoặc đơn cũng có thể là một biến. Các lập trình viên thường sử dụng Vòng lặp For để đếm qua tất cả các phần của một mảng và tải các ô dữ liệu trong bảng tính vào mảng đó. Bạn sẽ biết cách thực hiện điều này sau trong bài viết này.
Cách lập trình mảng VBA trong Excel
Chúng ta hãy xem một chương trình đơn giản trong đó bạn có thể muốn tải thông tin từ bảng tính vào một mảng đa chiều.
Ví dụ: hãy xem bảng tính bán sản phẩm, nơi bạn muốn lấy tên, mặt hàng và tổng doanh số bán hàng của đại diện bán hàng ra khỏi bảng tính.
Lưu ý rằng trong VBA khi bạn tham chiếu các hàng hoặc columnsbạn đếm hàng và columns bắt đầu từ trên cùng bên trái tại 1. Vậy cột đại diện là 3, cột mục là 4 và cột tổng là 7.
Để tải ba cái này columns trên tất cả 11 hàng, bạn sẽ cần viết đoạn mã 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 sẽ nhận thấy rằng để bỏ qua hàng tiêu đề, số hàng trong giao diện For đầu tiên cần bắt đầu từ 2 thay vì 1. Điều này có nghĩa là bạn cần trừ 1 cho giá trị hàng mảng khi bạn tải giá trị ô vào mảng bằng Cells(i, j).Value.
Nơi chèn tập lệnh mảng VBA của bạn
Để đặt chương trình tập lệnh VBA trong Excel, bạn cần sử dụng trình soạn thảo VBA. Bạn có thể truy cập mục này bằng cách chọn Nhà phát triển thực đơn và lựa chọn Xem mã trong phần Điều khiển của dải băng.
Nếu bạn không thấy Nhà phát triển trong menu, bạn sẽ cần thêm nó. Để thực hiện việc này, hãy chọn Tài liệu Và Tùy chọn để mở cửa sổ Tùy chọn Excel.
Thay đổi các lệnh chọn từ danh sách thả xuống thành Tất cả các lệnh. Lựa chọn Nhà phát triển từ menu bên trái và chọn Thêm vào để di chuyển nó sang khung bên phải. Chọn hộp kiểm để kích hoạt nó và chọn OK kêt thuc.
Khi cửa sổ Code Editor mở ra, hãy đảm bảo trang tính chứa dữ liệu của bạn được chọn ở khung bên trái. Lựa chọn Bảng tính trong danh sách thả xuống bên trái và Kích hoạt ở bên phải. Thao tác này sẽ tạo một chương trình con mới có tên Worksheet_Activate().
Hàm này sẽ chạy bất cứ khi nào tệp bảng tính được mở. Dán mã vào khung tập lệnh bên trong chương trình con này.
Tập lệnh này sẽ hoạt động qua 12 hàng và sẽ tải tên đại diện từ cột 3, mặt hàng từ cột 4 và tổng doanh thu từ cột 7.
Sau khi cả hai vòng lặp For kết thúc, mảng hai chiều arrMyArray chứa tất cả dữ liệu bạn đã chỉ định từ trang tính gốc.
Thao tác với mảng trong Excel VBA
Giả sử bạn muốn áp dụng thuế bán hàng 5% cho tất cả giá bán cuối cùng, sau đó ghi tất cả dữ liệu vào một trang tính mới.
Bạn có thể thực hiện việc này bằng cách thêm một vòng lặp For khác sau vòng lặp đầu tiên, kèm theo lệnh ghi kết quả vào một trang tính 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
Thao tác này sẽ “dỡ” toàn bộ mảng vào Trang tính 2, với một hàng bổ sung chứa tổng số nhân với 5% cho số tiền thuế.
Bảng kết quả sẽ trông như thế này.
Như bạn có thể thấy, mảng VBA trong Excel cực kỳ hữu ích và linh hoạt như bất kỳ thủ thuật Excel nào khác.
Ví dụ trên là cách sử dụng rất đơn giản của mảng. Bạn có thể tạo các mảng lớn hơn nhiều và thực hiện sắp xếp, tính trung bình hoặc nhiều chức năng khác đối với dữ liệu bạn lưu trữ trong đó.
Nếu muốn thực sự sáng tạo, bạn thậm chí có thể tạo hai mảng chứa một phạm vi ô từ hai trang tính khác nhau và thực hiện các phép tính giữa elements của từng mảng.
Các ứng dụng chỉ bị giới hạn bởi trí tưởng tượng của bạn.
