Convert VB code to VBA

[Lavenderchan]

Hello,

I am looking to plot point multiple pins in google maps using vba code. I am using this code to plot point one property using lat and long:

Private Sub Command92_Click()
On Error Resume Next
If IsNull(Me.Longitude) Or Me.Latitude = "" Then
MsgBox "No Longitude and latitude info found", vbCritical, "Error Info Dailog"
Else
 'double address written below after @ to be at centre and 10z are the zooming
Url = "[URL unfurl="true"]https://www.google.ae/maps/place/"[/URL] & Me.Latitude & "," & Me.Longitude & "/@" & Me.Latitude & "," & Me.Longitude & ",10z"
Application.FollowHyperlink Url
End If
End Sub

The only open source code that I did find that shows multiple plot points is written in vb.net and I am not sure how to convert it or if I should just rewrite my own. Here is the code:

Public Class GoogleMap
 Private MyLatitudineMinima As Double
 Private MyLatitudineMassima As Double
 Private MyLongitudineMinima As Double
 Private MyLongitudineMassima As Double
 Private MySizeW As Integer = 640
 Private MySizeH As Integer = 640
 Private MyScale As Integer = 1
 Private MyZoom As Integer = 14
 Private MyMapType As String = "roadmap"
 Private MySensor As Boolean = False
 Private AddMarker As Boolean = True
 Public Sub New(ByVal LatitudineMin As Double, ByVal LongitudineMin As Double, ByVal LatitudineMax As Double, ByVal LongitudineMax As Double)
 MyLatitudineMassima = LatitudineMax
 MyLatitudineMinima = LatitudineMin
 MyLongitudineMassima = LongitudineMax
 MyLongitudineMinima = LongitudineMin
 End Sub
 Private ReadOnly Property Url() As String
 Get
 Dim Str As String
 Str = "[URL unfurl="true"]http://maps.googleapis.com/maps/api/staticmap?center="[/URL]
 Str = Str & Coordinate.Avg(MyLatitudineMinima, MyLongitudineMinima, MyLatitudineMassima, MyLongitudineMassima).ToGoogle
 Str = Str & "&zoom=" & MyZoom.ToString
 Str = Str & "&size=" & MySizeW.ToString & "x" & MySizeH.ToString
 Str = Str & "&scale=" & MyScale.ToString
 Str = Str & "&maptype=" & MyMapType
 If AddMarker Then
 Str = Str & "&markers=color:green%7Clabel:G%7C" & Coordinate.Avg(MyLatitudineMinima, MyLongitudineMinima, MyLatitudineMassima, MyLongitudineMassima).ToGoogle
 End If
 Str = Str & "&sensor=" & MySensor.ToString.ToLower
 Return Str
 End Get
 End Property
 Public ReadOnly Property Image() As Image
 Get
 Dim ur As New System.Uri(Url)
 Dim Wr As System.Net.WebRequest
 Dim St As System.IO.Stream
 Wr = System.Net.WebRequest.Create(ur)
 Wr.Proxy = System.Net.WebRequest.GetSystemWebProxy
 st = Wr.GetResponse.GetResponseStream

 Using Br As New IO.BinaryReader(St)
 Using fs As New IO.MemoryStream
 ' Dim buffer(1024) As Byte
 Try
 Do
 fs.WriteByte(Br.ReadByte())

 Loop
 Catch Ex As System.IO.EndOfStreamException
 'finito stream
 Return System.Drawing.Image.FromStream(fs)
 Finally
 St.Close()
 St.Dispose()
 End Try
 End Using
 End Using

 End Get
 End Property

Any help would be appreciated.

 

[lameid]

There are a lot of .Net objects in there. I have shaked a stick at .Net a couple of times but am far from an expert and nothing is obvious there except that there is no end to the Class.

Although it does look like it somehow reads a picture of the map somewhere along the way with Image.

I did however find this link which may be useful but I also suspect will need some heavy adaptation - I did not get too deep into it. I would like to revisit the link myself sometime as I think it may allow me to retrieve results for something else from a website at least with some of the underlying pieces. If you get this adapted, I think we would all like to see it.

http://ramblings.mcpher.com/Home/excelquirks/getmaps/mapmarkers

 

[strongm]

>shows multiple plot points

Are you sure? Whilst this .net code is far from complete, it only seems to be written to display a single plot point/marker (at the center of the displayed map). Plus it is missing the required API key to authenticate, which means that Google will reject the query. You have to have a Google Cloud billing account to get an API key. Without it, you cannot use the Google Maps Static API, nor the jscript API. And, unfortunately, I am not aware of a way to plot multiple points just using a simple, non-API URL, such as you are trying to use in Command92_Click

That being said, and assuming you have an API key (and a webbrowser on a userform ..) something like the following should work (and is a rough translation of the .net code in your post):

[blue]Private Sub CommandButton1_Click()
    Dim Str As String
    Dim MyZoom As Double
    Dim MySizeW As Double
    Dim MySizeH As Double
    Dim MyScale As Double
    Dim MyMapType As String
    Dim lat As Double
    Dim longi As Double
    Dim APIKey As String
    
    MySizeW = 640
    MySizeH = 640
    MyScale = 1
    MyZoom = 14
    MyMapType = "roadmap"
    APIKey = "????"
 
     lat = 40.7143528
     longi = -74.0059731
     
     MyZoom = 1
     Str = "[URL unfurl="true"]http://maps.googleapis.com/maps/api/staticmap?center="[/URL]
     Str = Str & lat & "," & longi
     Str = Str & "&zoom=" & MyZoom
     Str = Str & "&size=" & MySizeW & "x" & MySizeH
     Str = Str & "&scale=" & MyScale
     Str = Str & "&maptype=" & MyMapType
     
     For lp = 0 To n ' adds n-1 markers in blue with the label 'S' assuming you have arrays of lat/long coordinates
        Str = Str & "&markers:color:blue" & "%7C" & "label:S" & "%7C" & lat(lp) & "," & longi(lp)
    Next
    Str = Str & "&key=" & APIKey
        


    WebBrowser1.Navigate2 Str
End Sub
[/blue]

 

[strongm]

Was this of any use?