Since the book and release in past due 2012, the State Identification Game have become one of the most famous downloads on MapForums. Mailboxnationwide.com
The State Identification Game changed into advanced the usage of Excel VBA and MapPoint. The MapPoint version ought to be as a minimum 2010 as it changed into on this version that you were first able to manipulate map layers and turn off labels. Excel versions tested include 2007 and 2010, however I do not see a motive why it ought to no longer work with earlier variations of Excel.
Since the launched of the compiled recreation, several builders have requested, and I have previously shared the code privately, but now for the first time in this text, we are now making the entire supply code for the sport available for public download.
Launching the Game
The download link with the game uncompiled and with complete source code as an Excel macro-enabled (.Xlsm) document is at the lowest of this newsletter.
When opened, the macro straight away kicks off the sport via the use of the Workbook_Open technique of the ThisWorkbook item. This is set as shown within the screenshot below.
This is the preliminary code which opens (instantiates) MapPoint, units the toolbars, and triggers the form.
Public APP As Object
Public MAP As Object
Public Sub StateIdentifier()
Private Sub InstantiateMapPoint()
Set APP = CreateObject(“MapPoint.Application”)
APP.Visible = True
APP.WindowState = geoWindowStateMaximize
Set MAP = APP.ActiveMap
APP.PaneState = geoPaneNone
APP.ItineraryVisible = False
Dim device As Object
‘mainly need to cover the Location and Scale toolbar or it essentially offers you the solution!
For Each tool In APP.Toolbars
tool.Visible = False
All of the last code and recreation logic are contained within the form item.
Opening The frmStateIdentifier UserForm
When first opened, the shape publicizes numerous module-degree variables, turns off the MapPoint labels, and begins the game.
Private s(3) As Integer
Private i, Correct, Answer, Round As Integer
Private stateTXT As String
Private resultTXT As String
Private wks As Excel.Worksheet
Private Sub UserForm_Activate()
Set wks = Excel.ActiveWorkbook.Sheets(“US States”)
Application.WindowState = xlNormal
Application.Height = 50
Application.Width = 50
You can see that the code also shrinks the Excel Application in order to keep it out of the manner. One of the primary challenges in the usage of Excel and a separate example of MapPoint, turned into to get Excel out of the manner, and allow the form with the buttons flow over the map. This is completed with the WindowState, Height, and Width homes of the Application item.
The TurnOffAllLabels code turned into discussed within the MapPoint Game Setup article.
Setting up the Rounds
The PlayGame subroutine units the initial variables and calls SetupRound.
Private Sub PlayGame()
cmd1.Caption = “”
cmd2.Caption = “”
cmd3.Caption = “”
cmd1.Visible = True
cmd2.Visible = True
cmd3.Visible = True
Round = 1
SetupRound randomly determines three candidate states, after which randomly choices among those three as the country to reveal on the map. All 3 states are show as button captions.
Private Sub SetupRound()
lblStatus.Caption = “Round: ” & Round
Dim loc As Object
‘Do While… Loop’s ensure specific states are chosen
s(1) = Int(Rnd(Time) * 50) + 2
s(2) = Int(Rnd(Time) * 50) + 2
Do While s(1) = s(2)
s(2) = Int(Rnd(Time) * 50) + 2
s(3) = Int(Rnd(Time) * 50) + 2
Do While s(three) = s(1) Or s(three) = s(2)
s(3) = Int(Rnd(Time) * 50) + 2
cmd1.Caption = wks.Cells(s(1), 1)
cmd2.Caption = wks.Cells(s(2), 1)
cmd3.Caption = wks.Cells(s(3), 1)
‘now select one of the three states
Answer = Int(Rnd(Time) * three) + 1
stateTXT = wks.Cells(s(Answer), 1)
If stateTXT <> “New York” And stateTXT <> “Washington” Then
Set loc = MAP.FindPlaceResults(stateTXT & “, United States”)(1)
Set loc = MAP.FindPlaceResults(stateTXT & “, United States”)(2)
MAP.Altitude = MAP.Altitude * 1.Four
If the stateTXT is New York or Washington, the Results collection surely has the City first, and the State second, so the second item inside the series is selected. I think you might be capable of discard this little bit of nastiness by the use of the FindAddressResults method and explicitly passing within the State inside the Region parameter.
Now the sport definitely waits for one of the buttons to be pressed.
Tallying the Answers
When one of the buttons is pressed, the respective approach is known as, and the cost 1, 2, or three is passed to TallyAnswer.
Private Sub cmd3_Click()
Private Sub TallyAnswer(ans As Integer)
Round = Round + 1
If ans = Answer Then
Correct = Correct + 1
resultTXT = resultTXT + “You picked ” & wks.Cells(s(ans), 1) & “. The accurate solution was ” & wks.Cells(s(Answer), 1) & “.” & vbNewLine
If Round <= 10 Then
MsgBox (Correct & ” out of 10 Correct!”) & vbNewLine & vbNewLine & resultTXT,, “Results”
resultTXT = “”
Correct = 0
cmd1.Visible = False
cmd2.Visible = False
cmd3.Visible = False
MAP.Saved = True
Application.Parent.WindowState = xlMaximized
If the perfect nation became chosen, the score, as stored through the variable Correct, is incremented and if the Round is still 10 or less, SetupRound sets up the next round. If incorrect, the string resultTXT stores the end result to display on the end of the game.
After the last spherical, a message field pops up with the result, and MapPoint is closed.
Thus far we’ve Excel VBA instantiating and manipulating MapPoint, a simple Excel form with buttons, and primary common sense for putting in sport rounds, tallying the answers, and reporting the results.
As far as what is next, I’ll go away this in different developer’s capable arms.
Some apparent directions to go might be to undertake this recreation to paintings with Countries or probable Cities around the world.
It would be first-class if you could Play Again while not having to shut the sport and re-beginning it.
A MapPoint manipulate could be used directly at the Excel UserForm to make a extra seamless consumer experience (in place of having the shape glide above MapPoint).
Also, it would be pretty cool if the effects have been timed and posted to a simple leaderboard on an internet web page so that you could, as an example, make it a task to try to be the fastest to get all 10 correct.
Speaking of the net, you would possibly don’t forget just the usage of MapPoint to create the map pictures, and put into effect the sport entirely as an internet app.