VBA二維數(shù)組的基礎(chǔ)介紹

既然你已經(jīng)知道了如何有規(guī)劃地產(chǎn)生一個清單（一維數(shù)組），是時候仔細(xì)看一下如何使用數(shù)據(jù)表了。下面的過程產(chǎn)生一個二維數(shù)組，儲存國家名稱，貨幣名稱和交換匯率。

Sub Exchange()
Dim t As String
Dim r As String
Dim Ex(3, 3) As Variant
t = Chr(9) 'tab
r = Chr(13) 'Enter
Ex(1, 1) = "Japan"
Ex(1, 2) = "Yen"
Ex(1, 3) = 128.2
Ex(2, 1) = "Mexico"
Ex(2, 2) = "Peso"
Ex(2, 3) = 9.423
Ex(3, 1) = "Canada"
Ex(3, 2) = "Dollar"
Ex(3, 3) = 1.567
MsgBox "Country " & t & t & "Currency" & t & "per US$" _
& r & r _
& Ex(1, 1) & t & t & Ex(1, 2) & t & Ex(1, 3) & r _
& Ex(2, 1) & t & t & Ex(2, 2) & t & Ex(2, 3) & r _
& Ex(3, 1) & t & t & Ex(3, 2) & t & Ex(3, 3), , _
"Exchange"
End Sub

當(dāng)你運行過程Exchange時，你將看到一個信息框，顯示三列信息（見圖7-2）

圖7-2 顯示在信息框上的文本是可以自定義格式的。

數(shù)組的維數(shù)：

Sub 數(shù)組示例()：

Dim?x?As?Long,?y?As?Long
Dim?arr(1?To?10,?1?To?3)?'創(chuàng)建一個可以容下10行3列的數(shù)組空間
For?x?=?1?To?4
    For?y?=?1?To?3
        arr(x,?y)?=?Cells(x,?y)?'通過循環(huán)把單元格區(qū)域a1:c4的數(shù)據(jù)裝進數(shù)組中
    Next?y
Next?x
MsgBox?arr(4,?3)?'根據(jù)提供的行數(shù)和列數(shù)顯示數(shù)組
arr(1,?2)?=?"我改一下試試"'你可以隨時修改數(shù)組內(nèi)指定位置的數(shù)據(jù)
MsgBox?arr(1,?2)
End?Sub

總結(jié)：二維是由行和列表示的數(shù)組，如 ARR(3,2)表示數(shù)組中第3排第2列的元素。而一維數(shù)組只是由一個元素決定，如ARR（4）表示數(shù)組中第4個元素

靜態(tài)和動態(tài)數(shù)組

Sub DynArray( )
Dim counter As Integer
'declare a dynamic array
Dim myArray( ) As Integer
'specify the initial size of the array
Redim myArray(5)
Workbooks.Add
'populate myArray with values
For counter = 1 to 5
myArray(counter) = counter +1
ActiveCell.Offset(counter-1, 0).Value = myArray(counter)
Next
'change the size of myArray to hold 10 elements
Redim Preserve myArray(10)
'add new values to myArray
For counter = 6 To 10
myArray(counter) = counter * counter
With ActiveCell.Offset(counter-1, 0)
.Value = myArray(counter)
.Font.Bold = TrueEnd with
Next counter
End Sub

myArray(counter) = counter + 1

ActiveCell.Offset(counter-1, 0).Value = myArray(counter)

ActiveCell.Offset(1-1, 0).Value = myArray(1)

ActiveCell.Offset(0,0).Value = myArray(1)

ReDim Preserve myArray(10)