VBA多年来一直是Microsoft Office套件的一部分。虽然它不具备完整 VB 应用程序的全部功能和功能，但VBA为Office用户提供了集成Office产品和自动化您在其中执行的工作的灵活性。
VBA中最强大的工具之一是将整个数据范围加载到称为数组的单个变量中的能力。通过以这种方式加载数据，您可以以多种方式对该范围的数据进行操作或计算。
那么什么是VBA数组呢？在本文中，我们将回答这个问题，并向您展示如何在您自己的 VBA 脚本(your own VBA script)中使用它。
什么是 VBA 数组？
在Excel(Excel)中使用VBA数组非常简单，但如果您从未使用过数组，那么理解数组的概念可能会有点复杂。
将数组想象(Think)成一个盒子，里面有部分。一维数组是一个有一行部分的盒子。二维数组是一个有两行截面的盒子。
您可以按照您喜欢的任何顺序将数据放入此“框”的每个部分。
在您的VBA脚本的开头，您需要通过定义您的VBA数组来定义这个“盒子”。因此，要创建一个可以保存一组数据的数组（一维数组），您可以编写以下行。
Dim arrMyArray(1 To 6) As String
稍后在您的程序中，您可以通过引用括号内的部分将数据放入此数组的任何部分。
arrMyArray(1) = "Ryan Dube"
您可以使用以下行创建一个二维数组。
Dim arrMyArray(1 To 6,1 to 2) As Integer
第一个数字代表行，第二个数字代表列。所以上面的数组可以包含一个 6 行 2 列的范围。
您可以使用数据加载此数组的任何元素，如下所示。
arrMyArray(1,2) = 3
这会将 3 加载到单元格 B1 中。
数组可以将任何类型的数据作为常规变量保存，例如字符串、布尔值、整数、浮点数等。
括号内的数字也可以是变量。程序员通常使用 For循环(Loop)来计算数组的所有部分，并将电子表格中的数据单元格加载到数组中。您将在本文后面看到如何执行此操作。
如何在 Excel 中编写 VBA 数组
让我们看一个简单的程序，您可能希望将电子表格中的信息加载到多维数组中。
例如，让我们看一个产品销售电子表格，您希望从电子表格中提取销售代表的姓名、商品和总销售额。
请注意，在VBA中，当您引用行或列时，您从左上角从 1 开始计算行和列。所以 rep 列是 3，item 列是 4，总列是 7。(rows and columns)
要在所有 11 行中加载这三列，您需要编写以下脚本。
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
您会注意到，为了跳过标题行，第一个 For look 中的行号需要从 2 而不是 1 开始。这意味着您需要在加载单元格值时为数组行值减去 1使用Cells (i, j).Value进入数组。
在哪里插入VBA 数组脚本(Your VBA Array Script)
要在Excel(Excel)中放置VBA脚本，您需要使用VBA编辑器。您可以通过选择“开发人员(Developer)”菜单并在功能区的“控件(Controls)”部分中选择“查看代码”来访问它。(View Code)
如果您在菜单中没有看到开发人员(Developer)，则需要添加它。为此，请选择文件(File)和选项(Options)以打开 Excel 选项窗口。
将选择命令从下拉菜单更改为所有命令(All Commands)。从左侧菜单中选择Developer ，然后选择(Developer)Add按钮将其移动到右侧的窗格中。选中复选框以启用它，然后选择OK完成。
当代码编辑器(Code Editor)窗口打开时，确保在左侧窗格中选择了您的数据所在的工作表。在左侧下拉列表中选择工作表并在右侧选择(Worksheet)激活(Activate)。这将创建一个名为Worksheet_Activate () 的新子例程。
只要打开电子表格文件，此函数就会运行。将代码粘贴到此子例程内的脚本窗格中。
该脚本将处理 12 行，并从第 3 列加载代表名称、从第 4 列加载项目以及从第 7 列加载总销售额。
完成两个 For 循环后，二维数组 arrMyArray 将包含您从原始工作表中指定的所有数据。
在 Excel VBA 中操作数组
假设您要对所有最终销售价格征收 5% 的销售税，然后将所有数据写入新工作表。
您可以通过在第一个循环之后添加另一个 For 循环来执行此操作，并使用命令将结果写入新工作表。
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
这会将整个数组“卸载”到Sheet2中，另外一行包含税额乘以 5% 的总数。
生成的工作表将如下所示。
如您所见，Excel中的(Excel)VBA数组非常有用，并且与任何其他 Excel 技巧(any other Excel trick)一样通用。
上面的例子是一个非常简单的数组使用。您可以创建更大的数组并对存储在其中的数据执行排序、平均或许多其他功能。
如果您想获得真正的创意，您甚至可以创建两个数组，其中包含来自两个不同工作表的一系列单元格(containing a range of cells)，并在每个数组的元素之间执行计算。
应用程序仅受您的想象力限制。
What Is a VBA Array in Excel and How to Program One
VBA has been рart of the Microsoft Office sυite for many уeаrs. While it doesn’t havе the full functіonality and power of a full VB applicatiоn, VBA provides Office users with the flexibilіty to іntegrate Office products and automate the work you do іn 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.
