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 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 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.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 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.

What Is a VBA Array in Excel and How to Program One

VBA has been part of the Microsoft Office ѕuite for many yeаrs. Whilе it doesn’t have the full functіonality and pоwer of a fυll VB aрplication, VBA provides Office users with the flexibility to integrate Office products and automate the work you do in them.

One of the most powerful tools available in VBA is the ability to load an entire range of data into a single variable called an array. By loading data in this way, you can manipulate or perform calculations on that range of data in a variety of ways.

So what is a VBA array? In this article we’ll answer that question, and show you how to use one in your own VBA script.

What Is a VBA Array?

Using a VBA array in Excel is very simple, but understanding the concept of arrays can be a little bit complex if you’ve never used one.

Think of an array like a box with sections inside it. A one-dimensional array is a box with one line of sections. A two-dimensional array is a box with two lines of sections.

You can put data into each section of this “box” in any order you like.

At the beginning of your VBA script, you need to define this “box” by defining your VBA array. So to create an array that can hold one set of data (a one-dimension array), you would write the following line.

Dim arrMyArray(1 To 6) As String

Later in your program, you can place data into any section of this array by referencing the section inside of parentheses.

arrMyArray(1) = "Ryan Dube"

You can create a two-dimensional array using the following line.

Dim arrMyArray(1 To 6,1 to 2) As Integer

The first number represents the row, and the second is the column. So the above array could hold a range with 6 rows and 2 columns.

You can load any element of this array with data as follows.

arrMyArray(1,2) = 3

This will load a 3 into the cell B1.

An array can hold any type of data as a regular variable, such as strings, boolean, integers, floats and more.

The number inside the parenthesis can also be a variable. Programmers commonly use a For Loop to count through all sections of an array and load data cells in the spreadsheet into the array. You’ll see how to do this later in this article.

How to Program a VBA Array in Excel

Let’s take a look at a simple program where you may want to load information from a spreadsheet into a multidimensional array.

As an example, let’s look at a product sales spreadsheet, where you want to pull the sales rep’s name, item, and total sale out of the spreadsheet.

Note that in VBA when you reference rows or columns, you count rows and columns starting from the top left at 1. So the rep column is 3, the item column is 4, and the total column is 7.

To load these three columns over all 11 rows, you’ll need to write the following script.

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

You’ll notice that in order to skip the header row, the row number in the first For look needs to start at 2 rather than 1. This means you need to subtract 1 for the array row value when you’re loading the cell value into the array using Cells(i, j).Value.

Where to Insert Your VBA Array Script

To place program a VBA script in Excel, you need to use the VBA editor. You can access this by selecting the Developer menu and selecting View Code in the Controls section of the ribbon.

If you don’t see the Developer in the menu, you’ll need to add it. To do this, select File and Options to open the Excel Options window.

Change the choose commands from dropdown to All Commands. Select Developer from the left menu and select the Add button to move it to the pane on the right. Select the checkbox to enable it and select OK to finish.

When the Code Editor window opens, make sure the sheet your data is in is selected in the left pane. Select Worksheet in the left dropdown and Activate in the right. This will create a new subroutine called Worksheet_Activate().

This function will run whenever the spreadsheet file is opened. Paste the code into the script pane inside this subroutine.

This script will work through the 12 rows and will load the rep name from column 3, the item from column 4, and the total sale from column 7.

Once both For loops are finished, the two dimensional array arrMyArray contains all of the data you specified from the original sheet.

Manipulating Arrays in Excel VBA

Let’s say you want to apply a 5% sales tax to all of the final sale prices, and then write all of the data to a new sheet.

You can do this by adding another For loop after the first, with a command to write the results to a new sheet.

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

This will “unload” the entire array into Sheet2, with an additional row containing the total multiplied by 5% for the tax amount.

The resulting sheet will look like this.

As you can see, VBA arrays in Excel are extremely useful and as versatile as any other Excel trick. 

The example above is a very simple use for an array. You can create much larger arrays and perform sorting, averaging, or many other functions against the data you store in them.

If you want to get real creative, you could even create two arrays containing a range of cells from two different sheets, and perform calculations between elements of each array.

The applications are limited only by your imagination.

Related posts

• Cách xóa dòng trống trong Excel

• Cách sử dụng Tính năng Nói ô trong Excel

• Cách Chèn Trang tính Excel vào Tài liệu Word

• Cách sử dụng Phân tích Điều gì xảy ra trong Excel

• Cách sửa một hàng trong Excel

• Cách Chèn CSV hoặc TSV vào một trang tính Excel

• 10 mẹo và thủ thuật Excel cho năm 2019

• Hướng dẫn VBA nâng cao cho MS Excel

• Sử dụng Công cụ Tìm kiếm Mục tiêu Phân tích Giả sử của Excel

• Cách sắp xếp thứ tự bảng chữ cái trong Excel

• Cách hợp nhất ô, cột và hàng trong Excel

• Nhóm các hàng và cột trong một trang tính Excel

• Cách loại bỏ các hàng trùng lặp trong Excel

• Cách tạo biểu đồ hình tròn trong Excel

• Khi nào sử dụng khớp chỉ mục thay vì hàm VLOOKUP trong Excel

• Kết nối Excel với MySQL

• Cách chia sẻ tệp Excel để cộng tác dễ dàng

• Cách viết công thức / câu lệnh IF trong Excel

• Cách sử dụng câu lệnh If và câu lệnh If lồng nhau trong Excel

• Căn giữa dữ liệu trang tính của bạn trong Excel để in