VBA數(shù)組中的常見錯(cuò)誤

圖7-4 該錯(cuò)誤出現(xiàn)于試圖訪問并不存在的數(shù)組成員

假設(shè)你聲明了一個(gè)包含6個(gè)成員的一維數(shù)組，而你卻試圖給第八個(gè)成員賦值，當(dāng)你運(yùn)行該過程時(shí)，VB無法找到第八個(gè)成員，所以顯示錯(cuò)誤信息。點(diǎn)擊調(diào)試按鈕，VB將導(dǎo)致錯(cuò)誤的代碼行（見圖7-5）加亮。檢查數(shù)組的聲明語句，并且更改被加亮代碼行括號(hào)里的索引號(hào)。“下標(biāo)越界”錯(cuò)誤經(jīng)常是由使用循環(huán)的過程引發(fā)的。下面的過程Zoo1就是這種情況的一個(gè)例子。在用戶取消在輸入框里輸入數(shù)據(jù)之前，循環(huán)里的語句反復(fù)被執(zhí)行。在執(zhí)行該過程時(shí)，當(dāng)變量 i 等于4的時(shí)候，VB無法在這個(gè)只有三個(gè)成員的數(shù)組里找到第四個(gè)成員，那么錯(cuò)誤信息就出現(xiàn)了。修改后的過程Zoo2示范了前面章節(jié)里介紹的LBound和UBound函數(shù)如何能夠避免試圖訪問不存在的數(shù)組成員的錯(cuò)誤。

圖7-5 當(dāng)你點(diǎn)擊錯(cuò)誤信息的調(diào)試按鈕，VB就會(huì)加亮引發(fā)錯(cuò)誤的語句

1.在當(dāng)前工程里插入新模塊，命名為Errors_In_Arrays
2.輸入下列過程Zoo1和Zoo2：

Sub Zoo1()
'this procedure triggers an error "Subscript out of range" 本過程引發(fā)“下標(biāo)越界”錯(cuò)
誤
Dim zoo(3) As String
Dim i As Integer
Dim response As String
i = 0
Do
i = i +1
response = InputBox("Enter a name of animal:")
zoo(i) = response
Loop until response = ""
End Sub
Sub Zoo2()
'this procedure avoids the error "Subscript out of range"本過程避免“下標(biāo)越界”錯(cuò)誤
Dim zoo(3) As String
Dim i As Integer
Dim response As String
i = 1
Do While i>=LBound(zoo) And i <=UBound(zoo)
response = InputBox("Enter a name of animal:")
If response = "" Then Exit Sub
zoo(i) = response
i = i + 1
Loop
For i = LBound(zoo) To UBound(zoo)
MsgBox zoo(i)
Next
End Sub

數(shù)組作為參數(shù)

Function AddMultipleArgs(ParamArray myNumbers() As Variant)
Dim mySum As Single
Dim myValue As Variant
For each myValue in myNumbers
mySum=mySum+myValue
Next
AddMultipleArgs = mySum
End Function

?AddMultipleArgs(1, 23.24, 3, 24, 8, 34)