Bingo Card Generator Using Microsoft Excel and Word

Over the years, I’ve seen different variations of bingo used at networking events and holiday parties. You make a list of answers to icebreaker questions, print out the cards, and people go searching for others in the group who can give them the answers they are looking for in hopes of getting a bingo. It brings out the competitive nature of most people and lets everyone learn a thing or two about each other.

The problem with doing a bingo game like that is you have to manually make the bingo cards, download a premade set, or use one of the hundreds of online bingo card generators. Well, because I believe a problem is not worth solving if you can’t solve it with Microsoft Excel, I created an Excel spreadsheet that makes bingo cards. Not only will it allow you to control the output better, but it is also a great example of using Microsoft VBA with both Excel and Microsoft Word.

Note: If you are not into programming or doing things the hard way, use your favorite search engine to find a free, online tool to generate your bingo cards. But if you want to learn more about VBA programming and control your own destiny, read on.

How Does the Excel Bingo Card Generator Work?

The important thing about a bingo card generator is to make sure that the items in the squares on the card are random. For each 5×5 card, we need 24 random items from a list (not 25 because “Bingo” is in the middle).

This tool gets that done by:

1. The user provides a list of items to put on the cards. The more the better
2. Use the Excel randombetween() function to create a random number for each entry in the item table
3. Sort by random numbers. This puts 24 new items at the top of the table
4. The “card” on the spreadsheet, formatted cells, uses the first 24 rows from the table for each block on the card
5. Copy that 5×5 formatted array to Word
6. Repeat for how many ever times the user wants

Once the VBA script is done, the user has a Word file they can add a header to then send to the printer.

The tool consists of an Excel file with a VBA Macro called GenerateBingoCards. There are two sheets in the Excel workbook. The first is where you enter in your items. As you can see it also has instructions and a view of what items will end up on the card.

The second sheet, “Card” has a formatted version of the card along with the only input, the number of cards you want, and a button to run the macro.

Please don’t change the formatting of this table other than the colors. It is sized to fit on a single page in Word and gives you room for a header and a footer.

This is what one of the cards looks like after you add a header:

It is pretty simple, and once you have a Word file, you can do a lot with it.

The VBA Bingo Card Generator for Excel and Word

Here is the source code for the macro. It is fairly simple and the comments explain every step.

Sub GenerateBingoCards()
    ' Simple VBA Script that takes a list of bingo card 
    '    entries in Excel and generates bingo cards in word
    '      By: Eric Miller
    '          PADT, Inc
    '          www.padtinc.com
    '
    '      Version 1: First public release
    '
    ' Input: The number of cards you want. Entered as an integer in 
    '    cell C2 of sheet "Card"
    '
    ' See "Instruction" Text box in the "Input" sheet of the workbook.
    
    ' Set up MS Word objects
    '    Note, if this generates an error, make sure you go to Tools > References
    '           and check: Microsoft Word NN.N Object Library

    ' Grab the app and make it visible
    Set wordApp = New Word.Application
    wordApp.Visible = True
    
    ' Create a new doc, set orientation to Portrait and set the margins
    '   Large margim on top is for you to add your title block
    Set wordDoc = wordApp.Documents.Add()
    With wordDoc.PageSetup
        .Orientation = wdOrientPortrait
        .TopMargin = wordApp.InchesToPoints(2.5)
        .BottomMargin = wordApp.InchesToPoints(0.5)
        .RightMargin = wordApp.InchesToPoints(0.5)
        .LeftMargin = wordApp.InchesToPoints(0.5)
    End With
    
    ' Get the number of cards the user wants from cell C2
    Set wws = ThisWorkbook.Sheets("Card")
    numCards = wws.Range("C2").Value
     
    ' Identify the worksheet and and table for the input we will use in the loop
    Set iws = ThisWorkbook.Sheets("Input")
    Set itemTable = iws.ListObjects("Item_Table")
  
    ' Loop for each card requestd
    For i = 1 To numCards
        ' Go to the input sheet and clear the cut/copy buffer
        iws.Select
        Application.CutCopyMode = False
        
        ' Sort the "Item_Table" based on the random number in the second column
        '   First do an update to get new random numbers in second column.
        '   then clear, set sort fields, set options, and apply
        iws.Calculate
        With itemTable.Sort
            .SortFields.Clear
            .SortFields. _
                Add2 Key:=Range("Item_Table[[#All],[Random '#]]"), _ 
                SortOn:=xlSortOnValues, _
                Order:=xlAscending, DataOption:=xlSortNormal
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        ' Go to the "Card" sheet and select the formated resuls
        '    Remember, the cells in the card point to the 
        '    first 24 rows in the Item Table
        Sheets("Card").Select
        Range("A4:E8").Select
        
        ' Copy the card contents
        Selection.Copy
        
        ' Now, over in word, paste the cells as a table into word
        Set wordSel = wordApp.Selection
        wordSel.PasteExcelTable _
            LinkedToExcel:=False, _
            WordFormatting:=False, _
            RTF:=False
            
        ' Center the table on the page
        wordSel.Tables(1).Rows.Alignment = wdAlignParagraphCenter
        ' Put some spaces in so when we get to the next paste, 
        ' it will paste a new table
        wordSel.TypeText Text:="   "
    Next i
    
End Sub

Excel Bingo Card Generator Files

Downloading Microsoft Office files with macros could be one of the most dangerous things you can do on the internet, so we have prepared a version with the macro embedded and one without, and you can paste it in yourself. The zip file also has a sample Word file with 25 cards. If you are not sure how to add a macro to an Excel spreadsheet, you should probably use one of the online tools.

Go Forth and Break Ice

We offer this tool to use how you see fit, with no warranty and no restrictions. If you need help, you can try shooting us an email at info@padtinc.com. The way we use it is we give everyone a card and a pen, and they have to walk around the crowd at an event asking them, “Have you ever…?” or “Do you…?” Three out of four times, the answer starts a conversation.

Let’s be honest. This is a bit of an old-school way to make these icebreaker bingo cards. But if you are like me, you like to keep control of things and use Excel. And besides, while everyone is using the bingo cards, you can tell them, “Actually, we made these using a VBA script in Excel” as your own icebreaker.

PADT is not a networking organization or an Excel programming house. We are the Southwestern US’s premier provider of mechanical engineering products and tools. If you or anyone you know software, hardware, or consulting for simulation, product development, or 3D Printing, reach out.

Learn more about how “We Make Innovation Work” at www.padtinc.com.