Errors in Arrays

When working with arrays, it's easy to make a mistake. If you try to assign more values than there are elements in the declared array, Visual Basic will display the error message "Subscript out of range" (see Figure 7-3 below).

Figure 7-3: This error was caused by an attempt to access a nonexistent array element.

Microsoft Visual Basic

Run-time error '9':

Subscript out of range


| Debug |


Suppose you declared a one-dimensional array that consists of six elements and you are trying to assign a value to the eighth element. When you run the procedure, Visual Basic can't find the eighth element, so it displays the above error message. If you click the Debug button, Visual Basic will highlight the line of code that caused the error (see Figure 7-4). Look at the array's declaration statement and change the index number that appears in the parentheses in the highlighted line of code.

Figure 7-4: When you click the Debug button in the error message, Visual Basic highlights the statement that triggered the error.

Acc2003_Chap07 Module11 (Code)




1 ^J 12001


Option Cornipere Datahase



■this procedure triggers an error "Subscript our of


Dim EOO(3) i.3 String

D urn 1 As Integer

1 US response As String

i = 0


i = i + 1

response - InputBOK ("Enter a name of :vi:: ",


soo(i) = response

Loop until response »


£ I:




The error "Subscript out of range" is often triggered in procedures using loops. The procedure Zool shown in Hands-On 7-11 serves as an example of such a situation. The statements in the loop are to be executed until the user cancels out from the input box. While executing this procedure, when the variable i equals 4, Visual Basic will not be able to find the fourth element in a three-element array, so the error message will appear. The modified procedure Zoo2 demonstrates how, by using the LBound and UBound functions introduced in the preceding section, you can avoid errors caused by an attempt to access a nonexistent array element.

Introduction to Access 2003 VBA Programming

© Hands-On 7-11: Understanding Errors in Arrays

1. In a new module, enter the following procedures, Zoo1 and Zoo2, as shown below.

Sub Zoo1()

' this procedure triggers an error "Subscript out of range"

Dim zoo(3) As String

Dim i As Integer

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"

Dim zoo(3) As String

Dim i As Integer

Dim response As String

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


For i = LBound(zoo) To UBound(zoo) MsgBox zoo(i)

Next End Sub

Another frequent error you may encounter while working with arrays is a type mismatch error. To avoid this error, keep in mind that each element of an array must be of the same data type. Therefore, if you attempt to assign to an element of an array a value that conflicts with the data type of the array, you will get a type mismatch error during the code execution. If you need to hold values of different data types in an array, declare the array as Variant.

0 0

Post a comment