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, VBA will display the error message "Subscript out of range."

Microsoft Visual

lasic

Run-time error '9': Subscript out of range

¿il 1

End 1 1

[ Debug j|

Help |

This error was caused by an attempt to access a nonexistent array element.

Suppose you declare 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 error message. Click the Debug button. Visual Basic will highlight the line of code that caused the error (Figure 7-5). Look at the array's declaration statement and change the index number that appears in the parentheses in the highlighted line of code.

The error "Subscript out of range" is often triggered in procedures using loops. The procedure Zool shown below 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, Visual Basic will not be able to find the fourth element in a three-element array when the variable i equals 4, so the error message will appear. The modified procedure Zoo2 demonstrates how using the LBound and UBound functions introduced in the preceding section can prevent errors caused by an attempt to access a nonexistent array element.

1 Chap07.xls

StaticArrays [Code]

_{n|ix|

J (General)

▼ 1 J Favor iteCities2

m

Sub FavoriteCities2()

'now declare the array Dim cities(Ei) As String Dim city As Variant m

Sub FavoriteCities2()

'now declare the array Dim cities(Ei) As String Dim city As Variant

'assign the values to array elements citles(f) M "Los Angeles' c:tiesi2).-= "Atlanta" c:ties(3) = "Boston" c:ties(4) = "Washington" cities© = "New' York" I citiesP) = "Trenton"

'display the list of cities in separate messages For Each city In cities tiSiLJ

Figure 7-5:

When you click the Debug button in the error message, Visual Basic highlights the statement that triggered the error

1. Insert a new module into the current project and rename it Errors_In_Arrays.

2. Enter the following procedures: Zool and Zoo2: 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 i = 1

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

Another frequent error you may encounter while working with arrays is a type mismatch. To avoid this error, keep in mind that each element of an array must be of the same data type. If you attempt to assign to an element of an array a value that conflicts with the data type of the array declared by the Dim statement, you'll receive the "Type mismatch" error during code execution. To hold values of different data types in an array, declare the array as Variant.

0 0

Post a comment