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.

This site is powered by Site Build It!. If you enjoy it, please check out the
Site Build It homepage to learn more and on how to build a success-guaranteed site with no technical skills.

End of the VBA Excel macro code web page.

Custom Search

Return from VBA Excel Macro Code to VBA Code Samples

Return to our Homepage

SBI!