VBA/Excel/Access/Word/Data Type/Array Function

avoids the error "Subscript out of range"

 
Sub Zoo2()
    
    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

The LBound and UBound functions return whole numbers that indicate the lower bound and upper bound indices of an array.

 
Sub FunCities2()
    Dim cities(1 To 5) As String
    cities(1) = "Las Vegas"
    cities(2) = "Orlando"
    cities(3) = "Atlantic City"
    cities(4) = "New York"
    cities(5) = "San Francisco"
    MsgBox cities(1) & Chr(13) & cities(2) & Chr(13) _
        & cities(3) & Chr(13) & cities(4) & Chr(13) _
        & cities(5)
    MsgBox "The lower bound: " & LBound(cities) & Chr(13) _
        & "The upper bound: " & UBound(cities)
End Sub

Use IsArray function to check if a variable is an array

 
Sub arrayTest5()
   Dim intScores1 As Integer
   Dim intScores2(4) As Integer
   Debug.Print "Is intScores1 an array: " & IsArray(intScores1)
   Debug.Print "Is intScores2 an array: " & IsArray(intScores2)
End Sub

Using the Array Function

 
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