vba数组操作方法大全图解分享vba编程基础知识

在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