
' ---------------------------------------------------------------
' Hands-On 7-1
' ---------------------------------------------------------------

' ensure that there is only one Option Base 1 statement
' at the top of the module

Option Base 1


Sub FavoriteCities()
    ' declare the array
    Dim cities(6) As String
    
    ' assign the values to array elements
    cities(1) = "Baltimore"
    cities(2) = "Atlanta"
    cities(3) = "Boston"
    cities(4) = "Washington"
    cities(5) = "New York"
    cities(6) = "Trenton"
    
    ' display the list of cities
    MsgBox cities(1) & Chr(13) & cities(2) & Chr(13) _
        & cities(3) & Chr(13) & cities(4) & Chr(13) _
        & cities(5) & Chr(13) & cities(6)
End Sub


' ---------------------------------------------------------------
' Hands-On 7-2
' ---------------------------------------------------------------

' Make sure that the Option Base 1 statement appears at the top of the module
    
Sub FavoriteCities2()
    ' declare the array
    Dim cities(6) As String
    Dim city As Variant
    
    ' assign the values to array elements
    cities(1) = "Baltimore"
    cities(2) = "Atlanta"
    cities(3) = "Boston"
    cities(4) = "Washington"
    cities(5) = "New York"
    cities(6) = "Trenton"
    
    ' display the list of cities in separate messages
    For Each city In cities
        MsgBox city
    Next
End Sub


' ---------------------------------------------------------------
' Hands-On 7-3
' ---------------------------------------------------------------

' Make sure that the Option Base 1 statement appears at the top of the module
    
Sub CityOperator()
    ' declare the array
    Dim cities(6) As String
    
    ' assign the values to array elements
    cities(1) = "Baltimore"
    cities(2) = "Atlanta"
    cities(3) = "Boston"
    cities(4) = "Washington"
    cities(5) = "New York"
    cities(6) = "Trenton"
    
    ' call another procedure and pass
    ' the array as argument
    Hello cities()
End Sub

Sub Hello(cities() As String)
    Dim counter As Integer
    
    For counter = 1 To 6
    MsgBox "Hello, " & cities(counter) & "!"
    Next
End Sub

' ---------------------------------------------------------------
' Hands-On 7-4
' ---------------------------------------------------------------


Sub Lotto()
    Const spins = 6
    Const minNum = 1
    Const maxNum = 51
    Dim t As Integer            ' looping variable in outer loop
    Dim i As Integer            ' looping variable in inner loop
    Dim myNumbers As String     ' string to hold all picks
    Dim lucky(spins) As String  ' array to hold generated picks
    
    myNumbers = ""
    For t = 1 To spins
        Randomize
        lucky(t) = Int((maxNum - minNum + 1) * Rnd + minNum)
    
        ' check if this number was picked before
        For i = 1 To (t - 1)
            If lucky(t) = lucky(i) Then
                lucky(t) = Int((maxNum - minNum + 1) * Rnd + minNum)
                i = 0
        End If
        Next i
        MsgBox "Lucky number is " & lucky(t), , "Lucky number " & t
        myNumbers = myNumbers & " -" & lucky(t)
    Next t
    MsgBox "Lucky numbers are " & myNumbers, , "6 Lucky Numbers"
End Sub


' ---------------------------------------------------------------
' Hands-On 7-5
' ---------------------------------------------------------------


Sub Exchange()
    Dim t As String
    Dim r As String
    Dim Ex(3, 3) As Variant
    
    t = Chr(9)  ' Tab
    r = Chr(13) ' Enter
    
    Ex(1, 1) = "Japan"
    Ex(1, 2) = "Japanese Yen"
    Ex(1, 3) = 102.76
    Ex(2, 1) = "Europe"
    Ex(2, 2) = "Euro"
    Ex(2, 3) = 0.744734
    Ex(3, 1) = "Canada"
    Ex(3, 2) = "Canadian Dollar"
    Ex(3, 3) = 1.20892
    
    MsgBox "Country " & t & t & "Currency" & t & t & "Value per US$" _
        & r & r _
        & Ex(1, 1) & t & t & Ex(1, 2) & t & Ex(1, 3) & r _
        & Ex(2, 1) & t & t & Ex(2, 2) & t & t & Ex(2, 3) & r _
        & Ex(3, 1) & t & t & Ex(3, 2) & t & Ex(3, 3), , _
        "Exchange"
End Sub


' ---------------------------------------------------------------
' Hands-On 7-6
' ---------------------------------------------------------------


Sub DynArray()
    Dim counter As Integer
    Dim myArray() As Integer        ' declare a dynamic array
    ReDim myArray(5)                ' specify the initial size of the array
    Dim myValues As String
    
    ' populate myArray with values
    For counter = 1 To 5
        myArray(counter) = counter + 1
        myValues = myValues & myArray(counter) & Chr(13)
    Next
    
    ' change the size of myArray to hold 10 elements
    ReDim Preserve myArray(10)
    
    ' add new values to myArray
    For counter = 6 To 10
        myArray(counter) = counter * counter
        myValues = myValues & myArray(counter) & Chr(13)
    Next counter
    
    MsgBox myValues
    For counter = 1 To 10
        MsgBox myArray(counter)
    Next counter
End Sub


' ---------------------------------------------------------------
' Hands-On 7-7
' ---------------------------------------------------------------

' Make sure that the Option Base 1 statement appears at the top of the module

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


' ---------------------------------------------------------------
' Hands-On 7-8
' ---------------------------------------------------------------


Sub IsThisArray()
    ' declare a dynamic array
    Dim tblNames() As String
    Dim totalTables As Integer
    Dim counter As Integer
    Dim db As Database
    
    Set db = CurrentDb
    
    ' count the tables in the open database
    totalTables = db.TableDefs.Count
    
    ' specify the size of the array
    ReDim tblNames(1 To totalTables)
    
    ' enter and show the names of tables
    For counter = 1 To totalTables - 1
        tblNames(counter) = db.TableDefs(counter).Name
        Debug.Print tblNames(counter)
    Next counter
    
    ' check if this is indeed an array
    If IsArray(tblNames) Then
        MsgBox "The tblNames is an array."
    End If
End Sub


' ---------------------------------------------------------------
' Hands-On 7-9
' ---------------------------------------------------------------

' Make sure that the Option Base 1 statement appears at the top of the module
    
Sub FunCities()
    ' declare the array
    Dim cities(1 To 5) As String
    
    ' assign the values to array elements
    cities(1) = "Las Vegas"
    cities(2) = "Orlando"
    cities(3) = "Atlantic City"
    cities(4) = "New York"
    cities(5) = "San Francisco"
    
    ' display the list of cities
    MsgBox cities(1) & Chr(13) & cities(2) & Chr(13) _
        & cities(3) & Chr(13) & cities(4) & Chr(13) _
        & cities(5)
    
    Erase cities
    
    ' show all that was erased
    MsgBox cities(1) & Chr(13) & cities(2) & Chr(13) _
        & cities(3) & Chr(13) & cities(4) & Chr(13) _
        & cities(5)
End Sub


' ---------------------------------------------------------------
' Hands-On 7-10
' ---------------------------------------------------------------

' Make sure that the Option Base 1 statement appears at the top of the module

Sub FunCities2()
    ' declare the array
    Dim cities(1 To 5) As String
    
    ' assign the values to array elements
    cities(1) = "Las Vegas"
    cities(2) = "Orlando"
    cities(3) = "Atlantic City"
    cities(4) = "New York"
    cities(5) = "San Francisco"
    
    ' display the list of cities
    MsgBox cities(1) & Chr(13) & cities(2) & Chr(13) _
        & cities(3) & Chr(13) & cities(4) & Chr(13) _
        & cities(5)
    
    ' display the array bounds
    MsgBox "The lower bound: " & LBound(cities) & Chr(13) _
        & "The upper bound: " & UBound(cities)
End Sub


' ---------------------------------------------------------------
' Hands-On 7-11
' ---------------------------------------------------------------

Sub Zoo1()
    ' this procedure triggers an error "Subscript out of range"
    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"
    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
	Debug.Print zoo(i)
        i = i + 1
    Loop
    
End Sub


' ---------------------------------------------------------------
' Hands-On 7-12
' ---------------------------------------------------------------

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


