PDA

View Full Version : Excel VBA function speed



spencerdavis
02-23-2009, 05:51 PM
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?

Winwaed
02-23-2009, 10:33 PM
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 (http://www.milecharter.com)
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 (http://www.mpmileage.com) but this calculates point-to-point route listings from a database, rather than a many-to-many table.


Richard

spencerdavis
02-24-2009, 04:13 AM
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

Winwaed
02-24-2009, 08:41 AM
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