spacer1
spacer2 1_1 1_2
2_1
 Subscribe
 The MP2K Update!
 
 
 
 Magazine
Front Cover
What's New
Articles
News
Sample Data
Gallery
Advertise
About
 Features
MapPoint 2013
Press Releases
MapPoint Forums
Companies
Link to MP2Kmag
Wish List
MapPoint Trial
Authors
 Earlier Content
Past News Items
Past What's New Announcements
 Sponsors
 Order

MapPoint 2013

Programming MapPoint in .NET

MapPoint Book

  Spatial Community
SVG Tutorials
MapPoint

Map Visitors

  ARTICLES  


Excel User-Defined Functions and MapPoint

David Hager demonstrates how to incorporate MapPoint functionality into a user-defined Excel function

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.

Discuss this story in the forum.

Author: David Hager
Email: dhExcel[AT]aol.com
URL: http://www.exxonmobilchemical.com/
David Hager currently works as a chemist with ExxonMobil. He was first recognized by Microsoft as an Excel MVP in 1995. He is a co-author of Excel Expert Solutions (QUE) and wrote a newsletter for Excel experts which is hosted on John Walkenbach’s web site (www.j-walk.com). His most recent creation is an Excel-driven tropical storm plotting application that contains historical storm data which outputs in MapPoint. You can contact him at dhExcel[AT]aol.com.



Google
 
MP2Kmag Internet


 Recent Discussion
-AutoColor
-SolidThinking Inspire v2016.1.5559 x64-BEAN (17/8)
-Auto FX PhotoGraphic Edges Ultimate Bundle Gen2 v9.6.0 (17/8)
-Mentor Graphics FloEFD 14.2-SSQ (17/8)
-Dlubal RFEM 5.06 (x64) Multilanguage (17/8)
-Bluebeam Revu eXtreme 2018.1 v18.x Multilingual (17/8)
-Autodesk Maya 2018.4 (17/8)
-Adobe Bridge CC 2018 (v8.1) x86-x64 Multilingual Update 2 (17/8)
-Simufact.Welding 4.0.1 (17/8)
-ANSYS Discovery Enterprise 19.1.1 (17/8)
-Aurora SPARK3D v2018
-ANSYS Electromagnetics Suite 17.1 (17/8)
-Solidthinking Inspire v2018.3.0.10526 Multilingual (x64) (17/8)
-Adobe Acrobat Pro DC 2018.011.20058 Multilingual (17/8)
-ANSYS Electromagnetics Suite 17.0 (x64) (17/8)
-GibbsCAM 12.0.29.0 (x64) Multilingual (17/8)
-Autodesk Autocad Mechanical 2019.1 by m0nkrus (17/8)
-Adobe Bridge CC 2018 v8.1 (x86/x64) Update 2 Multilingual (17/8)
-Autodesk AutoCAD Electrical 2019.1 (17/8)
-DS V5R22 CATIA-DELMIA-ENOVIA last hotfixes for all SP (17/8)
-VMware Fusion Pro 10.1.3 Build 9472307 (17/8)
-MAGIX Fotostory easy 2.0.0.35 Germany + English (17/8)
-Ds Simulia Abaqus v6.14-4 (17/8)
-Paragon Drive Copy 15 Professional 10.1.25.779 (x86/x64) (17/8)
-ARCHICAD 21 Build 3005 with Addons
 Resources
Browse GIS books and periodicals
Find a MapPoint Partner or Consultant
Real Estate Columbia Homes for Sale by Owner


Want Your Site To Appear Here?

   © 1999-2012 MP2K. Questions and comments to: website@mp2kmag.com
  Microsoft and MapPoint 2002/2004/2006/2009/2010/2011/2013 are either trademarks or registered trademarks of Microsoft.