Bingo
Deb created some bingo cards in Excel and issued a challenge to come up with a number selector. Here’s mine.
When I recorded this video, I didn’t know that the only certain numbers could go with certain letters. I’ve changed the workbook to accommodate that rule, but I don’t feel like re-recording, so you’ll have to live with it. Since this is my first attempt at using Jing, there’s about 9 seconds of dead time at the start of this video. Just be patient and it will come.
You can download Bingo.xls.zip
Here’s how I get the draw:
Function GetRandomDraw() As String
Dim lNumber As Long
Dim lLetter As Long
Dim sNumber As String
Dim sLetter As String
Dim lHigh As Long, lLow As Long
lLetter = Rnd * (5 - 1) + 1
lHigh = lLetter * 15
lLow = lHigh - 14
lNumber = Rnd * (lHigh - lLow) + lLow
sNumber = Format(wshStore.Range("rngNumbers").Item(lNumber).Value, "00")
sLetter = wshStore.Range("rngLetters").Item(lLetter).Value
GetRandomDraw = sLetter & "-" & sNumber
End Function
Dim lNumber As Long
Dim lLetter As Long
Dim sNumber As String
Dim sLetter As String
Dim lHigh As Long, lLow As Long
lLetter = Rnd * (5 - 1) + 1
lHigh = lLetter * 15
lLow = lHigh - 14
lNumber = Rnd * (lHigh - lLow) + lLow
sNumber = Format(wshStore.Range("rngNumbers").Item(lNumber).Value, "00")
sLetter = wshStore.Range("rngLetters").Item(lLetter).Value
GetRandomDraw = sLetter & "-" & sNumber
End Function
There’s some flakiness with copying and pasting shapes, but it doesn’t happen too frequently. Hopefully, if you download the workbook, it won’t happen to you. 
[...] Dick has created an Excel Bingo Number Selector file. You can watch his video demo and download the sample [...]
Nice! And it worked okay for me, in Excel 2007.
The code may seem to work but it is biased away from the end columns and the top and bottom rows. Relying on rounding to generate random integers guarantees that the integers will not be uniformly distributed. Rounding means that the probability of generating the 2 extreme numbers is 1/2 that of the other numbers. Try this test:
Dim X(4), I As Long
For I = 0 To 4: X(I) = 0: Next I
For I = 1 To 10000
Dim aVal As Long
aVal = Rnd() * (4 - 0) + 0
X(aVal) = X(aVal) + 1
Next I
Debug.Print Join(X, ",") 'X(0) and X(4) will be about 1/2 X(others)
End Sub
To generate a single unbiased US Bingo Number, use the below. It generates a unique random number in a single step (after the initialization, of course {grin}).
Dim LastIdx As Integer, AllNbrs(74) As Byte, Letters() As String
Sub Swap(ByRef A, ByRef B)
Dim Temp
Temp = A: A = B: B = Temp
End Sub
Sub resetBingo()
Dim I As Byte
For I = 0 To UBound(AllNbrs): AllNbrs(I) = I + 1: Next I
LastIdx = UBound(AllNbrs)
Letters = Split("B,I,N,G,O", ",")
End Sub
Function USBingoNumber()
Dim aRnd As Byte: aRnd = Int(Rnd() * (LastIdx - 0 + 1) + 0)
Swap AllNbrs(aRnd), AllNbrs(LastIdx)
USBingoNumber = AllNbrs(LastIdx): LastIdx = LastIdx - 1
USBingoNumber = Letters((USBingoNumber - 1) \ 15) _
& Format(USBingoNumber, "00")
End Function
To test the above code, run the GoodBingo procedure below. It fills up the activesheet with the results on 10,000 simulated runs, one row per simulation. At the bottom, it totals how often each of the 75 numbers were “called.” One would expect those numbers to be *on average* about 24/75*10000=3200.
Dim X(74), I As Long
For I = 0 To 74: X(I) = 0: Next I
resetBingo
For I = 1 To 24
Dim aVal As String, Idx As Byte
aVal = USBingoNumber()
Idx = Val(Mid(aVal, 2, Len(aVal))) - 1
X(Idx) = X(Idx) + 1
Next I
With ActiveSheet
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, 75).Value = X
End With
End Sub
Function GoodBingo()
Dim I As Integer
For I = 1 To 10000: OneBingo: Next I
With ActiveSheet
Dim CurrRow As Long: CurrRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
.Cells(CurrRow, 1).Resize(1, 75).Formula = "=SUM(A1:A" & (CurrRow - 1) & ")"
End With
End Function
Bingo? Why not Powerball?
Here’s a link to a Excel Bingo that I made many years ago. I can’t say that I remember how it works:)
http://www.cpearson.com/Zips/Bingo.ZIP
I like Jing, now I can post my Newton’s Cradle animation on my blog.
But that prompts the question, is there a way to save an Excel animation directly to a file, without going through a screen capture program?
Sub MaybeNotSoBadBingo()
‘Int((upperbound - lowerbound + 1) * Rnd + lowerbound) - Excel Help
Dim X(4) As Variant, I As Long
Dim aVal As Long
For I = 0 To 4: X(I) = 0: Next I
For I = 1 To 10000
‘ aVal = Rnd() * (4 - 0) + 0
aVal = Int(Rnd * (4 - 0 + 1) + 0)
X(aVal) = X(aVal) + 1
Next I
Debug.Print Join(X, “,”)
End Sub
‘–
Runs very close to 2000 in each element.
Dick, I sent a file to Debra that she has posted that is loosely based on yours. I used an array of “completed” numbers and flagged used balls and set up a bingo board instead of the flying balls. You’ll definitely recognize the buttons and routine names, etc.
[...] Posted on March 18, 2009 by dougaj4 Dick Kusleika at Daily-Dose-of-Excel recently posted a video of an animated bingo spreadsheet, using the Jing screen capture [...]
Dick - After a few tries I got Jing to link to my blog by using Vopod. Did you have any problems?
Or did you splash out on the “pro” version?
Nope, free version. The first time I did it, I created an swf file on my hard drive and tried to upload that to YouTube. It didn’t work. Then I realized/remembered that Jing had it’s own online storage thing. Once I signed in to that, it uploaded it automatically and I was able to copy and paste the embed tag and it just worked.
Hi Dick -
Under the guise of graphic presentation of data, I tried to sneak in some ncaa basketball, but the links wouldn’t show. It’s still in draft. Is there some extra magic required? I’d have thought that straight ol’ HTML would have worked, as that was what WordPress pasted in.
It may be also that the NYTimes doesn’t play nicely with others.
…mrt
MRT: You can’t have an html file as the source of an image tag - it has to be an image file. I got screen captures of those and changed the image tags in your draft.
Dick -
Thank you. I’m learning still. Standing by to hear from the Big 12 fans
…mrt