LSP 121 – Activity 13, VBA for Excel programming
CODE for Coin Toss Excel program using a macro
You do not need to enter the comments in the code below; comments are preceded by an apostrophe in the code below. However, it is a good idea to include the comments which explain the lines that follow. If you review this code later, the comments help you remember what the code is for. Type exactly as you see ‘upper case’ or ‘lower case’. Usually there is NO space to the left of a ‘(‘, such as in Cells(Row, Col)
After you open the VBA editor (press ALT+F11, then F5 and name the macro), name the macro coin_flipper, then you will see, minus the part in { }. See bottom of page 2 for what your code should look like if done correctly:
Sub coin_flipper()
{ enter code here }
End Sub
The first line to enter begins with ‘Declare’ as below. Type the entire line
that appears below. Type it BEFORE the Sub coin_flipper() line. Indenting is optional.
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub coin_flipper()
'initialize the Random number generator
Randomize
'clear out cells
For Row = 1 To 100
For Col = 1 To 4
Cells(Row, Col) = Null
Next Col
Next Row
Calculate
'pause a bit
Sleep 500
For Row = 1 To 100
Cells(Row, 1) = Row
Next Row
'here's where we flip the coin
For Row = 1 To 100
R = Rnd()
If R < 0.5 Then
Cells(Row, 2) = 1
Else
Cells(Row, 2) = 0
End If
Next Row
'build a running count of heads (1's) in column 3
Cells(1, 3) = Cells(1, 2)
For Row = 2 To 100
Cells(Row, 3) = Cells(Row - 1, 3) + Cells(Row, 2)
Next Row
'column 4 gets running percentage
For Row = 1 To 100
Cells(Row, 4) = Cells(Row, 3) / Row
Calculate
Next
'pause again
Sleep 3000
End Sub
******************
Be sure ‘End Sub’ occurs only once. It should already be there – do not add an extra ‘End Sub’ to the program code.
This is what your code should look like at the very beginning.
Notice the ‘Declare Sub Sleep …’ is before Sub coin_flipper and is followed by a line. The line shows up automatically.
Page 1