Bingo

Posted on by Dick Kusleika

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

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. :)

Posted in Uncategorized

16 thoughts on “Bingo”

  3. 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:

    Sub BadBingo()
        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}).

    Option Explicit

    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.

    Sub OneBingo()
        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

  6. 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?

  7. 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.

  8. 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. :-)

  10. 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?

  11. 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.

  12. 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

  13. 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.

  14. Dick –

    Thank you. I’m learning still. Standing by to hear from the Big 12 fans ;-)

    …mrt

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.