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

avoids the error "Subscript out of range"

   <source lang="vb">

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

</source>
   
  

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

   <source lang="vb">

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

</source>
   
  

Use IsArray function to check if a variable is an array

   <source lang="vb">

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

</source>
   
  

Using the Array Function

   <source lang="vb">

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

</source>