Excel VBA解读（154）：数据结构——数组常用操作示例代码

学习Excel技术，关注微信公众号：
excelperfect

本文主要给出使用数组常用的一些代码，供有需要时参考。

遍历数组
下面的代码从数组第一个元素开始遍历所有数组元素：
For i = LBound(arr) To UBound(arr)
Debug.Print arr(i)
Next i
或者：
For i = LBound(arr, 1) To UBound(arr, 1)
Debug.Print arr(i)
Nexti

上面的代码遍历一维数组，下面的代码遍历二维数组：
For i = LBound(arr, 1) To UBound(arr, 1)
For j = LBound(arr, 2) To UBound(arr, 2)
      Debug.Print arr(i, j)
Next j
Next i

如果将计数变量声明为variant型，那么还可以使用For Each循环遍历数组：
Dim item As Variant
Dim arr(6) As Long
Dim i As Long

For i = 0 To 6
arr(i) = i
Next i

For Each item In arr
Debug.Print item
Next item

如果使用工作表单元格区域中的数据快速填充数组，那么也可以使用For Each循环遍历数组元素：
Dim arr As Variant
Dim item As Variant

arr= Worksheets("Sheet1").Range("A1:C5")

For Each item In arr
Debug.Print item
Next item

传递数组
下面的代码将主过程中的数组传递给被调用过程：
Sub test11()
Dim myArr(5) As Long
Dim i As Long

For i = 0 To 5
      myArr(i) = i
Next i

MyArray myArr()
End Sub

Sub MyArray(ByRef arr() As Long)
Dim i As Long

For i = LBound(arr) To UBound(arr)
      Debug.Print arr(i)
Next i
End Sub

下面的代码从函数过程中返回数组：
Sub test31()
Dim myArray() As Long
Dim i As Long

myArray = GetArray

For i = LBound(myArray) To UBound(myArray)
      Debug.Print myArray(i)
Next i
End Sub

Function GetArray() As Long()
Dim arr(5) As Long
Dim i As Long

For i = 0 To 5
      arr(i) = i
Next i

GetArray = arr
End Function

获取数组中元素的数量
下面的自定义函数可以返回传递给它的任何维数的数组的元素数：
'返回数组元素的数量
Function ArrayElemNum(arr As Variant) As Long
On Error GoTo E
Dim i As Long
Do While True
      i = i + 1
      ArrayElemNum = IIf(ArrayElemNum = 0, 1,ArrayElemNum) _
            * (UBound(arr, i) - LBound(arr,i) + 1)
Loop
Exit Function
E:
If Err.Number = 13 Then
      Err.Raise vbObjectError,"ArrayElemNum", _
         "传递给ArrayElemNum函数的参数不是数组."
End If
End Function

测试ArrayElemNum函数的代码及结果如下：
Sub testArrayElemNum()
Dim arr1() As Long
'返回0
Debug.Print ArrayElemNum(arr1)

Dim arr2(5) As Long
'返回6
Debug.Print ArrayElemNum(arr2)

Dim arr3(5, 2) As Long
'返回18
Debug.Print ArrayElemNum(arr3)
End Sub

排序数组元素
下面的代码使用快速排序算法对数组元素排序：
Sub QuickSort(arr As Variant, first As Long, last As Long)
Dim vCentreVal As Variant
Dim vTemp As Variant
Dim lTempLow As Long
Dim lTempHi As Long

lTempLow = first
lTempHi = last

vCentreVal = arr((first + last) \ 2)

Do While lTempLow  first
         lTempHi = lTempHi - 1
      Loop

      If lTempLow

Excel VBA解读（154）： 数据结构——数组常用操作示例代码

Excel VBA解读（154）：数据结构——数组常用操作示例代码