Mảng VBA Trong Excel Là Gì Và Cách Lập Trình Một

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 làm 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ì? (VBA)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 mã trong tập lệnh VBA của riêng bạn(your own VBA script) .

Mảng VBA là gì?

Sử dụng mảng VBA trong (VBA)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 từng sử dụng.

Hãy nghĩ(Think) 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. (VBA)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 đến phần bên trong dấu ngoặc đơn.

arrMyArray(1) = "Ryan Dube"

Bạn có thể tạo một mảng hai chiều bằng cách sử dụ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(Loop) 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 sau trong 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ụ: chúng ta 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 cột, bạn đếm các hàng và cột(rows and columns) 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 đoạn mã sau.

Dim arrMyArray(1 To 11, 1 To 3) As StringDim 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 jNext 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 sử dụng Cells (i, j) .Value.

Chèn tập lệnh mảng VBA của bạn ở đâu(Your VBA Array Script)

Để đặ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 menu Nhà phát triển(Developer) và chọn Mã Chế độ xem(View Code) trong phần Điều khiển(Controls) của ruy-băng.

Nếu bạn không thấy Nhà phát triển(Developer) trong menu, bạn cần phải thêm nó. Để thực hiện việc này, hãy chọn Tệp(File) và Tùy chọn(Options) để mở cửa sổ Tùy chọn Excel.

Thay đổi các lệnh chọn từ menu thả xuống thành Tất cả các lệnh(All Commands) . Chọn Nhà phát triển(Developer) từ menu bên trái và chọn nút Thêm(Add) để di chuyển nó sang ngăn bên phải. Chọn hộp kiểm để bật nó và chọn OK để hoàn tất.

Khi cửa sổ Trình soạn thảo mã(Code Editor) 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(Worksheet) ở menu thả xuống bên trái và Kích hoạt(Activate) ở 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 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ả cá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, với 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.05Next 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 nhân với 5% cho số tiền thuế.

Trang tính kết quả sẽ như thế này.

Như bạn có thể thấy, mảng VBA trong (VBA)Excel cực kỳ hữu ích và linh hoạt như bất kỳ thủ thuật Excel nào khác(any other Excel trick) . 

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 sự sáng tạo thực sự, bạn thậm chí có thể tạo hai mảng chứa một dải ô(containing a range of cells) 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.