浅谈VBA中使用数组处理工作表数据

前言

在VBA中最经常涉及的就是读写工作表中的单元格，如果读写操作次数较多，或者涉及到的单元格数量较多，往往会导致程序运行效率较低。这时经常用到的解决方法就是使用数组保存数据，在内存中进行数据处理和加工，最后一次行更新工作表，这样的操作方式往往会极大提升程序的运行效率。

---

读取数据

声明变量

首先如何声明用于保存单元格内容的变量呢？虽然在VBA可以直接使用未声明的变量，但是声明变量一个良好的编程习惯。需要注意的是在加载单元格内容到数组的代码中，变量必须声明为Variant类型；如果将变量声明为数组，那么将产生类型不匹配的错误。

语法格式

 变量 = 工作表区域.Value

数据区域

连续规则区域

最简单的情况是工作表中数据区域是一个连续区域，那么可以直接使用CurrentRegion属性。

 aData = Activesheet.[A1].CurrentRegion.Value

非规则区域

很多时候，数据区域并不一定是完美的连续区域，此时就需要先确定最后数据行的位置，在VBA中有很多种定位最后数据单元格的方法，本文不再详细讨论。这里只使用其中的一种，即End属性。假设第一列为非空数据列，那么就要以此列做为判断的基准，最后数据单元格的行号保存在lstRow变量中。
假设数据区域有5列，那么可以用最常见的 Range("A1:E" & lstRow) 表示方法，其实更简单的方式是使用Resize。

With ActiveSheet
    lstRow = .Cells(Rows.Count, 1).End(xlUp).Row
    aData = .Range("A1:E" & lstRow).Value
    aData = .[a1].Resize(lstRow, 5).Value
End With

数组维度

显而易见，多行多列的数据区域读入数组，将形成一个二维数组，例如：aDataRect为 8 x 5的二维数组。很容易想到，对于单行或者单列单元格区域，将产生一维数组。实际上VBA处理逻辑不是这样的，aDataCol是一个 5 x 1的二维数组，aDataRow是一个 1 x 5的二维数组。

简单总结一句话，多个单元格区域加载至数组时，产生的数组肯定是 二维数组。

 aDataRect = ActiveSheet.Range("A1:E8").Value    
 aDataCol = ActiveSheet.Range("A1:A5").Value
 aDataRow = ActiveSheet.Range("A1:E1").Value

---

回写数据

确定结果数组的尺寸

如果代码只是处理数组中数据，全部数据都需要回写到单元格中，那么只需要将加载数据到数组的代码行中等号左右的元素互换就可以了。

ActiveSheet.Range("A1:E8").Value = aDataRect

很多情况下，结果数组需要写入不同的单元格位置，那么就需要用UBound函数来获取结果数组的维度上限。UBound(aRes, 1)获取数组的第一维的上限，也就是结果数据集的行数，与此类似，UBound(aRes, 2) 获取数据集的列数。

ActiveSheet.[K2].Resize(UBound(aRes, 1), UBound(aRes, 2)).Value = aRes

单行或者单列

只要确保接收数据的单元格与读取时是同样规格，并且单元格数量与数组元素数量相同，直接赋值就可以了。

 ActiveSheet.Range("B11:B15").Value = aDataCol
 ActiveSheet.Range("A6:E6").Value = aDataRow

一维数组写入单元格

虽然单行或者单列单元格区域读入数组时，产生的是一个二维数组，但是一维数组却是可以回写到单元格中的，貌似似乎有些不合乎逻辑。
aMyData是包含5个元素的一维数组，可以理解为在一行中横向排列的5个元素aMyData(0), aMyData(1), ... aMyData(5)。这个数组是可以直接写入单行单元格区域A6:E6中的；如果想写入单列的单元格区域，就需要借助工作表函数Transpose完成行列转置。请注意Transpose函数的限制。

 aMyData = Array(6, 7, 8, 9, 0)
 ActiveSheet.Range("A6:E6").Value = aMyData
 ActiveSheet.Range("B11:B15").Value = Application.Transpose(aMyData)

---

看完之后，是不是觉得——使用数组处理单元格数据其实很简单！