在VBA中，数组是一组有序的变量的集合，相对于单个变量来说，可以更方便地组织和使用数据。

1 初始化数组

1.1 使用循环语句初始化数组

Sub 使用循环初始化数组()

Dim a(1 To 10) As Integer

For i = 1 To 10

a(i) = 0

Next i

End Sub

1.2 使用Array函数初始化数组

Sub 使用Array函数初始化数组()

Dim a As Variant, b As Variant

a = Array(1, 3, 5, 7, 9)

b = Array("A", "B", "C", "D")

End Sub

1.3 使用数组值初始化数组

Sub 用已有数组初始化数组()

Dim a(5), b()

For i = 0 To 5

a(i) = i

Next

b = a

End Sub

2 使用动态数组

Dim reData() As Integer

Sub 动态数组()

Dim i As Integer, j As Integer

i = Val(InputBox("请输入数组的上界", "定义动态数组", 5))

ReDim reData(i)

For j = 1 To i

reData(j) = InputBox("请输入数组的第" & j & "个元素的值")

Next

For j = 1 To i

Debug.Print reData(j)

Next

End Sub

3 清除数组

Sub 清除数组()

Dim aData(10) As Integer, str1 As String

str1 = "原数组中的数据：" & vbNewLine

For i = 0 To 10

aData(i) = i

str1 = str1 & "aData(" & i & ")=" & aData(i) & " "

Next

Erase aData '删除原数组

str1 = str1 & vbNewLine & "使用Erase命令清除数组aData：" & vbNewLine

For i = 0 To 10

str1 = str1 & "aData(" & i & ")=" & aData(i) & " "

Next

MsgBox str1

End Sub

4 查询数组的下标范围

Sub 获取数组下界范围()

Dim aData(-100 To 100, 5 To 15, -3 To 4)

Dim str1 As String

str1 = "数组各维的下界为：" & vbNewLine

str1 = str1 & "第1维：" & LBound(aData, 1) & vbNewLine

str1 = str1 & "第2维：" & LBound(aData, 2) & vbNewLine

str1 = str1 & "第3维：" & LBound(aData, 3) & vbNewLine

str1 = str1 & vbNewLine & "数组各维的上界为：" & vbNewLine

str1 = str1 & "第1维：" & UBound(aData, 1) & vbNewLine

str1 = str1 & "第2维：" & UBound(aData, 2) & vbNewLine

str1 = str1 & "第3维：" & UBound(aData, 3) & vbNewLine

MsgBox str1

End Sub

5 数组元素排序

Option Base 1

Sub 数据排序()

Dim i As Integer, j As Integer

Dim k

Dim s(10) As Integer

For i = 1 To 10

s(i) = Application.InputBox("输入第" & i & "个数据：", "输入数组", , , , , , 1)

Next

For i = 1 To 9

For j = i + 1 To 10

If s(i) < s(j) Then

t = s(i)

s(i) = s(j)

s(j) = t

End If

Next

Next

For Each k In s

Debug.Print k

Next

End Sub

6 用数组生成彩票号码

Option Base 1

Sub 幸运号码()

Dim n As Integer, i As Integer, j As Integer

Dim l() As Integer

n = Application.InputBox("请输入需要产生幸运号码的数量：", "幸运号码", , , , , , 2)

ReDim l(n, 7) As Integer

For i = 1 To n

For j = 1 To 7

Randomize

l(i, j) = Int(10 * Rnd)

Next

Next

For i = 1 To n

For j = 1 To 7

Debug.Print l(i, j);

Next

Debug.Print

Next

End Sub

7 用数组填充单元格区域

Option Base 1

Sub 数组填充单元格区域()

Dim i As Long, j As Long

Dim col As Long, row As Long

Dim arr() As Long

row = Application.InputBox(prompt:="输入行数：", Type:=2)

col = Application.InputBox(prompt:="输入列数：", Type:=2)

ReDim arr(row, col)

For i = 1 To row

For j = 1 To col

arr(i, j) = (i - 1) * col + j

Next

Next

Set Rng = Sheets(3).Range(Cells(1, 1), Cells(row, col))

Rng.Value = arr

End Sub

－End－