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
No comments:
Post a Comment