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 <= lTempHi

Do While arr(lTempLow) < vCentreValAnd lTempLow < last

lTempLow = lTempLow + 1

Loop

Do While vCentreVal < arr(lTempHi)And lTempHi > first

lTempHi = lTempHi - 1

Loop

If lTempLow <= lTempHi Then

'交换数值

vTemp = arr(lTempLow)

arr(lTempLow) = arr(lTempHi)

arr(lTempHi) = vTemp

'移到下一位置

lTempLow = lTempLow + 1

lTempHi = lTempHi - 1

End If

Loop

If first < lTempHi Then QuickSort arr,first, lTempHi

If lTempLow < last Then QuickSort arr,lTempLow, last

End Sub

下面的代码测试快速排序代码：

Sub testQuickSort()

Dim arr(5) As Long

arr(0) = 5: arr(1) = 3

arr(2) = 1: arr(3) = 2

arr(4) = 6: arr(5) = 4

QuickSort arr, LBound(arr), UBound(arr)

Dim i As Long

For i = LBound(arr) To UBound(arr)

Debug.Print arr(i)

Next i

End Sub