Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Convert VB code to VBA

Convert VB code to VBA

Convert VB code to VBA


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"
 'double address written below after @ to be at centre and 10z are the zooming
Url = "https://www.google.ae/maps/place/" & 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
 Dim Str As String
 Str = "http://maps.googleapis.com/maps/api/staticmap?center="
 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
 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

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

 End Get
 End Property 
Any help would be appreciated.

RE: Convert VB code to VBA

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.


RE: Convert VB code to VBA

>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):


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 = "http://maps.googleapis.com/maps/api/staticmap?center="
     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)
    Str = Str & "&key=" & APIKey

    WebBrowser1.Navigate2 Str
End Sub

RE: Convert VB code to VBA

Was this of any use?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close