codecamp

VBA的5个内置数组函数

Array函数

Array函数允许你在代码执行中间创建一个数组,而不必事先确定其大小。该函数总是返回一个Varant数组。使用函数Array你可以快速地将一系列数据放置在一个清单里面。下面的过程CarInfo创建了一个叫做auto的固定大小,一维的三个成员的数组。
1.  在当前工程里插入一新模块,重命名为Array_Function
2.  输入下列过程CarInfo:
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 Variant
Dim cell As Range
Dim i As Integer
i = 1
heading = Array("First Name", "Last Name", "Position", _
"Salary")
Workbooks.Add
For Each cell in Range("A1:D1")
cell.Formula = heading(i)
i = i+1
Next
Columns("A:D").Select
Selection.Columns.AutoFit
Range("A1").Select
End Sub

IsArray函数

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

Erase函数

当你要清除数组里的数据时,应该使用Erase函数。该函数删除静态或动态数组储存的所有数据,另外,对于动态数组,Erase函数将重新分配原来分配给该数组的所有内存。下面的例子教你如何删除数组cities里的数据。

1.  在当前工程里插入一新模块,重命名为Erase_Function
2.  输入如下过程FunCities:
' 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就显示一个空信息框了。

LBound函数和UBound函数

LBound函数和UBound函数分别返回表明数组的下界和上界的数字。

1.  在当前工程里插入模块,命名为L_and_UBound_Function
2.  输入如下代码FunCities2:
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) & "."


VBA二维数组的基础介绍
VBA数组中的常见错误
温馨提示
下载编程狮App,免费阅读超1000+编程语言教程
取消
确定
目录

关闭

MIP.setData({ 'pageTheme' : getCookie('pageTheme') || {'day':true, 'night':false}, 'pageFontSize' : getCookie('pageFontSize') || 20 }); MIP.watch('pageTheme', function(newValue){ setCookie('pageTheme', JSON.stringify(newValue)) }); MIP.watch('pageFontSize', function(newValue){ setCookie('pageFontSize', newValue) }); function setCookie(name, value){ var days = 1; var exp = new Date(); exp.setTime(exp.getTime() + days*24*60*60*1000); document.cookie = name + '=' + value + ';expires=' + exp.toUTCString(); } function getCookie(name){ var reg = new RegExp('(^| )' + name + '=([^;]*)(;|$)'); return document.cookie.match(reg) ? JSON.parse(document.cookie.match(reg)[2]) : null; }