Hi,

I am working on an Excel VBA based app that uses MapPoint to display two postcodes and will draw a line between the two. The color and weight of that line is selected from an excel userform and the result stored in a hidden sheet along with some other bits and bobs.
The problem that I've been struggling for the last 48 hours relates to programatically setting the line color. I'm using the color constants described on MSDN Using Shape Color Values in MapPoint. Relevant code is below. I'm getting type mismatches between the string value as a result of the vlookup (tried mpBlack and the &H1& values as the lookup result) Manually coding line.forecolor = mpblack works fine.

Other things I've tried:
1. using forecolor.schemecolor

Code:

 
shape.line.forecolor.SchemeColor = 2 'should be pink/purple

2. forecolor.RGB

Code:

 
shape.line.forecolor.RGB= FF00FF

If the code doesn't error then I only get black lines (or in one case Olive, regardless of the value passed in the statement).

Code:

Dim oApp, oMap As Object
Set oApp = GetObject(, "MapPoint.Application")
oApp.Units = geoKm
oApp.ActiveMap.MapStyle = geoMapStyleData
    'set up variables
    Dim oLocA, oLocB As MapPoint.Location
    Dim strSitePcdeA, strSitePcdeB, strColour, strWeight As String
    Dim strLineName As String
    'get the submitted color
    strColour = Application.WorksheetFunction.VLookup(strSiteBw, Sheets("Circuits").Range("A2", Sheets("Circuits").Range("C65534").End(xlUp)), 2, False)
    If strColour = "<Select Colour>" Then strColour = "Black"
 
    'convert to the MP colour
    strColour = Application.WorksheetFunction.VLookup(strColour, Sheets("Lines").Range("A1", Sheets("Lines").Range("B65534").End(xlUp)), 2, False)
 
    Set oMap = GetObject(, "MapPoint.Application").ActiveMap
 
    'query mappoint for locations
    Set oLocA = oMap.FindAddressResults(, , , , strSitePcdeA)(1)
    Set oLocB = oMap.FindAddressResults(, , , , strSitePcdeB)(1)
 
    'draw the line
    oMap.Shapes.AddLine(oLocA, oLocB).Name = strLineName
    With oMap.Shapes.Item(strLineName)
        .ZOrder geoSendBehindRoads
        .Line.ForeColor = strColour
        .Line.Weight = strWeight
    End With
 
Set oLocA = Nothing
Set oLocB = Nothing
oMap.DataSets.ZoomTo      'zoom to datasets when displaying map
oApp.Visible = True

I've been googling and even the microsoft object model is scant on detail around the shape.line methods/ properties. There is C# based information but this focusses on using the System.Drawing namespace and defining a color variable. Something not possible in VBA AFAIK. I also can't find a VBA way to cast a string to a color...

I'm sure this is something that can be sorted quite simply so any assistance / direction /advice would be greatfully received.
Thanks for your time and help.

Cheers
Matt

You get the color converted to the relevant Number value here right?

'convert to the MP colour
strColour = Application.WorksheetFunction.VLookup(strColour, Sheets("Lines").Range("A1", Sheets("Lines").Range("B65534").End(xlUp)), 2, False)

What does your lookup worksheet look like? I assume it's a string for the color name and then a number, right?

I think you just need to convert your strColour to an Integer. I'm not sure what the VBA function to cast to an integer is off the top of my head, but something like this should work --

.Line.ForeColor = Int(strColour)
or
.Line.ForeColor = CInt(strColour)
or
.Line.ForeColor = Val(strColour)

Hi Eric,

Thanks for the prompt response. The lookup sheet is attached. I've experimented with the lookup returning the public constant (as as declared in the MapPointColors module eg mpBlack) or the hex value. Neither will be to happy with casting to an integer I suspect.

If there is an easier way of doing this that doesn't limit me to vbRed, vbBlue etc, I'd be happy to give it a pop....

Thanks
Matt

Yes, you want to get the Integer value, you could do this with some string manipulation and the =HEX2DEC Excel function.

See attached, I added some columns.

The Excel functions I used are

=RIGHT(I2,LEN(I2)-2)

This strips the &H from the front of the string.

=IF(RIGHT(K2,1)="&",LEFT(K2,LEN(K2)-1),K2)

This strips the & when it exists from the right of the string.

=HEX2DEC(L2)

Converts to Integer.


So if you thought the solution was going to be a little nasty, you were correct. But it's not too bad...

Eric,

You are a gent and a scholar. Thank you very much for your invaluable assistance. That worked an absolute treat. Project completed.

Slightly off topic but I've noticed VSTO for the first time today. Do you know of any articles that discuss the relative merits of Office VBA/ VSTO/ VS2010 windows forms (with MP reference added)?
I guess the question is, is continuing development in VBA like flogging a dead horse?

Again many thanks for your help.
Cheers
Matt

Start a new thread to attract other's attention and input on this, but I think VBA/VB6 is fine as long as you're just automating MapPoint... it's a little simpler and you're not losing any functionality.

With the .NET platform it's a lot easier to incorporate web services, and of course you then have a lot more UI options and easier programming with all the new libraries.

Eric