Developing the State Identification Game With Microsoft MapPoint and Excel VBA

blog-admin 0

Since the publication and launch in late 2012, the State Identification UFABET Game have become one of the maximum popular downloads on MapForums.

The State Identification Game became advanced using Excel VBA and MapPoint. The MapPoint version ought to be as a minimum 2010 as it become on this version that you have been first capable of manage map layers and flip off labels. Excel versions tested consist of 2007 and 2010, but I don’t see a reason why it must now not work with earlier versions of Excel.

Since the released of the compiled sport, numerous builders have requested, and I actually have previously shared the code privately, however now for the primary time in this newsletter, we are now making the overall supply code for the game to be had for public download.

Launching the Game

The download hyperlink with the game uncompiled and with full supply code as an Excel macro-enabled (.Xlsm) report is at the bottom of this newsletter.

When opened, the macro right away kicks off the game by using the usage of the Workbook_Open technique of the ThisWorkbook object. This is ready as shown within the screenshot under.

This is the preliminary code which opens (instantiates) MapPoint, units the toolbars, and triggers the shape.

 

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

‘mainly need to hide the Location and Scale toolbar or it basically gives you the answer!

For Each device In APP.Toolbars

tool.Visible = False

Next

End Sub

All of the ultimate code and recreation good judgment are contained in the shape object.

Opening The frmStateIdentifier UserForm

When first opened, the form pronounces numerous module-degree variables, turns off the MapPoint labels, and starts 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 maintain it out of the manner. One of the primary demanding situations in using Excel and a separate example of MapPoint, changed into to get Excel out of the way, and let the form with the buttons glide over the map. This is done with the WindowState, Height, and Width homes of the Application item.

The TurnOffAllLabels code became discussed inside the MapPoint Game Setup article.

Setting up the Rounds

The PlayGame subroutine units the preliminary 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 alternatives among the ones 3 as the nation to expose 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 make certain 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

Loop

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

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

s(three) = 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 out 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)

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 sincerely has the City first, and the State second, so the second one item inside the series is chosen. I think you is probably capable of discard this little bit of nastiness by using the FindAddressResults method and explicitly passing within the State in the Region parameter.

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

Tallying the Answers

When one of the buttons is pressed, the respective approach is called, and the price 1, 2, or three is handed 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 turned into ” & wks.Cells(s(Answer), 1) & “.” & vbNewLine

End If

If Round <= 10 Then

SetupRound

Else

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

‘reset variables

resultTXT = “”

Correct = zero

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 perfect kingdom changed into selected, the score, as stored by means of the variable Correct, is incremented and if the Round continues to be 10 or much less, SetupRound sets up the subsequent round. If wrong, the string resultTXT stores the result to show on the give up of the game.

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

What’s Next?

Thus far we’ve Excel VBA instantiating and manipulating MapPoint, a simple Excel form with buttons, and basic logic for setting up sport rounds, tallying the solutions, and reporting the results.

As far as what’s subsequent, I’ll go away this in other developer’s succesful hands.

Some obvious directions to move could be to adopt this game to work with Countries or probably Cities around the world.

It could be fine if you may Play Again while not having to close the sport and re-beginning it.

A MapPoint manipulate will be used immediately at the Excel UserForm to make a more seamless user enjoy (as opposed to having the shape waft above MapPoint).

Also, it would be pretty cool if the results were timed and posted to a easy leaderboard on an internet web page so you ought to, as an example, make it a assignment to try to be the fastest to get all 10 accurate.

Speaking of the internet, you would possibly consider simply the usage of MapPoint to create the map images, and put into effect the game entirely as a web app.

What do you believe you studied? Download the code for the MapPoint Game and hack away and share your upgrades with the network by using posting underneath.

Have a laugh and Happy Mapping!

Leave a Reply

Your email address will not be published. Required fields are marked *