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 6 of 6

Setting MapPoint 2009 line color by variable in VBA

This is a discussion on Setting MapPoint 2009 line color by variable in VBA within the Development forums, part of the MapPoint Desktop Discussion category; Hi, I am working on an Excel VBA based app that uses MapPoint to display two postcodes and will draw ...

  1. #1
    mattdee is offline Junior Member White Belt
    Join Date
    Jan 2009
    Posts
    5

    Setting MapPoint 2009 line color by variable in VBA

    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

  2. #2
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,094
    Blog Entries
    4

    Re: Setting MapPoint 2009 line color by variable in VBA

    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)
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

  3. #3
    mattdee is offline Junior Member White Belt
    Join Date
    Jan 2009
    Posts
    5

    Re: Setting MapPoint 2009 line color by variable in VBA

    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
    Attached Files Attached Files

  4. #4
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,094
    Blog Entries
    4

    Re: Setting MapPoint 2009 line color by variable in VBA

    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...
    Attached Files Attached Files
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

  5. #5
    mattdee is offline Junior Member White Belt
    Join Date
    Jan 2009
    Posts
    5

    Re: Setting MapPoint 2009 line color by variable in VBA

    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
    Last edited by mattdee; 01-18-2011 at 05:04 PM.

  6. #6
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,094
    Blog Entries
    4

    Re: Setting MapPoint 2009 line color by variable in VBA

    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
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. GPS trail line color
    By onelegna in forum MapPoint Desktop Discussion
    Replies: 1
    Last Post: 07-23-2008, 08:22 PM
  2. c++ - setting the color of a line
    By leopoldB in forum Development
    Replies: 2
    Last Post: 05-13-2008, 08:55 AM
  3. Setting Shape Color
    By Kevin_Williams in forum MapPoint Desktop Discussion
    Replies: 1
    Last Post: 02-28-2007, 12:54 PM
  4. Color coding locations by some variable, i.e. sales dollars
    By mr4tno in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 02-17-2005, 10:15 AM
  5. How to change line thickness and color
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 9
    Last Post: 12-12-2003, 11:02 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