在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
本文内容来自网友供稿，文章观点仅代表作者本人，本站非盈利且无偿提供信息存储空间服务，不拥有所有权，如有文章有不实信息或侵犯了您的权益，请发送邮件至 cfseo1997@163.com 反馈核实，如需转载请注明出处：https://www.taobobolive.com/48350.html
