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

[…] were a few follow up workbooks, including an Excel Bingo Number Selector file from Dick […]

Here is a BINGO card I just made using only formulas.

https://dhexcel1.wordpress.com/2015/03/31/bingo/