Developing the State Identification Game With Microsoft MapPoint and Excel VBA

Since the publication and release in late 2012, the State Identification Game became one of the most popular downloads on MapForums.

The State Identification Game was developed using Excel VBA and MapPoint. The MapPoint version must be at least 2010 as it was in this version that you were first able to manipulate map layers and turn off labels. Excel versions tested include 2007 and 2010, but I don’t see a reason why it should not work with earlier versions of Excel.

Since the released of the compiled game, several developers have asked, and I have previously shared the code privately, but now for the first time in this article, we are now making the full source code for the game available for public download.

Launching the Game

The download link with the game uncompiled and with full source code as an Excel macro-enabled (.xlsm) file is at the bottom of this article.

When opened, the macro immediately kicks off the game by using the Workbook_Open method of the ThisWorkbook object. This is set as shown in the screenshot below.

This is the initial code which opens (instantiates) MapPoint, sets the toolbars, and triggers the form.

 

Public APP As Object
Public MAP As Object

Public Sub StateIdentifier()

InstantiateMapPoint

frmStateIdentifier.Show
End Sub

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 tool As Object

'in particular need to hide the Location and Scale toolbar or it basically gives you the answer!

For Each tool In APP.Toolbars

tool.Visible = False

Next
End Sub

All of the remaining code and game logic are contained in the form object.

Opening The frmStateIdentifier UserForm

When first opened, the form declares several module-level 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

TurnOffAllLabels

PlayGame
End Sub

You can see that the code also shrinks the Excel Application in order to keep it out of the way. One of the first challenges in using Excel and a separate instance of MapPoint, was to get Excel out of the way, and let the form with the buttons float over the map. This is accomplished with the WindowState, Height, and Width properties of the Application object.

The TurnOffAllLabels code was discussed in the MapPoint Game Setup article.

Setting up the Rounds

The PlayGame subroutine sets 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
End Sub

SetupRound randomly determines three candidate states, and then randomly picks among those three as the state to show on the map. All three states are show as button captions.


Private Sub SetupRound()

lblStatus.Caption = "Round: " & Round

Dim loc As Object

Randomize

'Do While... Loop's ensure unique 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

Loop

s(3) = Int(Rnd(Time) * 50) + 2

Do While s(3) = s(1) Or s(3) = s(2)

s(3) = Int(Rnd(Time) * 50) + 2

Loop

cmd1.Caption = wks.Cells(s(1), 1)

cmd2.Caption = wks.Cells(s(2), 1)

cmd3.Caption = wks.Cells(s(3), 1)

'now pick one of the three states

Answer = Int(Rnd(Time) * 3) + 1

stateTXT = wks.Cells(s(Answer), 1)

If stateTXT "New York" And stateTXT "Washington" Then

Set loc = MAP.FindPlaceResults(stateTXT & ", United States")(1)

Else

Set loc = MAP.FindPlaceResults(stateTXT & ", United States")(2)

End If

loc.Select

loc.Goto

MAP.Altitude = MAP.Altitude * 1.4

frmStateIdentifier.cmd1.SetFocus

End Sub

If the stateTXT is New York or Washington, the Results collection actually has the City first, and the State second, so the second item in the collection is chosen. I think you might be able to discard this bit of nastiness by using the FindAddressResults method and explicitly passing in the State in the Region parameter.

Now the game simply waits for one of the buttons to be pressed.

Tallying the Answers

When one of the buttons is pressed, the respective method is called, and the value 1, 2, or 3 is passed to TallyAnswer.

Private Sub cmd3_Click()

TallyAnswer (3)
End Sub

Private Sub TallyAnswer(ans As Integer)

Round = Round + 1

If ans = Answer Then

Correct = Correct + 1

Else

resultTXT = resultTXT + "You picked " & wks.Cells(s(ans), 1) & ". The correct answer was " & wks.Cells(s(Answer), 1) & "." & vbNewLine

End If

If Round

SetupRound

Else

MsgBox (Correct & " out of 10 Correct!") & vbNewLine & vbNewLine & resultTXT,, "Results"

'reset variables

resultTXT = ""

Correct = 0

cmd1.Visible = False

cmd2.Visible = False

cmd3.Visible = False

frmStateIdentifier.Hide

MAP.Saved = True

APP.Quit

Application.Parent.WindowState = xlMaximized

End If
End Sub

If the correct state was chosen, the score, as stored by 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 result to display at the end of the game.

After the last round, a message box pops up with the result, and MapPoint is closed.

What’s Next?

Thus far we have Excel VBA instantiating and manipulating MapPoint, a simple Excel form with buttons, and basic logic for setting up game rounds, tallying the answers, and reporting the results.

As far as what’s next, I’ll leave this in other developer’s capable hands.

Some obvious directions to go would be to adopt this game to work with Countries or possibly Cities around the world.

It would be nice if you could Play Again without having to close the game and re-opening it.

A MapPoint control could be used directly on the Excel UserForm to make a more seamless user experience (rather than having the form float above MapPoint).

Also, it would be pretty cool if the results were timed and posted to a simple leaderboard on a web page so you could, for instance, make it a challenge to try to be the fastest to get all 10 correct.

Speaking of the web, you might consider just using MapPoint to create the map images, and implement the game entirely as a web app.

What do you think? Download the code for the MapPoint Game and hack away and share your enhancements with the community by posting below.

Have fun and Happy Mapping!

You May Also Like