Welcome to MapForums!

Register, sign in, or use Facebook Connect above to join in and participate in the forum.

When you are signed in, this message, the ads in this row, and the red-underlined link ads all go away.

Subscribe to receive our newsletter.
Subscribe Unsubscribe
Results 1 to 4 of 4

Developing the State Identification Game with MapPoint and Excel

This is a discussion on Developing the State Identification Game with MapPoint and Excel within the MP2K Magazine Articles forums, part of the Map Forums category; First, the compiled State Identification Game can be downloaded here -- State Identification Game Using MapPoint - Can You Do ...

  1. #1
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,094
    Blog Entries
    4

    Developing the State Identification Game with MapPoint and Excel

    First, the compiled State Identification Game can be downloaded here --
    State Identification Game Using MapPoint - Can You Do It? - Blogs - MapPoint Forums

    Second, discussion about setting up the game including the spreadsheet available for download was published in a previous article here --
    Setting up the State Identification Game

    Since the publication and release of the game in late 2012, the State Identification Game quickly became the most popular download of all time 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

    Rather than keep you in suspense and bury the download link at the bottom of the article, here is the game uncompiled, with full source code, as an Excel macro-enabled (.xlsm) file --
    http://www.MapForums.com/State_Indentifier_Game.zip

    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.

    Code:
    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.

    Code:
    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 Game Setup article.

    Setting up the Rounds

    The PlayGame subroutine sets the initial variables and calls SetupRound.

    Code:
    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.

    Code:
    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.

    Code:
    Private Sub cmd1_Click()
      TallyAnswer (1)
    End Sub
    
    Private Sub cmd2_Click()
      TallyAnswer (2)
    End Sub
    
    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 <= 10 Then
        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 and hack away and share your enhancements with the community by posting below.

    Have fun and Happy Mapping!
    Last edited by Eric Frost; 01-07-2013 at 12:59 PM.
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

  2. #2
    TomSawyer is offline Junior Member White Belt
    Join Date
    Jan 2013
    Posts
    2

    Re: Developing the State Identification Game with MapPoint and Excel

    This is cool, thanks!

    I played with this just now for about 30 minutes and quickly mastered it, but was surprised how many I got wrong in the beginning.

    I love the way you used the Excel form to act as a front-end for MapPoint, I never knew MapPoint could be controlled so easily and effectively.

  3. #3
    TinTin is offline Junior Member White Belt
    Join Date
    Jan 2013
    Posts
    2

    Re: Developing the State Identification Game with MapPoint and Excel

    Has anyone mentioned trying to get this to work for country?

    I might try to do it.

    Any pointers appreciated.

  4. #4
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,094
    Blog Entries
    4

    Re: Developing the State Identification Game with MapPoint and Excel

    Are you a developer wanting to modify this yourself or what country or countries would you like to see implemented? (Or are you just in the process of posting several dumb posts and add a spam sig later? lol)

    Eric
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Setting up the State Identification Game
    By Eric Frost in forum MP2K Magazine Articles
    Replies: 0
    Last Post: 01-07-2013, 10:51 AM
  2. State Identification Game Using MapPoint & Excel
    By Eric Frost in forum Development
    Replies: 0
    Last Post: 11-01-2012, 05:51 PM
  3. State Identification Game Using MapPoint
    By Eric Frost in forum News and Announcements
    Replies: 0
    Last Post: 10-05-2012, 09:46 AM
  4. Developing with MapPoint and Excel - Getting Started
    By Eric Frost in forum MP2K Magazine Articles
    Replies: 19
    Last Post: 11-22-2010, 10:18 PM
  5. Shall We Play A Game? UMapper’s Virtual Earth Game
    By VirtualEarth MSDN Blog in forum Bing Maps Blogs & Tweets
    Replies: 0
    Last Post: 06-02-2009, 02:30 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


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