My twins (Nishanth & Nakshtra) are now almost 7. They are super keen to learn how computers work. So the other day, I showed them Code.org where there are several coding exercises disguised as games. They loved those games … err coding exercises. So that got me thinking… why not make a game in Excel that teaches kids simple programming concepts.
Here is the version 1.0 of Snowman & Hot Chocolate Maze game:
In this post, let’s understand how to build such a game using Excel VBA.
The idea
This maze game is inspired from the Angry Birds coding game at Code.org
The idea is simple. We create a 16×16 grid in Excel. We position a snowman at a certain point in the grid. We place a cup of hot chocolate at another point. The goal is to make snowman reach hot chocolate with a series of commands. Of course there will be obstacles (walls).
Typical Blank Maze with Snowman & Hot Chocolate loaded.
Since kids will be playing this, we need considerable variety of mazes. So let’s assume that,
- There can be any number of mazes, one per spreadsheet.
- Each maze can have a different design, different starting & ending positions.
- Each maze can also have its own images (ie Rabbit & carrot in one maze, Kid and ice cream in another etc.)
- Making new mazes should be easy. You can copy a worksheet, paste it, change the settings and all the code should work just the same.
The implementation
There are two components in this game.
- The front end – ie what players see
- The back end – ie our VBA code
Designing the front end
Here is the basic layout of our maze front end.
We will create sheet specific named ranges for,
- grid for the maze
- settings for the settings
- code.start for the first cell of the code
Displaying the symbols:
We can use the Segoe UI Emoji font. This font is part of all modern versions of Windows. This has a fine collection of various emojis that you see everywhere nowadays. The Emoji hex codes are,
- 26C4 for Snowman
- 2615 for hot chocolate
- 2603 for Snowman with snow flakes
You can find more such emojis. Just go to Insert > Symbol and switch the font to Segoe UI Emoji font.
Note: download Segoe UI Emoji font if you don’t have it.
Adding the obstacles:
You (ie the parent) can write o in any cell inside the maze to build an obstacle there. So once a new maze is cloned (ie a new worksheet is copy pasted), simply create a maze layout by typing o into various cells.
Pro tip: Just select the maze layout you want with CTRL+Select option and the type o in any cell and press CTRL+Enter to get o in all cells.
All these os will be displayed as walls thru conditional formatting.
Designing the VBA back end
There are two parts to our VBA code.
- Setup the maze
- Solve the maze
Setting up the maze
The logic for this is simple.
- Clear the grid contents, except when the cell has “o”
- Set up snowman at starting position
- Set up hot chocolate at ending position
Here is the code for Maze setup.
Private Sub setupGameSheet(name As String)
'set up game sheet given by name
Dim grid As Range, settings As Range, code As Range, cell As Range
With Sheets(name)
Set grid = Range("grid")
Set settings = Range("settings")
Set code = Range("code.start")
For Each cell In grid
If cell.Value2 <> "o" Then cell.ClearContents
Next cell
grid.Cells(settings.Cells(1, 1), settings.Cells(1, 2)) = settings.Cells(1, 3)
grid.Cells(settings.Cells(2, 1), settings.Cells(2, 2)) = settings.Cells(2, 3)
Set grid = Nothing
Set settings = Nothing
Set code = Nothing
End With
End Sub
Solving the maze
This is where things get tricky.
Simplified algorithm for this goes like,
- For each line of the code
- Check the first character of the code.
- If L, go left
- If R, go right
- If U, go up
- If D, go down
- by the number of steps indicated after first letter.
- If the new position falls outside grid
- If so, display an error and end macro
- If the new position leads in to an obstacle
- display an error and end macro
- Else
- Print a snow man along the pathway
- If snow man reaches ending position
- Print snow man with snow flakes. End macro
- Continue to next line of code
- If at end of all code, the snow man still not at ending point
- Display an error message
Here is the code for this:
Private Sub runCode(name As String)
'runs the code in sheet given by name
setup
Dim code As Range, settings As Range, grid As Range, steps As Integer
Dim newx As Integer, newy As Integer, symbol As String, done As Boolean
Dim endx As Integer, endy As Integer, oldx As Integer, oldy As Integer
Set grid = Sheets(name).Range("grid")
Set settings = Sheets(name).Range("settings")
Set code = Sheets(name).Range("code.start")
newx = settings.Cells(1, 2)
newy = settings.Cells(1, 1)
symbol = settings.Cells(1, 3)
done = False
endx = settings.Cells(2, 2)
endy = settings.Cells(2, 1)
While (Len(code) > 0 And Not done)
oldx = newx
oldy = newy
steps = getNumber(code.Value2)
Select Case UCase(Left(code, 1))
Case "L"
newx = newx - steps
Case "R"
newx = newx + steps
Case "U"
newy = newy - steps
Case "D"
newy = newy + steps
Case Else
End Select
'check the boundary
If newx < 0 Or newy < 0 Or newx > 16 Or newy > 16 Then
MsgBox "Don't leave the box!", vbCritical + vbOKonlym, "Hold it there tiger..."
done = True
'check for obstacles
ElseIf hasObstacles(Range(grid.Cells(oldy, oldx), grid.Cells(newy, newx))) Then
MsgBox "Cant move there!", vbCritical + vbOKOnly, "Oo ooh! The snow man hit an obstacle"
done = True
Else
Range(grid.Cells(oldy, oldx), grid.Cells(newy, newx)) = symbol
If newx = endx And newy = endy Then
done = True
grid.Cells(newy, newx) = ChrW(doneSymbol)
End If
End If
killTime
Set code = code.Offset(1)
Wend
If Not done Then
MsgBox "Your snowman is thirsty, fetch him the hot chocolate", vbCritical + vbOKOnly, "Try Again"
End If
Set grid = Nothing
Set settings = Nothing
Set code = Nothing
End Sub
Supporting functions
To keep the code clean, I have created two functions and one sub. They are here.
Const doneSymbol = &H2603
Const speed = 900 'change this to speedup / slowdown the game
Function getNumber(ByVal fromThis As Variant) As Integer
'extract the number after first character
'return 1 incase of error
getNumber = 1
On Error Resume Next
getNumber = CInt(Mid(fromThis, 2))
End Function
Sub killTime()
Dim i As Long
For i = 1 To speed
DoEvents
Next i
End Sub
Function hasObstacles(thisRange As Range) As Boolean
Dim cell As Range
For Each cell In thisRange
hasObstacles = hasObstacles Or cell.Value = "o"
If hasObstacles Then Exit Function
Next cell
End Function
Linking front end & VBA
Now that our UI & Code are ready, let’s link them up.
We set up two buttons on the worksheet (using rounded rectangle shapes), one for setup and other for running.
We write two simple macros and assign them to the buttons.
Sub setup()
setupGameSheet ActiveSheet.name
End Sub
Sub run()
runCode ActiveSheet.name
End Sub
And our game is ready.
Let’s roll, the snowman is thirsty.
Download the Maze Coding Game
Go ahead and download the game workbook here. Examine the code to learn more. Ask your kids to play for some fun. The workbook contains 3 mazes. Feel free to add more.
How to extend this game?
Here are few ways to extend the game.
- Add special point cells to the grid. When snowman passes thru these cells, score goes up.
- Add a special power cell. If snowman reaches this cell, he gains the ability break obstacles.
- Play sounds for jumps, points and breaks.
How do you like this?
My kids loved the idea. At the same time, they don’t want to play more than 2 puzzles at a time. I think they are attracted to Oggy and the cockroaches more than programming for now.
What about you? Do you find such games useful to teach programming and a love of computers to your kids. Let me know your experience once trying this.
Learn how to code yourself
Not just kids, anyone can benefit by learning how to program. So if you are new to coding, check out our five part tutorial on VBA.
63 Responses to “To-do List with Priorities using Excel”
Very useful, you always give us good ideas for our excel files. Thanks
I've been working on calendars leagues. If you must watch a bit on my blog. http://economiaemergente.com/
EXCELLENTE!!!!
Needed .. thanks for sharing
[...] To-do List with Priorities using Excel [...]
Excellent spreadsheet. Nice work.
Ciao Peppe!, Tante grazie per compartire il tuo eccellentissimo lavoro in Excel. Tu hai a web blog? - Grazie Chandoo per la publicazione.
Hello Peppe, Thank you so much for sharing your most excellent work in Excel. Have you a web blog? - Thanks Chandoo for publication.
Hi Jose,
tanks for your appreciations and tks to Chandoo for publishing
my little job. it's a pleasure for me to be mentioned on my guru's blog.
Just to reply to Jose, I don't have a blog, but if you want to share some ideas or need some help don't hesitate to contact me also on twitter like @peppinogreco.
Regards
Peppe
Hi Peppe!
Thank you for this very useful excel spreadsheet!
Great!
I've learned a little bit of VBA during the last year, and get addicted to it, but sometimes, it makes us forget how powerful excel is, without macros.
Nice post!
Cauê
Hi Chandru,
A very good post. Though I had been reading your posts for a longer time, did not post any questions so far except for wishing and appreciating.
I have a question here. I had attempted to do something on my own (a little R & D) on the new year resolution template itself. However, I could not do it fully. Thankfully, you had provided the link for each step , which was exactly what I was looking for 🙂
I had done with the check boxes and also conditional formatting. I am glad indeed. I am able to highlight a row when a check box is checked. However, the value of the checkbox gets printed in the same cell which it was linked to. How can I avoid it ? I could not see it in the sample excel files you had provided.
I appreciate your help in this.
Cheers,
Raghavan alias Saravanan M
Jeddah | Kingdom of Saudi Arabia
Hi Raghavan... Thanks for your comments and I am glad you are trying to build this on your own. There is no way we can avoid printing the check box value in linked cell. If you do not want to see "TRUE or FALSE" in a cell, you do one of the following.
Dal Messico grazie tanti Peppe.
A great idea, thanks for shearing it with all of us.
Daniel
Lovely idea - downloading now.
What software is used to create the animated gif of the template in action? Love to replicate to simple demos on my site.
Cheers
Glen
@Glen
Chandoo uses Camtasia Studio to make the animated GIF's
You can read what else Chandoo uses here: http://chandoo.org/wp/about/what-we-use/
Cheers
I should have checked first.
G
Still defeated.
What I am looking for is the TORN edge effect as applied to the screen capture. I can see how to do this for captured images, not vidoes.
I wonder if they are post processed in some way?
Cheers
G
Sorted.
Capture white screen with torn paper edge with SnagIT
Make the inner of border transparent (Photoshop)
Add the image as an overlay in Camtasia.
Sorry to hijack an Excel thread with this - its been bugging me for a while.
G
Raghavan
I just make the font white for the cell linked to the checkbox or if you have shading applied then font colour = shading so its there but is not seen or printed.
John
Excellent! Thank you very much.
Excellent thanks!
Happy New Year.
Looks simple but excellent. Never knew you could do this without VBA.
Thanks Pepe
good day,
Please, how can I create a chart with scroll bar that is also dynamic in PPT.
I created the chart in Excel, but I need this information to be presented dynamically in powerpoint and when I put the bar rolls loses functionality. please can you help me?
Come nella migliore tradizione:grandi ma semplici idde dall'Italia.
bravo Peppe
This was outstanding. I have had two bosses give me to-do lists that I was very unhappy with. I went and added 15 more lines to this and it was really easy to so with a little reformatting and changing some links. THANKS!!!
Thanks. really usuful. Will be waiting for such thing in future.
Great tutorial! It would be interesting if someone could explain how to do the chart with detail: how to insert the values of the horizontal axis, to create the horizontal bar (the outlines) and the bar itself, etc
Hi Juan...
See this page for a tutorial on the chart - http://chandoo.org/wp/2009/12/17/quick-thermometer-chart/
[...] To-do List with Priorities using Excel [...]
Good Concept!
Downloaded it but, my Excel 2007 hangs and I have to recover it few times. Finally it opens but, everything is distorted.
Am I doing something wrong?
-DJ
Interesting idea.
You give e new way to track my actual planning.
But instead of using thermometer in this case, we can use a simple bar chart , with data is the total done.
Reasoning for that, with thermometer, you have to format all the small part of data with the same color. If you have more than 10 parts, it will take your time to finish.
I tested and it shown the same.
I'm searching for How to automatically add check box link to a new cells when we add new item?
Thanks for your interesting idea.
Thank you Peppe & Chandoo for sharing an awesome idea.
How do i increase the list ? I cant just drag down can I ? the check boxes perform the same way
VERY EXCELLENT THANK YOU VERY MUCH.
How do you increase the list? Formatting of the check boxes and shading etc does not copy correctly if using copy and paste or dragging cells down...
Thanks for this useful to do list.
I have the same question as TADOVN. This blog doesn't properly give instruction on how to add new task row. Following are my queries.
1) How do I add a new row?
2) If I copy paste the last row to create a new row, the check box get duplicated, i.e. if I click on the new check box on the new row, the previous check box also gets checked.
So the simple question is.. how do I add a new row so that it behaves the same way as other rows?
Thank you very much! Great to do list template.
Thanks for the template.
From an NGO organisation in Malaysia
Will someone please answer the question about how to add additional rows to this list? I love it, but this is a fatal flaw, as I frequently have many more tasks.
Thank you!
Below is how I added additional rows:
1) Select both columns H and N, right clicked, and clicked Unhide to reveal the formulas.
2) Select row 12 on the To Do List, copy it, and insert it below in the next row.
3) Change the 12 in cell C22 to a 13.
3) Drag your mouse and copy the formulas from cells I15, J15, and K15.
4) Paste the formulas below in cells I16, J16, and K16.
5) Right click on the check box in cell F22.
6) Click Format Control.
7) Click the Control tab.
8) In the Cell Link box, change the I15 to I16.
9) Repeat the steps above. (Change I16 in the Cell Link box to I17...I17 to I18, etc.)
10) If you are not seeing Format Control when you right click the check box, you need to make the Developer Tab available.
Leah,
If you follow my previous instructions, you still may need to go back and change the formulas in column K. They calculate the priority weights and go in consecutive order as you go down the column:
IFERROR(1/E10,0)
IFERROR(1/E11,0)
IFERROR(1/E12,0), etc.
Some of you who are more Excel savy may be able to figure out how to copy the formulas quicker. This is just the way I figured it out.
Thank you Dennis; I will try that!
I am sure I would love this and it will help me to accomplice my tasks efficiently . Thanks Buddy
How would I be able to delete one of the row (not use 6 for example) so it won't calculate it with the progress?
[…] 42,416] Angry Formulas game… [Visitors: 36,392] Learn top 10 Excel features [Visitors: 25,723] To-do list with priorities – Excel templates [Visitors: 19,947] Introduction to Power Pivot [Visitors: 21,298] Best new features in Excel 2013 […]
[…] 2013 Calendar, 2012 Calendar, 2011 Calendar, New Year Resolution Tracker, Picture Calendar Template and Todo list template […]
[…] ?? ????? ??????????? ?????? ?????? ????? ? ????????????? ??? Excel. ??? ???? ????? ?? ??? […]
Thank you so much for this post. I took me a bit to figure out how the checkboxes link to the rest of the sheet, but now that I've got it I've created a new page for every day so I can track tasks going forward. I've also added work tasks side-by-side with personal tasks. Once I did that I also thought it would be neat to see how productive I am week over week so I added a nice summery page. The summary builds on the percentage completion for personal and work tasks.
Love this template - so versatile and yet simple.
My next project is to get standard weighting for certain tasks so I don't have to keep remembering them.
Cheers,
Victor
I like this template. I may modify how the checkboxes work though for a couple reasons:
1) It's a pain to add more rows. If I want to add 10 more rows, it appears that I have to re-point each new object to the appropriate link-cell. Otherwise, they all point back to the copied row - checking one causes all of them to check.
2) I can't group and collapse rows in the checklist without all the objects stacking together and remaining visible in the lowest non-collapsed row. With a simple "x", this would be ok.
One solution would be to have a simple "x" instead of a checkbox object. I could just use an "x" to mark complete, and make the TRUE/FALSE based on an If formula (If "x" then TRUE; otherwise FALSE).
I downloaded the file, but it is a ZIP file with several subfolders and xml files. There is no workbook here. How do I open this in Excel?
thank you for the help and excellent ideas you share.
@Kris
Yes, Excel files are special Zip files that actually contain a number of files including your data
If the file opens like that save it locally as a *.zip file and rename it to a *.xlsx file
Open with excel normally
How do you change the color when it is completed....I have multiple companies and need to color code this template.
Thank you.
@S.F
Use Conditional Formatting
Hello! I have added additional rows, fixed it so that the check boxes work individually, AND made it so that the #% changes when each box is checked -- however the status bar won't move past the midway mark.
Any ideas on how I can get the progress bar to fill up the entire way once the list is complete?
If you right click the status bar, select 'Select Data' and go to 'Chart Data Range' and revise to include your expanded range. The bar chart colors may default to a predefined style. Right click the chart to reformat the Chart Area.
Or, to change the bar colors, I populated all rows w/ activities and rank and then left clicked the bar chart color that I wanted to change - went up to the ribbon under the home tab, selected the new bar color from the fill color dropdown.
Love the instant gratification of the status bar! Genius!
Thank you so much, what a great tool! God bless you for doing this for free!!
Awesome
Nice to show power of excel.
Over in the Chandoo.org Forums, Asshu has updated this witha VB Interface
Have a look and use if from: http://chandoo.org/forum/threads/to-do-list-vb-interface.28973/
Dear All,
There are good job done here & its very helpful for all.
God Bless You to you all for your valuable working.
Regards,
Chirag
Hi guys,
I've added additional rows, but the percentages in the thermo-meter don't reflect this when the boxes are checked. I'm lost with how to change this, so any assistance would be greatly appreciated!
Jake
@Jake
Can you please ask the question on the Chandoo.org Forums
https://chandoo.org/forum/
Please attach a sample file to simplify the solution
Hi Chandoo, how you do it for all this check list. it is using Excel VBA, I am not good it that.. still leaning part. and I was trying to figure out. Trying to understand all vba code and meaning and when I use which code.
do you have any guide line on this, i mean. Exp: dim is what, string etc:
for all this checking list does need to use VBA?
Thankyou
Peggy