Excel Flash Cards Voice

I was doing a bit of driving so I tried out adding a voice to the flash cards mentioned in a previous post. The computer speakers didn’t work too well so it didn’t work as well as I would like. Here’s the code with the voice, it also is way to verbose.

Option Explicit

Public Voice As New SpVoice


Sub FlashCardSpeak()
    
    Dim oSheet As Worksheet
    Set oSheet = ThisWorkbook.Worksheets.Item("ListOfTerms")
    
    Dim iRow As Integer
    Dim iCount As Integer
    Dim Response
    Dim iRow1, iRow2, iRow3, iRow4, iRow5, iRow6, iRow7, iRow8, iRow9, iRow10 As Integer
    
    iRow = 1
    iRow1 = 1
    iRow2 = 2
    iRow3 = 3
    iRow4 = 4
    iRow5 = 5
    iRow6 = 6
    iRow7 = 7
    iRow8 = 8
    iRow9 = 9
    iRow10 = 10
    
Do Until oSheet.Cells(iRow1, 1) = "" And oSheet.Cells(iRow2, 1) = "" And oSheet.Cells(iRow3, 1) = "" _
    And oSheet.Cells(iRow4, 1) = "" And oSheet.Cells(iRow5, 1) = "" And oSheet.Cells(iRow6, 1) = "" _
    And oSheet.Cells(iRow7, 1) = "" And oSheet.Cells(iRow8, 1) = "" And oSheet.Cells(iRow9, 1) = "" _
    And oSheet.Cells(iRow10, 1) = ""
    
    If oSheet.Cells(iRow1, 1) = "" Then
        ' Don't do anything
        
    Else
        If oSheet.Cells(iRow1, 3) = "" Then
            
            Voice.Speak oSheet.Cells(iRow1, 1)
            
            Response = MsgBox(oSheet.Cells(iRow1, 1), vbYesNoCancel, "Do you know it?")
            
            If Response = vbYes Then
                Voice.Speak oSheet.Cells(iRow1, 2)
                MsgBox oSheet.Cells(iRow1, 2), , oSheet.Cells(iRow1, 1)
                oSheet.Cells(iRow1, 3) = 1
                iRow1 = iRow1 + 10
            ElseIf Response = vbNo Then
                Voice.Speak oSheet.Cells(iRow1, 2)
                MsgBox oSheet.Cells(iRow1, 2), , oSheet.Cells(iRow1, 1)
            Else
                Exit Sub
            End If
        Else
            iRow1 = iRow1 + 10
        End If
    End If
        
    If oSheet.Cells(iRow2, 1) = "" Then
        ' Don't do anything
        
    Else
        If oSheet.Cells(iRow2, 3) = "" Then
            Voice.Speak oSheet.Cells(iRow2, 1)
            Response = MsgBox(oSheet.Cells(iRow2, 1), vbYesNoCancel, "Do you know it?")
        
            If Response = vbYes Then
                Voice.Speak oSheet.Cells(iRow2, 2)
                MsgBox oSheet.Cells(iRow2, 2), , oSheet.Cells(iRow2, 1)
                oSheet.Cells(iRow2, 3) = 1
                iRow2 = iRow2 + 10
            ElseIf Response = vbNo Then
                Voice.Speak oSheet.Cells(iRow2, 2)
                MsgBox oSheet.Cells(iRow2, 2), , oSheet.Cells(iRow2, 1)
            Else
                Exit Sub
            End If
        Else
            iRow2 = iRow2 + 10
        End If
    End If

    If oSheet.Cells(iRow3, 1) = "" Then
        ' Don't do anything
        
    Else
        If oSheet.Cells(iRow3, 3) = "" Then
            Voice.Speak oSheet.Cells(iRow3, 1)
            Response = MsgBox(oSheet.Cells(iRow3, 1), vbYesNoCancel, "Do you know it?")
        
            If Response = vbYes Then
                Voice.Speak oSheet.Cells(iRow3, 2)
                MsgBox oSheet.Cells(iRow3, 2), , oSheet.Cells(iRow3, 1)
                oSheet.Cells(iRow3, 3) = 1
                iRow3 = iRow3 + 10
            ElseIf Response = vbNo Then
                Voice.Speak oSheet.Cells(iRow3, 2)
                MsgBox oSheet.Cells(iRow3, 2), , oSheet.Cells(iRow3, 1)
            Else
                Exit Sub
            End If
        Else
            iRow3 = iRow3 + 10
        End If
    End If

    If oSheet.Cells(iRow4, 1) = "" Then
        ' Don't do anything
        
    Else
        If oSheet.Cells(iRow4, 3) = "" Then
            Voice.Speak oSheet.Cells(iRow4, 1)
            Response = MsgBox(oSheet.Cells(iRow4, 1), vbYesNoCancel, "Do you know it?")
        
            If Response = vbYes Then
                Voice.Speak oSheet.Cells(iRow4, 2)
                MsgBox oSheet.Cells(iRow4, 2), , oSheet.Cells(iRow4, 1)
                oSheet.Cells(iRow4, 3) = 1
                iRow4 = iRow4 + 10
            ElseIf Response = vbNo Then
                Voice.Speak oSheet.Cells(iRow4, 2)
                MsgBox oSheet.Cells(iRow4, 2), , oSheet.Cells(iRow4, 1)
            Else
                Exit Sub
            End If
        Else
            iRow4 = iRow4 + 10
        End If
    End If

    If oSheet.Cells(iRow5, 1) = "" Then
        ' Don't do anything
        
    Else
        If oSheet.Cells(iRow5, 3) = "" Then
            Voice.Speak oSheet.Cells(iRow5, 1)
            Response = MsgBox(oSheet.Cells(iRow5, 1), vbYesNoCancel, "Do you know it?")
        
            If Response = vbYes Then
                Voice.Speak oSheet.Cells(iRow5, 2)
                MsgBox oSheet.Cells(iRow5, 2), , oSheet.Cells(iRow5, 1)
                oSheet.Cells(iRow5, 3) = 1
                iRow5 = iRow5 + 10
            ElseIf Response = vbNo Then
                Voice.Speak oSheet.Cells(iRow5, 2)
                MsgBox oSheet.Cells(iRow5, 2), , oSheet.Cells(iRow5, 1)
            Else
                Exit Sub
            End If
        Else
            iRow5 = iRow5 + 10
        End If
    End If

    If oSheet.Cells(iRow6, 1) = "" Then
        ' Don't do anything
        
    Else
        If oSheet.Cells(iRow6, 3) = "" Then
            Voice.Speak oSheet.Cells(iRow6, 1)
            Response = MsgBox(oSheet.Cells(iRow6, 1), vbYesNoCancel, "Do you know it?")
        
            If Response = vbYes Then
                Voice.Speak oSheet.Cells(iRow6, 2)
                MsgBox oSheet.Cells(iRow6, 2), , oSheet.Cells(iRow6, 1)
                oSheet.Cells(iRow6, 3) = 1
                iRow6 = iRow6 + 10
            ElseIf Response = vbNo Then
                Voice.Speak oSheet.Cells(iRow6, 2)
                MsgBox oSheet.Cells(iRow6, 2), , oSheet.Cells(iRow6, 1)
            Else
                Exit Sub
            End If
        Else
            iRow6 = iRow6 + 10
        End If
    End If

    If oSheet.Cells(iRow7, 1) = "" Then
        ' Don't do anything
        
    Else
        If oSheet.Cells(iRow7, 3) = "" Then
            Voice.Speak oSheet.Cells(iRow7, 1)
            Response = MsgBox(oSheet.Cells(iRow7, 1), vbYesNoCancel, "Do you know it?")
        
            If Response = vbYes Then
                Voice.Speak oSheet.Cells(iRow7, 2)
                MsgBox oSheet.Cells(iRow7, 2), , oSheet.Cells(iRow7, 1)
                oSheet.Cells(iRow7, 3) = 1
                iRow7 = iRow7 + 10
            ElseIf Response = vbNo Then
                Voice.Speak oSheet.Cells(iRow7, 2)
                MsgBox oSheet.Cells(iRow7, 2), , oSheet.Cells(iRow7, 1)
            Else
                Exit Sub
            End If
        Else
            iRow7 = iRow7 + 10
        End If
    End If

    If oSheet.Cells(iRow8, 1) = "" Then
        ' Don't do anything
        
    Else

        If oSheet.Cells(iRow8, 3) = "" Then
            Voice.Speak oSheet.Cells(iRow8, 1)
            Response = MsgBox(oSheet.Cells(iRow8, 1), vbYesNoCancel, "Do you know it?")
        
            If Response = vbYes Then
                Voice.Speak oSheet.Cells(iRow8, 2)
                MsgBox oSheet.Cells(iRow8, 2), , oSheet.Cells(iRow8, 1)
                oSheet.Cells(iRow8, 3) = 1
                iRow8 = iRow8 + 10
            ElseIf Response = vbNo Then
                Voice.Speak oSheet.Cells(iRow8, 2)
                MsgBox oSheet.Cells(iRow8, 2), , oSheet.Cells(iRow8, 1)
            Else
                Exit Sub
            End If
        Else
            iRow8 = iRow8 + 10
        End If
    End If

    If oSheet.Cells(iRow9, 1) = "" Then
        ' Don't do anything
        
    Else
        If oSheet.Cells(iRow9, 3) = "" Then
            Voice.Speak oSheet.Cells(iRow9, 1)
            Response = MsgBox(oSheet.Cells(iRow9, 1), vbYesNoCancel, "Do you know it?")
        
            If Response = vbYes Then
                Voice.Speak oSheet.Cells(iRow9, 2)
                MsgBox oSheet.Cells(iRow9, 2), , oSheet.Cells(iRow9, 1)
                oSheet.Cells(iRow9, 3) = 1
                iRow9 = iRow9 + 10
            ElseIf Response = vbNo Then
                Voice.Speak oSheet.Cells(iRow9, 2)
                MsgBox oSheet.Cells(iRow9, 2), , oSheet.Cells(iRow9, 1)
            Else
                Exit Sub
            End If
        Else
            iRow9 = iRow9 + 10
        End If
    End If

    If oSheet.Cells(iRow10, 1) = "" Then
        ' Don't do anything
        
    Else
        If oSheet.Cells(iRow10, 3) = "" Then
            Voice.Speak oSheet.Cells(iRow10, 1)
            Response = MsgBox(oSheet.Cells(iRow10, 1), vbYesNoCancel, "Do you know it?")
        
            If Response = vbYes Then
                Voice.Speak oSheet.Cells(iRow10, 2)
                MsgBox oSheet.Cells(iRow10, 2), , oSheet.Cells(iRow10, 1)
                oSheet.Cells(iRow10, 3) = 1
                iRow10 = iRow10 + 10
            ElseIf Response = vbNo Then
                Voice.Speak oSheet.Cells(iRow10, 2)
                MsgBox oSheet.Cells(iRow10, 2), , oSheet.Cells(iRow10, 1)
            Else
                Exit Sub
            End If
        Else
            iRow10 = iRow10 + 10
        End If
    End If
Loop
End Sub