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

Excel VBA function speed

This is a discussion on Excel VBA function speed within the Development forums, part of the MapPoint Desktop Discussion category; I'm using Excel XP and MapPoint Europe 2009 in Vista x64 on a quad core PC with 6gig ram. Not ...

  1. #1
    spencerdavis is offline Junior Member White Belt
    Join Date
    Feb 2009
    Posts
    2

    Excel VBA function speed

    I'm using Excel XP and MapPoint Europe 2009 in Vista x64 on a quad core PC with 6gig ram. Not sure if any of that is relevant.

    This is my first attempt at VBA and at MapPoint integration. I need to calculate a combination of around eight thousand routes in a spreadsheet, I have postcodes as column headings and postcodes as row headings.

    I have a function in VBA as follows:-
    Function MPRouteTime(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
    MPRouteTime = Application.Round(CStr(.DrivingTime / geoOneMinute), 5)
    End With

    objMap.Saved = True
    End Function


    I use the function =MPRouteDist(0,C$3,$B4) to give me the distance of the quickest route between the postcodes at C3 and B4. Everything works great except that it takes about two seconds per calculation which is quite a long while to wait for my volume of updates. There are things I can do to limit the amount of updates but I would ideally like to be able to do a refresh in a lot less than three hours.
    Is there any way to speed this up?

  2. #2
    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: Excel VBA function speed

    Route finding is computationally difficult and it is always going to take half a second or a second per route.
    This depends on a lot of factors including the length of the routes.

    Some things to consider:

    - Cache the locations. Finding locations takes time. Not as much as a route, but it adds up.

    - Hide the MapPoint display

    - Consider writing an add-in within MapPoint instead of Excel. This reduces COM compartment time (although you "pay" when it talks to Excel instead, you should be talking to Excel less than you talk to MapPoint).

    - Have you tried a 32 bit OS? MapPoint (and I assume Excel) will be running in 32 bit mode. Does this add an overhead? You do not gain anything from the 64 bit OS or large memory (2GB is plenty in 32 bit for this type of thing)

    - COM hygiene - explicitly dispose/clear objects when not in use. This helps to improve the performance of MapPoint's poor garbage collector. This will take its toll on large batch runs, although I don't think you've reached this point yet.

    - 2 cpus should be fine. MapPoint 2006 & 2009 route finding definitely benefit with a second core. I haven't seen anything to suggest 4 cores will be better unless the machine is doing other work, or you want to write multi-threaded code (I don't think this is possible in an Excel Macro?).


    If you can locate your postcode points first, then you may want to try our MileCharter product: MileCharter: Home Page
    This calculates mileage charts (list-to-list) in Excel.

    We also have a product that can take advantage of a quad core system, called MPMileage: Batch calculate mileages with Microsoft MapPoint but this calculates point-to-point route listings from a database, rather than a many-to-many table.


    Richard
    Winwaed Software Technology LLC
    http://www.winwaed.com
    See http://www.mapping-tools.com for MapPoint Tools

  3. #3
    spencerdavis is offline Junior Member White Belt
    Join Date
    Feb 2009
    Posts
    2

    Re: Excel VBA function speed

    How do I cache the locations? As I have a fixed set of locations I think this will help.
    The PC is my home PC, this work is for a charity and they would rather not buy software, hence my involvement. I'd also rather not install another o/s if possible.
    Mappoint does not visibly load but I can see it appear in the task manager, sometimes two at a time.

    I guess I'll have to start reading up on writing an adding for MapPoint.

    Is it possible to install MapPoint to a ramdisk, e.g. a virtual disk drive held in RAM, I have spare so if route finding is an I/O issue, this may help?

    Thanks

    Spencer

  4. #4
    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: Excel VBA function speed

    You could try a ramdisk, but I suspect it will only speed the start up..


    The FindResults(wp).Item(1) (quoting from memory there, but you get idea) is performing a geocoding function - it is looking up a place (postcode) and returning a MapPoint location object.
    Cache these Location objects.


    Richard
    Winwaed Software Technology LLC
    http://www.winwaed.com
    See http://www.mapping-tools.com for MapPoint Tools

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Using MapPoint function from EXCEL VBA
    By Jonboy in forum MapPoint Desktop Discussion
    Replies: 7
    Last Post: 05-01-2009, 05:43 AM
  2. Javascript function call from vb.net
    By JoeBo in forum Bing Maps and MapPoint Web Service
    Replies: 2
    Last Post: 03-28-2008, 09:20 AM
  3. Need Help using the SUM function for County
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 1
    Last Post: 04-08-2005, 11:48 PM
  4. Excel VBA or VB6 For Speed
    By DavidP in forum MapPoint Desktop Discussion
    Replies: 3
    Last Post: 08-14-2004, 06:50 PM
  5. Fix DistanceTo function
    By PC in forum Wish List
    Replies: 1
    Last Post: 03-21-2003, 06:35 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