在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()

Advertisements

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

Advertisements

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－