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 8 of 8

MapPoint object in Excel 2010 VBA not plotting radius

This is a discussion on MapPoint object in Excel 2010 VBA not plotting radius within the MapPoint Add-ins and Related Products forums, part of the Map Forums category; I am having a heck of a time getting Excel 2010 to plot a radius shape into the MapPoint object. ...

  1. #1
    jonmcrawfords is offline Junior Member White Belt
    Join Date
    Oct 2013
    Posts
    3

    MapPoint object in Excel 2010 VBA not plotting radius

    I am having a heck of a time getting Excel 2010 to plot a radius shape into the MapPoint object. I have run across a glut of examples using the following syntax ( this from Microsoft's help):
    oMap.Shapes.AddShape(geoShapeRadius, locVolcano, someNum, someNum2).Name = "Zone"

    which promptly fails the sub, saying the parameter is incorrect.

    I can plot all my pushpins, just can't get the radius to work? Any ideas?
    Here's my sub:

    Code:
    Private Sub CommandButton1_Click()
     Dim oApp As Object 'MapPoint.Application
        Dim oMap As Object 'MapPoint.Map
        Dim oPush As Object 'MapPoint.Pushpin
        Dim oLoc As Object 'MapPoint.Location
        Dim myAdd, myCity, myState, myZip, myFind, myRow, endRow As Integer
        
        
        'Check to see if the map already exists.  If so, delete it from the spreadsheet
        'maxshapes = ActiveSheet.Shapes.Count
        'For x = 2 To maxshapes
           'ActiveSheet.Shapes(x).Delete
        'Next x
            
                
        Set oApp = CreateObject("Mappoint.Application")
        Set oMap = oApp.NewMap      'Create the new map
        oApp.Units = geoMiles
        
        'Find the last cell in the range with data in it
            endRow = Range("B5").End(xlDown).Row
            myRow = 5
        'process each row onto the map
        For x = 5 To endRow
            myRow = x        'Get the currently selected row
            myAdd = Cells(x, 2)     'Use the first column as the address info
            myCity = Cells(x, 3)        'Use the second column as the city info
            myState = Cells(x, 4)       'Use the third column as the state info
            myZip = Cells(x, 5)     'Use the fourth column as the zip info
        
            
            'The first column on the spreadsheet is a blank used for the confirmation info below
            Cells(myRow, 1).Value = ""
            Cells(myRow, 1).Interior.ColorIndex = xlColorIndexNone  'Set the background
            
            'If Len(myAdd) < 2 Then Exit Sub 'If the address is less than 2 characters, exit
            myFind = myAdd & "," & myCity & "," & myState   'Create the address string
            Set oLoc = oMap.Find(myFind)    'Find the new address
            
            If Not oLoc Is Nothing Then     'If found, start processing
                Cells(myRow, 1).Value = "Found!"    'Change the first column to indicate found
                Cells(myRow, 1).Interior.ColorIndex = 4 'Change the color to Green
                
                Set oPush = oMap.AddPushpin(oLoc)   'Create a pushpin on the map
                oPush.Symbol = oMap.Symbols(21)
                oPush.Goto
                oPush.Highlight = False
    
    '*****************THIS LINE IS MY PROBLEM CHILD ***********************
                oMap.Shapes.AddShape(geoShapeRadius, oLoc, 50, 30).Name = "Radius"
     
    
                'Hide the picture toolbar
                Application.CommandBars("Picture").Visible = False
                Cells(myRow, 2).Select      'Reselect our address field
            Else
                Cells(myRow, 1).Value = "Not Found!"    'Didn't find the address
                Cells(myRow, 1).Interior.ColorIndex = 3 'Set color to red
            End If
           
        Next x
        
        
        oApp.ActiveMap.Saved = True
        
        oMap.DataSets(1).ZoomTo
        oMap.CopyMap            'Copy the map to the clipboard
        Cells(5, 7).Select      'Select the seventh column of this row
        ActiveSheet.Paste       'Paste the map into the spreadsheet
        
        Set oLoc = Nothing
        Set oPush = Nothing
        Set oMap = Nothing
        Set oApp = Nothing
    End Sub

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

    Re: MapPoint object in Excel 2010 VBA not plotting radius

    Hi Jon,

    Welcome to the Forums and I hope we can help!!

    I wouldn't try creating the shape and setting the name at the same time, maybe try something like this?

    Code:
    Dim SHP As MapPoint.Shape
    Set SHP = oMap.Shapes.AddShape(geoShapeRadius, oLoc, 50, 30)
    SHP.Name = "Radius"
    Also, you will need to Add a Reference in your project for MapPoint in order for it to know the value of the geoShapeRadius constant, otherwise it might be interpreting it as 0 and that might be causing that error.

    If you look up the "GeoAutoShapeType values" in the help file, it looks like the geoShapeRadius constant has a value of 1009 so you could try swapping that in there like this:

    Code:
    Dim SHP As MapPoint.Shape
    Set SHP = oMap.Shapes.AddShape(1009, oLoc, 50, 30) 'geoShapeRadius
    SHP.Name = "Radius"
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

  3. #3
    jonmcrawfords is offline Junior Member White Belt
    Join Date
    Oct 2013
    Posts
    3

    Re: MapPoint object in Excel 2010 VBA not plotting radius

    Thanks, but that throws an error saying that the MapPoint.Shape is a user-defined type that has not been defined.

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

    Re: MapPoint object in Excel 2010 VBA not plotting radius

    You can either

    1. add the reference for MapPoint by going to Tools References (or Project References in VB6)

    2. just declare it as an Object like you've done for the other MapPoint objects --

    Code:
    Dim SHP As Object 'MapPoint.Shape
    Hope this helps!
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

  5. #5
    jonmcrawfords is offline Junior Member White Belt
    Join Date
    Oct 2013
    Posts
    3

    Re: MapPoint object in Excel 2010 VBA not plotting radius

    It's almost like you've done this before! Worked like a charm, thanks! (knew I was missing something basic)

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

    Re: MapPoint object in Excel 2010 VBA not plotting radius

    Haha, glad to help!

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

  7. #7
    Winwaed's Avatar
    Winwaed is offline Mapping-Tools.com Black Belt
    Join Date
    Feb 2004
    Location
    Irving,Texas
    Posts
    1,859
    Blog Entries
    60

    Re: MapPoint object in Excel 2010 VBA not plotting radius

    As an aside, Eric's original solution is generally the better and less likely to cause problems. You're using "late binding" - every MapPoint object is defined as an object. It is easy to get them mixed up - eg. treat a Recordset as aShape or something weird like that.

    The other way to avoid the error would be to set the project references, to point to the MapPoint COM model. Then you should be able to explicitly use the object names.
    Winwaed Software Technology LLC
    http://www.winwaed.com
    See http://www.mapping-tools.com for MapPoint Tools

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

    Re: MapPoint object in Excel 2010 VBA not plotting radius

    I definitely think having the Reference is nice while programming as you get the context-sensitive help.

    But for deployment if many people are going to be using it, maybe it's better to remove the reference and Dim everything as Objects?

    With MapPoint, I think I've noticed this is useful if you want both North America and European MapPoint users to both be able to use the same Excel macro.

    How does it work with COM Add-ins?

    Also, are there any performance implications?

    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. Replies: 1
    Last Post: 03-16-2012, 08:33 AM
  2. MapPoint North America 2010 not plotting pushpin correctly
    By mrodriguez in forum MapPoint Desktop Discussion
    Replies: 5
    Last Post: 02-15-2012, 11:54 AM
  3. "FindNearby" in MapPoint 2010 Object
    By thowle in forum MapPoint Desktop Discussion
    Replies: 3
    Last Post: 01-18-2011, 03:47 AM
  4. Excel Macro to MapPoint Object Help
    By arrancollins in forum MapPoint Desktop Discussion
    Replies: 4
    Last Post: 01-28-2010, 05:06 AM
  5. Trouble adding a MapPoint 2010 COM object
    By BobFromBoston in forum Development
    Replies: 2
    Last Post: 11-12-2009, 03:56 AM

Tags for this Thread

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 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127