It’s been a great competition and now it is time to announce the winners.
Check out my new Excel challenges page to find out which solutions I received and who won the prize for best solution!
I hope you enjoyed this challenge. If you have more ideas for challenges, let me know!
Regards,
The Boy (but not this The Boy) is taking a Java class. I was watching him code up a hangman game last night. There’s a lot to like about Java, I think. It’s frustrating working with a new language when you don’t know all the debugging tricks. I couldn’t figure out how to step through code or use the console to inspect variables (like an Immediate Window) in Eclipse. I’m sure it can be done, I just don’t know how.
I decided to replicate his game in VBA. It’s pretty rough. It doesn’t tell you what you’ve already guessed, for one thing. Also, I don’t use the grid because he was using ASCII art in the Eclipse console and I wanted to do the same. Clearly shapes on the grid would be cooler. VBA doesn’t have a “scan” feature to get input, so I had to use InputBox and MsgBox to get user input.
As I was coding it, I was thinking that I should have use test-first development as an exercise, but I didn’t. For what it’s worth:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 |
Public Sub PlayHangman() Dim bPlaying As Boolean Dim sInput As String Dim lTries As Long Dim vaWords As Variant Dim sTheWord As String Dim aDispWord() As String Dim i As Long Const sTERMCHAR As String = "-" Const lMAXTRIES As Long = 6 bPlaying = True 'I'll pick one of these words at random vaWords = Split("jazz zigzag colour favourite doughnut rabbit") sTheWord = vaWords(Int(Rnd * (UBound(vaWords) - LBound(vaWords) + 1) + LBound(vaWords))) 'Fill the displayed word w "emtpy space" ReDim aDispWord(1 To Len(sTheWord)) For i = LBound(aDispWord) To UBound(aDispWord) aDispWord(i) = "_" Next i 'Start the game Do While bPlaying Debug.Print "Welcome to hangman" 'This is the guessing loop Do 'Print the hangman and whatever was guessed PrintHangman lTries Debug.Print Join(aDispWord, Space(1)) sInput = Left$(InputBox("Enter your guess", "Hangman"), 1) 'Type a dash to get abort early If sInput = sTERMCHAR Then bPlaying = False Exit Do End If 'Correct guesses get filled in the displayed word and incorrect 'guesses increments lTries If InStr(1, sTheWord, sInput) > 0 Then For i = 1 To Len(sTheWord) If Mid$(sTheWord, i, 1) = sInput Then aDispWord(i) = sInput End If Next i Else lTries = lTries + 1 End If Loop Until lTries >= lMAXTRIES Or Join(aDispWord, vbNullString) = sTheWord If Join(aDispWord, vbNullString) = sTheWord Then Debug.Print "You win" Else Debug.Print "You lose. The word was " & sTheWord End If bPlaying = MsgBox("Play again?", vbYesNo) = vbYes Loop End Sub Public Sub PrintHangman(ByVal lTries As Long) Dim aMan(1 To 5, 1 To 7) As String Dim i As Long, j As Long Dim sPrint As String aMan(1, 1) = "_": aMan(1, 2) = "_": aMan(1, 3) = "_": aMan(1, 4) = "_": aMan(1, 5) = "_": aMan(1, 6) = "_" For i = 2 To UBound(aMan) aMan(i, 1) = "|" For j = 2 To UBound(aMan, 2) aMan(i, j) = Space(1) Next j Next i aMan(2, 6) = "|" If lTries >= 1 Then aMan(3, 6) = "o" If lTries >= 2 Then aMan(4, 6) = "|" If lTries >= 3 Then aMan(4, 5) = "/" If lTries >= 4 Then aMan(4, 7) = "\" If lTries >= 5 Then aMan(5, 5) = "/" If lTries >= 6 Then aMan(5, 7) = "\" For i = LBound(aMan, 1) To UBound(aMan, 1) For j = LBound(aMan, 2) To UBound(aMan, 2) sPrint = sPrint & aMan(i, j) Next j Debug.Print sPrint sPrint = vbNullString Next i End Sub |
When Doug posted about Data Comparison Tricks, I saw Dick tell him to “pimp his site” in the comments.
Having a vivid imagination, this is what went through my head.
Anyway, that’s what I’ll now proceed to do. (Having obtained Dick’s permission first of course!)
Here’s some stuff I’ve been working on recently.
A multi-field Find and Select/Replace tool.
Although a bit old, (like me), some of the code came from this.
Here’s the old post about it from back in 2009. (From my former blog, which I’ll also pimp!)
An alternative Status Bar that recognizes numbers even if the format is text. Woohoo!
And some games. (For the kids, but you can play too)
That’s enough pimpin’ for now. (I’m making new stuff as I write this) See you next time?
The Civilization game dependency tree
How to build a dependency tree when there is no obvious connection between the business model and an Excel model.
A long time ago I spend a lot of time playing the strategy game Civilization. For those not familiar with the game, one of its features was “advances.” Each advance brought with it certain additional capabilities and benefits. Of course, there was a requirement before one could acquire an advance, particularly a set of pre-requisites. For example, pre-requisites for the “Navigation” advance were the “Seafaring” and the “Astronomy” advances. In turn, the Seafaring advance required one to already know “Pottery” and “Map Making”.
I created an Excel worksheet that let me use Excel’s Precedent arrows to understand the optimal path to specific advances as in Figure 1. The advances are shown in red and the benefit(s) of each advance are in black, blue and green. The Excel blue arrows show the pre-requisites for the Seafaring advance.
Figure 1
While the dated worksheet may be of limited value even to Civilization enthusiasts, the technique for creating the dependency tree is unique enough to be of value to Excel consumers. What makes it of value is that the game dependencies (in the context of work this would be the dependencies in the business model) had nothing to do with what Excel considers as dependencies! Consequently, to use Excel’s Trace Precedents feature I had to somehow map the model dependency into Excel formula dependencies.
For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0908%20CIV%20game%20dependency%20tree.shtml
Last month I read an article about a job interviewer who played TicTacToe with his applicants. It was like an instant shared round of golf, and the interviewer put great store in the correlation of good hires with good players. So in case any DDoE readers might run into that guy, here is TicTacToe (or Noughts and Crosses as it’s known across the pond) in a user form suitable for practice.
There are several XL versions out there in the wide world of web (though not too many in a user form) and this is just the latest. It’s a refresh of my first XL code that wasn’t a tutorial, written in XLM too many moons ago.
You can set if you go first or second, set if you are X or O, and set if XL plays hard (don’t know if it can be beat), medium (can be beat), or dumb (as in very dumb—makes random legal moves). It’ll keep score. If you play well, you can’t lose. If XL plays well, you can’t win.
Start refreshes the game, and Reset takes you back to where you can adjust the mode of play. It looks different, but it works fine on a Mac with XL 2011. Its default is the Comic Sans MS font. If you’re playing second, you can control the start square for XL by clicking Start until XL randomly picks the square you want it to use.
The form is available here. After importing, it needs just a one-line macro to run.
Sub TicTacToe()
frmTicTacToe.Show
End Sub
Presumably, that guy giving the interview plays TicTacToe well, so you won’t win. I don’t think you want to lose, either. Or maybe just lose one out of three. I think I read about it on the BBC’s iPad service. If you remember where it was, please comment. And if you beat it with XL playing hard, please say how. Your game is captured in the VBE’s Immediate Window.
Provided as a public service to Dick’s readers. :roll:
…mrt
©¿©¬