VBA的5个内置数组函数

Option Base 1
Sub CarInfo()
Dim auto As Variant
auto = Array("Ford", "Black", "1999")
MsgBox auto(2) & " " & auto(1) & ", " & auto(3)
auto(2) = "4-door"
MsgBox auto(2) & " " & auto(1) & ", " & auto(3)
End Sub

另外一个例子，示范如何使用Array函数将列标输入到工作表里:

Sub ColumnHeads()Dim heading As VariantDim cell As RangeDim i As Integeri = 1heading = Array("First Name", "Last Name", "Position", _"Salary")Workbooks.AddFor Each cell in Range("A1:D1")cell.Formula = heading(i)i = i+1NextColumns("A:D").SelectSelection.Columns.AutoFitRange("A1").SelectEnd Sub

Sub IsThisArray()declare a dynamic array 声明一动态数组Dim sheetNames() As StringDim totalSheets As IntegerDim counter As Integercount the sheets in the current workbook 计数当前工作簿里的工作表数目totalSheets = ActiveWorkbook.Sheets.Countspecify the size of the array 明确数组大小ReDim sheetNames(1 To totalSheets)enter and show the names of sheets 输入和显示工作表名称For counter = 1 to totalSheetssheetNames(counter) = ActiveWorkbook.Sheets(counter).NameMsgBox sheetNames(counter)Next countercheck if this is indeed an array 检查它是否确实为数组If IsArray(sheetNames) ThenMsgBox "The sheetNames is an array."End IfEnd Sub

Erase函数

 start indexing array elements at 1
Option Base 1
Sub FunCities()
declare the array
Dim cities(1 to 5) As String
assign the values to array elements
cities(1) = "Las Vegas"
cities(2) = "Orlando"
cities(3) = "Atlantic City"
cities(4) = "New York"
cities(5) = "San Francisco"
display the list of cities
MsgBox cities(1) & Chr(13) & cities(2) & Chr(13) _
& cities(3) & Chr(13) & cities(4) & Chr(13) _
& cities (5)
Erase cities
show all that was erased
MsgBox cities(1) & Chr(13) & cities(2) & Chr(13) _
& cities(3) & Chr(13) & cities(4) & Chr(13) _
& cities (5)
End Sub

在函数Erase清除数组里的数据后，函数MsgBox就显示一个空信息框了。

Sub FunCities2()
declare the array
Dim cities(1 to 5) As String
assign the values to array elements
cities(1) = "Las Vegas"
cities(2) = "Orlando"
cities(3) = "Atlantic City"
cities(4) = "New York"
cities(5) = "San Francisco"
display the list of cities
MsgBox cities(1) & Chr(13) & cities(2) & Chr(13) _
& cities(3) & Chr(13) & cities(4) & Chr(13) _
& cities (5)
display the array bounds
MsgBox "The lower bound: " & LBound(cities) & Chr(13) _
& "The upper bound: " & UBound(cities)
End Sub

当你要确定一个二维数组的上下界时，你就必须明确维数：1表示第一维，2表示第二维。在本章早先时候将的Exchange过程里的后面加上如下语句，可以确定该二维数组的上下界（将下列代码加入到关键字End Sub之前）：

MsgBox "The lower bound (first dimension) is " _& LBound(Ex, 1) & "."MsgBox " The upper bound(first dimension) is " _& UBound(Ex, 1) & "."MsgBox "The lower bound (second dimension) is " _& LBound(Ex, 2) & "."MsgBox " The upper bound(second dimension) is " _& UBound(Ex, 2) & "."