UK Bingo cards

[craigey1]

Apologies for asking for so much help on my very first post here.

I've been searching all over for a solution to be able to produce a UK style bingo card within Excel using formulas or VBA, but so far have only found code that's capable of producing singular games rather than a set of 6. I've searched here & seen that the question has been asked before, but so far no-one seems to have been able to come up with a solution. I thought I could look at randbetween() to generate the necessary numbers for each column, but couldn't see how to then comply the required game layout rules. I really don't know where to start with this. I'd normally try & then ask for help when stuck, so here I am!

The UK bingo games use numbers 1 to 90 (including 90) with the numbers split across 6 games of 3 rows by 9 columns. The number 1 to 9 would appear in the first column, 10 to 19 in column 2, 20 to 29 column 3 & so on until the 9th column which also includes the number 90. All numbers would only appear once across all 6 of the games & each box has 5 blanks across each row & can have 0, 1 or 2 blanks per column.
In summary:
1. A bingo 'card' is a set of 6 individual 3 x 9 grids, stacked vertically, which contain the numbers 1-90
2. The numbers 1 - 9 (9 numbers) are in the first column and 80-90 (11 numbers) in the 9th, the intervening columns (2-8) contain 10 numbers each.
3. An individual box has 15 numbers, 5 per row and between 0 and 2 per column

I'd appreciate any help with this as was hoping to generate cards for friends / family during the lockdown.

thanks in advance

 

[lrobbo314]

Think I was trying to be too clever with this subroutine. I updated and haven't ran into the problem again.

Sub SortSA(SA As Variant)
Dim TMP As Integer

For i = 1 To 3
    For j = i To 3
        If SA(i) > SA(j) And SA(i) <> 0 And SA(j) <> 0 Then
            TMP = SA(i)
            SA(i) = SA(j)
            SA(j) = TMP
        End If
    Next j
Next i
End Sub

 

[craigey1]

Think I was trying to be too clever with this subroutine. I updated and haven't ran into the problem again.

Fantastic, that seems to have fixed it for me. Thanks again, for all your time & effort.

 

[baldheadedscott]

Fantastic, that seems to have fixed it for me. Thanks again, for all your time & effort.

Do you have the file for this still? been trying to figure it out, and I just can't get it...

 

[tezza]

Do you have the file for this still? been trying to figure it out, and I just can't get it...

I haven't updated mine with the latest code but if you want a head start, here's a dropbox link to the one I'm using.

Bingo in Dropbox

It's been a while since I've used Dropbox so any issues let me know.

This runs fine on 2010 and 2016 Excel version.

Thanks to @lrobbo314 for the code, I'd have never got it running otherwise

 

[lrobbo314]

Thanks @tezza.

I made a playable javascript version as well. CSS isn't my strong suit, but I'm happy with how it came out. Optimized the swap algorithm a bit too.

Here is the link if anyone is interested.

p5.js Web Editor

Also, here is a link to an Excel workbook that has the code and everything.

UK Bingo No OBJ DB.xlsm

 

[tezza]

I made a playable javascript version as well. CSS isn't my strong suit, but I'm happy with how it came out. Optimized the swap algorithm a bit too.

That's taking it to another level lol, I've never touch on CSS.

 

[lrobbo314]

Don't know if anyone here saw it, but I also made an excel formula to generate US bingo cards. Can't imagine doing this for UK Bingo with formulas. Should be possible since Excel is now considered to be Turing complete with dynamic array formulas, but F me, no way!

Anyway, here's the link. US Bingo Formula

 

[baldheadedscott]

Thanks @tezza.

I made a playable javascript version as well. CSS isn't my strong suit, but I'm happy with how it came out. Optimized the swap algorithm a bit too.

Here is the link if anyone is interested.

p5.js Web Editor

Also, here is a link to an Excel workbook that has the code and everything.

UK Bingo No OBJ DB.xlsm

Okay, excuse my ignorance, but I don't know how or what to do to randomize the cards so that the next set/card is di=fferent than the one that shows up initially.
I made my own regular/US bingo cards, and I know how to do it on mine. But I can't seem to figure out how to do that with yours? Not sure if it's because of the macros or what?

 

[tezza]

Okay, excuse my ignorance, but I don't know how or what to do to randomize the cards so that the next set/card is di=fferent than the one that shows up initially.
I made my own regular/US bingo cards, and I know how to do it on mine. But I can't seem to figure out how to do that with yours? Not sure if it's because of the macros or what?

Enable macros and click New Books. That will run the macro to create new numbers in the tabs Book 1 to Book 10

The game tab just combines them all.

To run the code in your own workbook, go to a blank sheet and run the macro and it'll generate the numbers there for you. Each time you run the macro it will overwrite the old ones.

 

[tezza]

Okay, excuse my ignorance, but I don't know how or what to do to randomize the cards so that the next set/card is di=fferent than the one that shows up initially.
I made my own regular/US bingo cards, and I know how to do it on mine. But I can't seem to figure out how to do that with yours? Not sure if it's because of the macros or what?

I uploaded a quick video to dropbox to give you an idea of what I do.

Bingo vid in dropbox

The first tab picks the random numbers

The Game is for auto play mode, when the first tab generates a random number, it shows on all the games in the Game tab

The first tab also tracks at the bottom all of the games and will also show you when a line or house has been achieved.

For a fresh restart hit New Books, New Game then Next Number until there's a winner.

Can it be done better? More than likely but this is my limitations.