VBA二维数组的基础介绍

2021-12-08 14:30 更新

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

静态和动态数组

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

App下载