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 nhất hiện có 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 được 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à hướng dẫn bạn cách sử dụng một câu hỏi trong tập lệnh VBA của riêng bạn.
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 về mảng có thể hơi phức tạp nếu bạn chưa bao giờ sử dụng.
Hãy nghĩ về một mảng giống như một cái hộp với các phần bên trong nó. 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 bạn. Vì vậy, để tạo một mảng có thể chứa một tập 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 đó 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 là cột. Vì vậy, mảng ở trên có thể chứa một phạm vi có 6 hàng và 2 cột.
Bạn có thể tải bất kỳ phần tử nào của mảng này với dữ liệu như sau.
arrMyArray(1,2) = 3
Thao tác 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 biến thông thường, chẳng hạn như chuỗi, boolean, số nguyên, float và hơn thế nữa.
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ẽ thấy cách thực hiện việc này ở phần sau của bài viết này.
Cách lập trình mảng VBA trong Excel
Hãy xem một chương trình đơn giản mà 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ố 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 cột, bạn đếm các hàng và cột bắt đầu từ trên cùng bên trái là 1. Vì vậy, cột đại diện là 3, cột mục là 4 và tổng cột là 7.
Để tải ba cột này trên tất cả 11 hàng, bạn cần viết tập lệnh 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 Cho cái nhìn đầu tiên cần phải bắt đầu bằng 2 thay vì 1. Điều này có nghĩa là bạn cần trừ 1 cho giá trị hàng của mảng khi bạn đang tải giá trị ô vào mảng bằng cách sử dụng Cells (i, j) .Value.
Chèn tập lệnh mảng VBA của bạn ở đâu
Để đặt chương trình một 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 phần này bằng cách chọn Nhà phát triển và chọn Xem mã trong phần Điều khiển của ruy-băng.
Nếu bạn không thấy Nhà phát triển trong menu, bạn cần thêm Nhà phát triển đó. Để thực hiện việc này, hãy chọn Tệp và Tùy chọn để mở cửa sổ Tùy chọn Excel.
Thay đổi các lệnh chọn từ trình đơn thả xuống thành Tất cả các lệnh . Chọn Nhà phát triển từ trình đơn bên trái và chọn Thêm để di chuyển nó sang ngăn bên phải. Chọn hộp kiểm để bật nó và chọn OK để kết thúc.
Khi cửa sổ Trình soạn thảo mã mở ra, hãy đảm bảo rằng trang tính dữ liệu của bạn được chọn trong ngăn bên trái. Chọn Trang tính trong menu 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 ngăn 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 số bán hàng từ cột 7.
Khi cả hai vòng lặp For kết thúc, mảng arrMyArray hai chiều chứa tất cả dữ liệu bạn đã chỉ định từ trang tính ban đầu.
Thao tác 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á ưu đãi 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, bằng 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 Sheet2, với một hàng bổ sung chứa tổng số tiền thuế nhân với 5%.
Trang tính kết quả sẽ giố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à một cách sử dụng rất đơn giản cho một 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 dựa trên dữ liệu bạn lưu trữ trong đó.
Nếu bạn muốn có được quảng cáo thực sự, bạn thậm chí có thể tạo hai mảng chứa một dải ô từ hai trang tính khác nhau và thực hiện các phép tính giữa các phần tử của mỗi mảng.
Các ứng dụng chỉ bị giới hạn bởi trí tưởng tượng của bạn.