• 微信公众号：美女很有趣。 工作之余，放松一下，关注即送10G+美女照片！

# 数组学习系列1-VBA二维数组的基础介绍（4）

3小时前 1次浏览

``````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, 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
``````

#### Sub 数组示例()：

``````Dim x As Long, y As Long
Dim arr(1 To 10, 1 To 3) '创建一个可以容下10行3列的数组空间
For x = 1 To 4
For y = 1 To 3
arr(x, y) = Cells(x, y) '通过循环把单元格区域a1:c4的数据装进数组中
Next y
Next x
MsgBox arr(4, 3) '根据提供的行数和列数显示数组
arr(1, 2) = "我改一下试试"'你可以随时修改数组内指定位置的数据
MsgBox arr(1, 2)
End Sub``````

## 静态和动态数组

1.  在当前工程里插入一个新模块并且重新命名为 DynamicArrays
2.  输入下列过程 DynArray：
``````Sub DynArray( )
Dim counter As Integer
'declare a dynamic array
Dim myArray( ) As Integer
'specify the initial size of the array
Redim myArray(5)
'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)
For counter = 6 To 10
myArray(counter) = counter * counter
With ActiveCell.Offset(counter-1, 0)
.Value = myArray(counter)
.Font.Bold = True``````End with
Next counter
````End Sub````
3.  将你的 Excel 窗口和 VB 编辑器窗口并排显示
4.  逐步运行过程 DynArray。你可以将鼠标置于代码中间，并且按下F8来执行逐条语句。程序
DynArray 的结果如下图所示。

``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)``