MapForums

I am new to programming MapPoint and I would like to speed up the following code that I have written using Excel VBA (this is just an extract not the complete subroutine)

Set objRS = objDataSet.QueryAllRecords

'Loop through all the records (if any)
' Symbol 336 is first of Custom set
Do Until objRS.EOF
Set objFldStatus = objRS.Fields.Item("Status")
Set objFldType = objRS.Fields.Item("Type")
Select Case objFldStatus.Value
Case "M"
Select Case objFldType.Value
Case "FG"
objRS.Pushpin.Symbol = 16 ' black dot
Case "MC"
objRS.Pushpin.Symbol = 23 ' purple dot
Case "RC"
objRS.Pushpin.Symbol = 17 ' red dot
Case "TC"
objRS.Pushpin.Symbol = 22 ' green dot
Case "SC"
objRS.Pushpin.Symbol = 21 ' cyan dot
Case "FT"
objRS.Pushpin.Symbol = 20 ' blue dot
Case Else
objRS.Pushpin.Symbol = 254 ' question mark
End Select
Case "D"
objRS.Pushpin.Symbol = 336 ' transparent
Case "X"
objRS.Pushpin.Symbol = 336 ' transparent
Case Else
objRS.Pushpin.Symbol = 255 ' exclamation mark
End Select

'Move to the next record (or to EOF)
objRS.MoveNext
Loop

I have also tried using the if ... then ... else if construct but I did not detect much reduction in execution time when processing 10,000 records.

As I do not have VB6 nor do I have Microsoft Office XP Developer I am unable to create a DLL

Richard

Hi,

I dont think you can speed this code up. If/else does the same as a Case statement, also you do a 1 byte compare (or 2), so that is also fast because it is only CPU register comparisation.

The problem is probably also not because you loop in 10000 records (you can test this of course), but because you are redisplaying all the pushpins (changing the symbols), and that is what take time.

So what you eventually can do is process the whole data and whilst processing check which symbol you want and put the pushpin on mappoint, so then it is only 1 loop while it is displayed and the whole time will be exact the time needed for display.

I hope my explanation make sence...

__________________
rgds, Wilfried Mestdagh
www.mestdagh.biz
MapPoint coding demo
Order MapPoint 2009 with Routing and User Tools Spreadsheet

Wilfried,

Thank you for the quick reply - much faster than MapPoint

I will do some tests to see where the time is being consumed by removing various bits of the code.

In the quoted paragraph from your reply are you suggesting that I should check to see if the symbol already exists and only update it if it is different? I have tried running without the map display on and I have not noticed any significant different in execution time. MapPoint certainly uses 100% of the available CPU time.

Richard

Hi Richard,

No, I'm sorry but my English is not so good to express myself sometime

what I meant was following:

Now (as far as I can conclude from your code) you trow all positions into mappoint database. Then you check each record and eventually update symbol. This is time consuming.

I meant that you go tru your own data first in VBA, and for each record assign the right symbol and trow the symbol to mappoint.

This way all the work is done in one single loop, and you are not depending on the speed of mappoint.

Is this more clear what I mean ?

__________________
rgds, Wilfried Mestdagh
www.mestdagh.biz
MapPoint coding demo
Order MapPoint 2009 with Routing and User Tools Spreadsheet

Wilfried has a good overall idea. If it fits what you're doing, it will be the fastest option.

If you must use this code, there are three things you can try:

- Simplify your comparisons. Can you work with numbers for the first 2 chars?
If you use numbers, you can use a Look Up Table.

- Are some options more likely than others?
List them in order of likelihood. The most likely first. This removes many unnecessary comparisons.
Also investigate a binary search instead of a linear one.

- Use the VB6 "With...End" statement. This avoids a lot of VB's member lookup processing time.


Richard

__________________
Winwaed Software Technology LLC
http://www.winwaed.com
See http://www.mapping-tools.com for MapPoint Tools
Pre-Order MapPoint 2009 today: http://www.mapping-tools.com/mappoint2009

Wilfried and Richard,

Many thanks for your help. I now understand that it is best to prepare my data in Excel prior to invoking MapPoint. The easiest change for me is to prepare a Symbol field in my Excel spreadsheet and use this to set the pushpin symbol. It is a pity that Microsoft do not recognise a field name of Symbol and use this directly. This would get round the restriction of 8 different pushpins of the Multiple Symbol Map; it is not always necessary to have a legend.

Am I correct in thinking that an XLA file executes more quickly than the same code run from within the VBA Editor? It seems to for me. I say seems because the timings that I have taken vary such a lot.

Richard