Subscribe To
This Site

VBA Excel Macro Code

The following VBA Excel macro code shows how you can use a speadsheet instead of flash cards. This example uses the 50 states and their capitals. A state appears in column A and after a few seconds its capital appears in column B.

'Option Base 1 makes the arrays start at 1 instead of 0
Option Base 1
Sub States()
    Dim iStart As String, iEnd As String, iUsed(100) As Integer
    Dim iCnt As Integer, iCntArr As Integer
    Dim MyValue As String, tmp As String
'Set the range and initialize all the array
    iStart = 1
    iEnd = 50
    For iCnt = iStart To iEnd
        iUsed(iCnt) = 0
    Next
'Set our counter back to 1
    iCnt = 1
'Randomize causes the Rnd function (5 lines below)
'to start with a random seed value
    Randomize
    Do Until iCnt = 51
        Do
        MyValue = Int((iEnd * Rnd) + 1)
            For iCntArr = 1 To iEnd
                If MyValue = iUsed(iCntArr) Then Exit For
                If iUsed(iCntArr) = 0 Then Exit Do
            Next
        Loop
        iUsed(iCnt) = MyValue
        tmp = Worksheets("Sheet1").Range("A" & MyValue).Value
        Worksheets("Sheet2").Range("A" & iCnt).Value = tmp
        Application.Wait (Now + TimeValue("0:00:03"))
        tmp = Worksheets("Sheet1").Range("B" & MyValue).Value
        Worksheets("Sheet2").Range("B" & iCnt).Value = tmp
        Application.Wait (Now + TimeValue("0:00:01"))
        iCnt = iCnt + 1
        DoEvents
    Loop

End Sub

End of the VBA Excel macro code.

The Do Loop first gets a random value between 1 and 50. The For Next block loops until we have a value that hasn't been displayed yet in the spreadsheet. It does this by checking each random value against the values that were already used and stored in the iUsed array...

When it finds a value which is thus far unused, it exits the For Next loop and stores the value in the iUsed array.

The A column on Sheet2 is then populated with the state from the A column from Sheet1. You then have 3 seconds to remember the state's capital.

You will need to add the states to column A on Sheet1-one per line, and their capitals in column B of Sheet1...

To just test the code, add a few and adjust the counters from 50 to x rows of test data and from 51 to x + 1.

Before you run the macro, set Sheet2 as your active sheet.

To stop the macro, press Ctrl-Break.

Return from VBA Excel Macro Code to "VBA Code Samples"

Return to our "Home page"