Visio Bingo Template

Recently, Matt R asked about a Visio Bingo Template for his marketing team. I thought to myself, “Now there’s a mission-critical application of Visio!”

Well actually, it was a ShapeSheet challenge. I wanted to make a card that allowed the user to randomly update the numbers, but without using any code.

The resulting bingo card shape is implemented entirely using the ShapeSheet. There’s no VBA code behind the number generation, so you don’t have to worry about any VBA Macro Security issues.

How to Use the Visio Bingo Template

To get a new set of numbers, simply right-click the board and select “Choose New Values” from the menu. Or you can just double-click the board to get a new set.

The board shape is fully re-sizable, so you can save paper and put four boards on one page. The numbers are re-generated “on-drop”, so you don’t have to worry about creating duplicate boards and winding up with lots of simultaneous winners.

Currently, the download is available in Visio 2007 format only. This is because I wanted to make use of Visio 2007’s nifty Theme Colors and Theme Effects features:

Visio 2003 users should be able to open and use the Bingo Template, but they won’t have the ability to change the colors via theming.

Please post a comment at the end of the article if you really need the template in the older format.

How It Works

If you’re into the ShapeSheet like we Visio Guys are, you might be interested in how we pulled this off. Since a full description would take days to write, I’ll provide you with a few hints:

The shape has two levels of grouping: The main shape groups together five BINGO letters and five columns. Each column shape is a group of five number shapes, plus a background.

Each B-I-N-G-O column can choose five values from 15 possible values. So we start with a list of possible values. For instance, the “B” column can choose from: “+1;+2;+3;+4;+5;+6;+7;+8;+9;+10;+11;+12;+13;+14;+15” This is string is stored at the top-level in “User.valsB.” It is then referred to in the B-column shape’s ShapeSheet, in cell “User.val1.prompt”.

We then have five user cells that store randomly generated indices into this list: “User.val1”, “User.val2”, “User.val3”, “User.val4”, “User.val5” But the boxes can have repeat values, we have to make a successively shorter list for each val. So val2’s list of choices needs to not include the value that val1 chose.

So “User.val1” chooses from the main list using the RAND() function to get a number between 0 and 14, and the INDEX function to select from the list.

The list of choices for “User.val2” then must not include the value that User.val1 chose. We eliminate this by using the Substitute ShapeSheet function to make a new list of 14 numbers. The “+” characters help make sure we don’t affect more than one value — when we get rid of “2”, we don’t want “12” to be affected. So replace “+2;” with “”, which gets rid of 2, but not 12.

To cause the randomness, we use the SETF function to push a random value into each User.val cell. We have to write the RAND function to get a random value, but the RAND function tends to function often. Officially, it updates once per minute, but I found that it went off more than that.

So we used some trickery to get a random value, then to save the value, but erase the function. This involved setting each User.val cell twice. Have a look at the system of User-cells that we used to do this:

• User.trigger = some value, changed by double-clicking or right clicking
• User.setFormulas = DEPENDSON(User.trigger) + SETF(GetRef(User.val1), “INT(15*RAND())” )…
• User.setValues = SETF( GetRef(User.val1), User.val1 )

User.trigger is just a value that we change when the user double-clicks the shape, drops the shape, or right-clicks the shape. A formula like: SETF( GetRef(User.trigger), NOT(User.trigger) ) is used to change the value.

When User.trigger changes, the DEPENDSON function in User.setFormulas causes the SETFs in that cell to go off. These push actual formulas into the val cells. The quotes around “INT(15*RAND())” tell Visio to write the formula to the cell.

When User.val1 is changed by User.setFormulas, User.setValues is then triggered to go into action. Since the SETFs in this cell have no quotes, they simply overwrite the formula with the last value. So the flow is like this:

1. SETF writes formula
2. ShapeSheet calculate random value
3. SETF overwrites formula with current value

The ideas are simple, but reading the ShapeSheet isn’t that easy. Plus, there are several levels of grouping that make it a bit harder to track down what’s going on. Hopefully this description will help the curious to more easily figure out how the Bingo Beast works.

Download The Visio Bingo Template

To re-iterate, Visio 2003 users should be able to open and use the Bingo Template, but they won’t be able to take advantage of the theming features.

Please post a comment if you really need the template in the older format.

Download “Visio Bingo Template (2007)” s!Aj0wJuswNyXlhSqp2qA38_aQlLqg – Downloaded 4631 times – 103.00 B