Creating the State Identification Sport With Microsoft MapPoint and Excel VBA

 For the reason that publication and launch in late 2012, the State Identification Sport turned one of the fashionable downloads on MapForums.

The State Identification Sport was developed utilizing Excel VBA and MapPoint. The MapPoint model have to be at the least 2010 because it was on this model that you simply had been first capable of manipulate map layers and switch off labels. Excel variations examined embody 2007 and 2010, however I do not see a cause why it mustn't work with earlier variations of Excel.

For the reason that launched of the compiled sport, a number of builders have requested, and I've beforehand shared the code privately, however now for the primary time on this article, we are actually making the total supply code for the sport out there for public obtain.

Launching the Sport

The obtain hyperlink with the sport uncompiled and with full supply code as an Excel macro-enabled (.xlsm) file is on the backside of this text.

When opened, the macro instantly kicks off the sport through the use of the Workbook_Open methodology of the ThisWorkbook object. That is set as proven within the screenshot beneath.

That 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.Present
Finish Sub

Personal Sub InstantiateMapPoint()

Set APP = CreateObject("MapPoint.Software")

APP.Seen = True

APP.WindowState = geoWindowStateMaximize

Set MAP = APP.ActiveMap

APP.PaneState = geoPaneNone

APP.ItineraryVisible = False

Dim instrument As Object

'particularly want to cover the Location and Scale toolbar or it principally provides you the reply!

For Every instrument In APP.Toolbars

instrument.Seen = False

Subsequent
Finish Sub

The entire remaining code and sport logic are contained within the type object.

Opening The frmStateIdentifier UserForm

When first opened, the shape declares a number of module-level variables, turns off the MapPoint labels, and begins the sport.

 

Personal s(3) As Integer

Personal i, Right, Reply, Spherical As Integer

Personal stateTXT As String

Personal resultTXT As String

Personal wks As Excel.Worksheet

Personal Sub UserForm_Activate()

Set wks = Excel.ActiveWorkbook.Sheets("US States")

Software.WindowState = xlNormal

Software.Top = 50

Software.Width = 50

TurnOffAllLabels

PlayGame
Finish Sub

You may see that the code additionally shrinks the Excel Software with a view to hold it out of the best way. One of many first challenges in utilizing Excel and a separate occasion of MapPoint, was to get Excel out of the best way, and let the shape with the buttons float over the map. That is completed with the WindowState, Top, and Width properties of the Software object.

The TurnOffAllLabels code was mentioned within the MapPoint Sport Setup article.

Establishing the Rounds

The PlayGame subroutine units the preliminary variables and calls SetupRound.

Personal Sub PlayGame()

cmd1.Caption = ""

cmd2.Caption = ""

cmd3.Caption = ""

cmd1.Seen = True

cmd2.Seen = True

cmd3.Seen = True

Spherical = 1

SetupRound
Finish Sub

SetupRound randomly determines three candidate states, after which randomly picks amongst these three because the state to point out on the map. All three states are present as button captions.

Personal Sub SetupRound()

lblStatus.Caption = "Spherical: " & Spherical

Dim loc As Object

Randomize

'Do Whereas... Loop's guarantee distinctive states are chosen

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

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

Do Whereas s(1) = s(2)

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

Loop

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

Do Whereas 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 decide one of many three states

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

stateTXT = wks.Cells(s(Reply), 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)

Finish If

loc.Choose

loc.Goto

MAP.Altitude = MAP.Altitude * 1.4

frmStateIdentifier.cmd1.SetFocus

Finish Sub

If the stateTXT is New York or Washington, the Outcomes assortment really has the Metropolis first, and the State second, so the second merchandise within the assortment is chosen. I feel you would possibly be capable to discard this little bit of nastiness through the use of the FindAddressResults methodology and explicitly passing within the State within the Area parameter.

Now the sport merely waits for one of many buttons to be pressed.

Tallying the Solutions

When one of many buttons is pressed, the respective methodology is known as, and the worth 1, 2, or Three is handed to TallyAnswer.

Personal Sub cmd3_Click()

TallyAnswer (3)
Finish Sub

Personal Sub TallyAnswer(ans As Integer)

Spherical = Spherical + 1

If ans = Reply Then

Right = Right + 1

Else

resultTXT = resultTXT + "You picked " & wks.Cells(s(ans), 1) & ". The proper reply was " & wks.Cells(s(Reply), 1) & "." & vbNewLine

Finish If

If Spherical <= 10 Then

SetupRound

Else

MsgBox (Right & " out of 10 Right!") & vbNewLine & vbNewLine & resultTXT,, "Outcomes"

'reset variables

resultTXT = ""

Right = 0

cmd1.Seen = False

cmd2.Seen = False

cmd3.Seen = False

frmStateIdentifier.Disguise

MAP.Saved = True

APP.Stop

Software.Dad or mum.WindowState = xlMaximized

Finish If
Finish Sub

If the proper state was chosen, the rating, as saved by the variable Right, is incremented and if the Spherical remains to be 10 or much less, SetupRound units up the following spherical. If incorrect, the string resultTXT shops the end result to show on the finish of the sport.

After the final spherical, a message field pops up with the end result, and MapPoint is closed.

What's Subsequent?

Up to now we now have Excel VBA instantiating and manipulating MapPoint, a easy Excel type with buttons, and fundamental logic for organising sport rounds, tallying the solutions, and reporting the outcomes.

So far as what's subsequent, I am going to go away this in different developer's succesful arms.

Some apparent instructions to go could be to undertake this sport to work with International locations or presumably Cities world wide.

It could be good when you might Play Once more with out having to shut the sport and re-opening it.

A MapPoint management may very well be used immediately on the Excel UserForm to make a extra seamless consumer expertise (slightly than having the shape float above MapPoint).

Additionally, it might be fairly cool if the outcomes had been timed and posted to a easy leaderboard on an online web page so you possibly can, for example, make it a problem to attempt to be the quickest to get all 10 right.

Talking of the online, you would possibly think about simply utilizing MapPoint to create the map photographs, and implement the sport completely as an online app.