Automating MapPoint

EEE#21: Excel User Defined Functions That Incorporate MapPoint Functionality

By David Hager

–Note: Excel 2003 and MapPoint 2004 were used in the development of this technique and its use with other versions has not been tested.–

The integration that Microsoft provides between Excel and MapPoint for importing and exporting data provides a powerful method for visual data analysis. As it turns out, there is another way to obtain information from MapPoint to Excel that is not as well-known.

User-defined functions (UDF) are used in Excel worksheets is the same way that native Excel functions are used. The VBA code for an Excel UDF is written in a standard module in the Excel VBE and called by entering the function in a formula in a worksheet cell. In order to incorporate MapPoint automation in an Excel UDF, open the Visual Basic Environment (VBE) by using the Alt-F11 keys. Then, under Tools|References check the Microsoft MapPoint 11.0 Object Library (North America).

A simple example of an Excel UDF that incorporates MapPoint functionality is shown below.

Function StraightLineDist(strPoint1, strPoint2)
 
  Dim objApp As New MapPoint.Application
  Set objMap = objApp.ActiveMap
 
  Set objLocate1 = objMap.FindResults(strPoint1).Item(1)
  Set objLocate2 = objMap.FindResults(strPoint2).Item(1)
  StraightLineDist = Application.Round(CStr(objLocate1.DistanceTo(objLocate2)), 5)
 
End Function

It is quite rare to find an example where the VBA code for an Excel UDF successfully instantiates an application. In this case, an instance of MapPoint is created with the statement

Dim objApp As New MapPoint.Application

that allows the Excel UDF access to a valuable source of location information.

The FindResults method works in the same way that the Place/Data tab in the dialog box opened from Edit|Find of the MapPoint main menu does. The Item(1) property returns the best choice based on the available information. As an example, type the following in an Excel worksheet.

A1: Beaumont, Tx
A2: Houston, Tx
A3: =StraightLineDist(A1, A2)

The result returned in cell A3 is 77.11092, which is the number of miles as the crow flies between Beaumont and Houston Texas as calculated by the DistanceTo method.

In order to improve performance of the StraightLineDist function, the statement

Application.Volatile False

can be used in the code to limit function calls to only those times when the input data is changed. It might be also advantageous to set the MapPoint objects to nothing in order to remove them from memory.

The next example returns the distance for a multiple point route and contains an element that is perhaps unique among UDF s.

Function MPRouteDist(iMPType As Integer, ParamArray WPoints())
 
  Dim objApp As New MapPoint.Application
  Set objMap = objApp.ActiveMap
 
  With objMap.ActiveRoute
    For Each wpoint In WPoints
        .Waypoints.Add objMap.FindResults(wpoint).Item(1)
    Next
    .Waypoints.Item(1).SegmentPreferences = iMPType
    .Calculate
     MPRouteDist = Application.Round(CStr(.Distance), 5)
  End With
 
    objMap.Saved = True
End Function

The iMPType variable is the type of route to be returned:

0 = Travel using the quickest route
1 = Travel using the shortest route
2 = Travel on preferred roads as set in the Preferred Roads dialog box

The ParamArray WPoints() statement allows for an arbitrary number of waypoint function arguments along the route.

By entering the following formula, the distance for the quickest round trip route from Houston is returned (882.89018 miles).

=MPRouteDist(0,A1, A2,A3,A4,A1)

where:

A1: Houston, Tx
A2: Dallas, Tx
A3: San Antonio, Tx
A4: Corpus Christi, Tx

This function actually makes changes on the active MapPoint map (not visible) and a prompt appears asking to save those changes unless the statement

objMap.Saved = True

is included in the code. This writer does not know of another example where an Excel UDF modifies another application. This unusual behavior serendipitously provides a simple way to generate route maps from Excel. If the following formula is entered without the objMap.Saved = True statement in the code, the route map from Houston to Dallas is generated and can be saved. Thus, generation of route maps from MapPoint is now as easy as entering an address in a worksheet cell.

=MPRouteDist(0,A1, A2)

In summary, Excel UDFs provide easy access to the rich store of information in MapPoint and these examples just scratch the surface of what is possible. For example, I have made Excel UDFs that draw geometric shapes around MapPoint locations. This and other challenges are left to the reader.

Posted in Uncategorized

10 thoughts on “Automating MapPoint

  1. Hi David!

    Nice to hear from you again, long time no see!

    I like this code. I just tried a similar thing with Word and it seems you can do things to Word from a UDF too, without any problem.

    I would do one thing a bit different though. Your current example opens a new Mappoint session each time, I would do it like I did in my Word sample:

    Option Explicit

    Public oWdObj As Word.Application

    Function WriteResultToWord(stest As String)
    If oWdObj Is Nothing Then
    Set oWdObj = New Word.Application
    oWdObj.Visible = True
    oWdObj.Documents.Add
    End If
    oWdObj.ActiveDocument.Paragraphs.First.Range.InsertAfter “, ” & stest

    End Function

    Of course this needs some code in the Workbook_beforeClose event to close Word and set the oWdObj to nothing, but you get the idea.

  2. Hi,

    Interesting approach and it reminds me about how we work with MS VB 6.0.

    I prefer to split up the declaration part from the instantiating part when working with other apps, i e

    Dim objApp As MapPoint.Application

    Set objApp = New MapPoint.Application

    In that way we can compare the variable with Nothing and we don’t confuse VBA when to instantiate the variable(s).

    One of the nice thing with VB.NET is that the line

    Dim objApp As New MapPoint.Application

    is 100 % valid :)

    Of course this will be confusing when switching between the development-platforms but that’s another story.

    Kind regards,
    Dennis

  3. World maps
    ——–
    Here is something that I owe many thanks to Andy Pope.
    I have used his code to make world maps in ppt.
    http://www.andypope.info/ngs/ng12.htm

    Next step will be to include cities. Something that Fernando Cinquegrani has worked on. If I only could combine the two. What an interesting presentation that would be:
    http://www.prodomosua.it/zips/helloworld.xls

    Ola Sandström

    Use this: http://babelfish.altavista.com/
    To translate this: http://www.prodomosua.it/ppage02.html

  4. Is it possible to use simialar UDF with excel to map an entire column of zip code to MP2004?

  5. Very nice and elegant. I have been messing with Mappoint thru vb functions but this works very will and is simplier than what i had been doing. Thanks…

    AWE

  6. Do you know of a function or some way that VB.Net can be used to extract coordinates (as decimal degrees) from pushpins or waypoints that are mapped in MapPoint?

    Thanks,

    Carlo

  7. Hi Expert Guyz,
    I’m using MapPoint2004 in a C# application. I’ve to let user draw radius circles on the map and then work out which clients exist within that circle. The circle should be zoom-aware. At present I draw circle successfully but it is drawn a bit back of my pointer and secondly it is not zoom-adjusted. May I be guided by anyone in this direction, I’d be highly obliged.
    Regards

  8. Thanks for the UDF. It helped understand using Mappoint with Excel.
    I wanted to find a way to speed up the UDF and in watching the Trace I noticed that the program spent mnost of its time creating the object. So I experimented and found this solution of reusing the object that had already been created.
    So far it seems to work fine, but I have to wonder if there might be something that I am missing that will eventually cause a problem. Does anybody know?

    Function MPRouteDist(iMPType As Integer, ParamArray WPoints())

    On Error Resume Next
    ‘Reusing the object speeds up the UDF. However, we have to clear the route
    ‘so the waypoints don’t keep getting added to the route.
    ‘The Clear is done at the end of the UDF.
    Set objApp = GetObject(, “MapPoint.Application”)
    If Err.Number 0 Then
    Set objApp = CreateObject(“MapPoint.Application”)
    End If
    On Error GoTo LEH

    Set objmap = objApp.ActiveMap
    Set objRoute = objmap.ActiveRoute

    With objmap.ActiveRoute
    For Each wpoint In WPoints
    .Waypoints.Add objmap.FindResults(wpoint).item(1)
    Next
    .Waypoints.item(1).SegmentPreferences = iMPType
    .Calculate
    MPRouteDist = Application.Round(.Distance, 1)
    End With
    LEH:
    ‘Clear the route so we can reuse the object.
    ‘Otherwise the waypoints will be added to the activeroute, making the route longer and longer.
    objmap.ActiveRoute.Clear
    objmap.Saved = True

    End Function


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.