VBA进阶 | 数组基础04: 运用数组处理工作表数据

 

前面我们已经讲过，工作表就是一个二维数组，指定其水平维度（行）和垂直维度（列）就可以找到单元格，例如Cells(2,3)就是单元格C2。而在二维数组中，使用其每一维的下标索引值即可从中获取元素，例如myArray(1,2)就是数组myArray的第2行第3列的元素。

 

在Excel中，可以将使用数组来存储单元格区域数据，并进行处理，然后再将数据输入到工作表，这比只是在工作表中处理数据更高效。

 

使用工作表数据填充数组

如下图1所示的工作表。

 图1

使用下面的语句快速填充数组：

Dim myArray AsVariant

myArray =Worksheets('Sheet1').Range('B2:C8')

 

注意到，在声明数组变量时，并没有指定维数，也没有指定具体的数据类型，而是指定为Variant型。因此，也可以使用下面的声明语句：

Dim myArray

 

下面的过程代码测试刚才创建的数组的维数及其上限和下限：

Sub testArray()

    Dim myArray As Variant, vUBound As Variant

    Dim i As Integer, str As String

   

    myArray =Worksheets('Sheet1').Range('B2:C8')

    i = 1

   

    Do

        str = str & '第 ' & i& ' 维的下限 = ' & LBound(myArray, i) & _

              ' 上限 = ' &UBound(myArray, i) & vbCr & vbCr

        i = i 1

        On Error Resume Next

        vUBound = UBound(myArray, i)

        If Err.Number <> 0 Then

            str = str & '数组myArray包含的维数是: ' & i - 1

            Exit Do

        End If

        On Error GoTo 0

    Loop

   

    MsgBox str

End Sub

运行代码后的结果如下图2所示。

 图2

可以看出，与普通数组不同，放置单元格数据的数组的下限索引值是1。

 

将数组中的数据输入到工作表

下面的代码对图1工作表中每位学生的成绩求和并将结果输入到列D：

Sub testArray1()

    Dim myArray

    Dim iCount As Integer

   

    myArray =Worksheets('Sheet1').Range('B2:C8')

   

    For iCount = LBound(myArray) ToUBound(myArray)

       Worksheets('Sheet1').Range('D' & iCount 1) _

              = myArray(iCount, 1) myArray(iCount, 2)

    Next iCount

End Sub

运行代码后的结果如图3所示。

 图3

 

下面的过程将数组myArray中的数据输入到工作表单元格区域A1:C3中：

Sub testArray2()

    Dim myArray(1, 2)

   

    myArray(0, 0) = '姓名'

    myArray(0, 1) = '性别'

    myArray(0, 2) = '成绩'

    myArray(1, 0) = '张三'

    myArray(1, 1) = '男'

    myArray(1, 2) = '95'

   

    Worksheets('Sheet2').Cells(1, 1)._

      Resize(UBound(myArray, 1) 1,UBound(myArray, 2) 1) = myArray

End Sub

 

在数组中使用工作表函数

仍以上文所示的工作表为例，下面的代码使用工作表函数Average分别求语文和数学的平均成绩。

Sub testArray3()

    Dim myArrayChn, myArrayMath

   

    myArrayChn =Worksheets('Sheet1').Range('B2:B8')

    myArrayMath =Worksheets('Sheet1').Range('C2:C9')

   

    MsgBox '语文平均成绩为: ' &WorksheetFunction.Average(myArrayChn) & _

        vbCr & '数学平均成绩为: ' &WorksheetFunction.Average(myArrayMath)

End Sub

如果要求语文和数学成绩的最高分，可以在语句中使用Max函数：

WorksheetFunction.Max(myArrayChn)

WorksheetFunction.Max(myArrayMath)

 

一般来说，将工作表单元格数据赋值给Variant型变量后，该变量就成了一个二维数组。可以使用TRANSPOSE函数将工作表中的一列转换成一行，从而将二维数组转变成一维数组。

仍以上文所示的工作表为例，代码：

Sub testArray4()

    Dim myArrayChn As Variant

    myArrayChn = WorksheetFunction.Transpose(Range('B2:B8'))

    MsgBox '第5位学生的语文成绩是: ' &myArrayChn(5)

End Sub

运行后的结果如图4所示。

 图4

 

当然，也可以使用Transpose函数将一行中数据转换成一维数组，还可以使用Index函数将工作表中一行数据转换成一维数组，例如：

Dim myArrayTitle AsVariant

myArrayTitle =WorksheetFunction.Index(Range('A1:C1').Value, 1, 0)

---

 

---