View Full Version : MapPoint object in Excel 2010 VBA not plotting radius

10-04-2013, 02:37 PM
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:

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
'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.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
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.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

Eric Frost
10-06-2013, 09:03 AM
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?

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:

Dim SHP As MapPoint.Shape
Set SHP = oMap.Shapes.AddShape(1009, oLoc, 50, 30) 'geoShapeRadius
SHP.Name = "Radius"

10-08-2013, 08:13 AM
Thanks, but that throws an error saying that the MapPoint.Shape is a user-defined type that has not been defined.

Eric Frost
10-08-2013, 11:58 AM
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 --

Dim SHP As Object 'MapPoint.Shape

Hope this helps!

10-08-2013, 12:30 PM
It's almost like you've done this before! Worked like a charm, thanks! (knew I was missing something basic)

Eric Frost
10-08-2013, 04:46 PM
Haha, glad to help!


10-09-2013, 08:03 AM
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.

Eric Frost
10-09-2013, 03:04 PM
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?