Excel Flashcards

I choose to study to be certified as a cost engineer and just recently took the test. There was a whole document of terminology I needed to be familiar with. Since I needed a relatively painless way to cover the words I thought I’d create flash cards in Excel to help go through them. I converted the PDF file with the terminology into a list in Excel. Column A had the Description of the word and Column B had the Definition.

The code is a bit verbose and could probably be reduced quite a bit. The code goes through the first 10 words, providing a message box of the word. I then have the opportunity to indicate that I know the word or not. If I don’t know the word, it stays in the queue. If I indicate that I do know the word a 1 is put in Column C and then next word is added to the queue. By indicating that I know it in Column C I’m able to start back up where I started if I have to go and do something else. In both cases I’m presented with the definition of the word. I also added information to the worksheet indicating how many words there are and how many words I have remaining. I created a Shape and assigned the macro to it so when I push the shape the macro starts. Here’s the flash cards in action:

Here’s the code:

Option Explicit

Public Voice As New SpVoice

Sub FlashCard()
    
    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
                MsgBox oSheet.Cells(iRow1, 2), , oSheet.Cells(iRow1, 1)
                oSheet.Cells(iRow1, 3) = 1
                iRow1 = iRow1 + 10
            ElseIf Response = vbNo Then
                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
        
            Response = MsgBox(oSheet.Cells(iRow2, 1), vbYesNoCancel, "Do you know it?")
        
            If Response = vbYes Then
                MsgBox oSheet.Cells(iRow2, 2), , oSheet.Cells(iRow2, 1)
                oSheet.Cells(iRow2, 3) = 1
                iRow2 = iRow2 + 10
            ElseIf Response = vbNo Then
                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
        
            Response = MsgBox(oSheet.Cells(iRow3, 1), vbYesNoCancel, "Do you know it?")
        
            If Response = vbYes Then
                MsgBox oSheet.Cells(iRow3, 2), , oSheet.Cells(iRow3, 1)
                oSheet.Cells(iRow3, 3) = 1
                iRow3 = iRow3 + 10
            ElseIf Response = vbNo Then
                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
        
            Response = MsgBox(oSheet.Cells(iRow4, 1), vbYesNoCancel, "Do you know it?")
        
            If Response = vbYes Then
                MsgBox oSheet.Cells(iRow4, 2), , oSheet.Cells(iRow4, 1)
                oSheet.Cells(iRow4, 3) = 1
                iRow4 = iRow4 + 10
            ElseIf Response = vbNo Then
                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
        
            Response = MsgBox(oSheet.Cells(iRow5, 1), vbYesNoCancel, "Do you know it?")
        
            If Response = vbYes Then
                MsgBox oSheet.Cells(iRow5, 2), , oSheet.Cells(iRow5, 1)
                oSheet.Cells(iRow5, 3) = 1
                iRow5 = iRow5 + 10
            ElseIf Response = vbNo Then
                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
        
            Response = MsgBox(oSheet.Cells(iRow6, 1), vbYesNoCancel, "Do you know it?")
        
            If Response = vbYes Then
                MsgBox oSheet.Cells(iRow6, 2), , oSheet.Cells(iRow6, 1)
                oSheet.Cells(iRow6, 3) = 1
                iRow6 = iRow6 + 10
            ElseIf Response = vbNo Then
                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
        
            Response = MsgBox(oSheet.Cells(iRow7, 1), vbYesNoCancel, "Do you know it?")
        
            If Response = vbYes Then
                MsgBox oSheet.Cells(iRow7, 2), , oSheet.Cells(iRow7, 1)
                oSheet.Cells(iRow7, 3) = 1
                iRow7 = iRow7 + 10
            ElseIf Response = vbNo Then
                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
        
            Response = MsgBox(oSheet.Cells(iRow8, 1), vbYesNoCancel, "Do you know it?")
        
            If Response = vbYes Then
                MsgBox oSheet.Cells(iRow8, 2), , oSheet.Cells(iRow8, 1)
                oSheet.Cells(iRow8, 3) = 1
                iRow8 = iRow8 + 10
            ElseIf Response = vbNo Then
                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
        
            Response = MsgBox(oSheet.Cells(iRow9, 1), vbYesNoCancel, "Do you know it?")
        
            If Response = vbYes Then
                MsgBox oSheet.Cells(iRow9, 2), , oSheet.Cells(iRow9, 1)
                oSheet.Cells(iRow9, 3) = 1
                iRow9 = iRow9 + 10
            ElseIf Response = vbNo Then
                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
        
            Response = MsgBox(oSheet.Cells(iRow10, 1), vbYesNoCancel, "Do you know it?")
        
            If Response = vbYes Then
                MsgBox oSheet.Cells(iRow10, 2), , oSheet.Cells(iRow10, 1)
                oSheet.Cells(iRow10, 3) = 1
                iRow10 = iRow10 + 10
            ElseIf Response = vbNo Then
                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