This demo combines 4 static street view images and 2 static (non-interactive) map images (road & satellite) into an Excel panorama view using Google's static MAP & Street View APIs.

The Google Static Map API service creates a static (non-interactive) map based on URL parameters sent through a standard HTTP request and returns the map as an image, which you can be displayed in Excel. 

In the same way, the Google Street View Image API creates a Street View panorama. The viewport is defined with URL parameters sent and is returned as a static image in Excel.

The VBA code is simple: oShape.Fill.UserPicture strURL  where strURL is a string with URL parameters, as defined in Google developer's guides. The image created by the service is loaded in an Excel shape object.

In our demo, the shape outline has been removed (no outline), the shapes have been 'snapped to shape' and grouped.

A search address can be entered in 3 ways:

• In the ribbon, in the address field.
  
• In the userform's text field, above the 'Show Map' button.
  
• Or by selecting and/or editing a POI (point of interest) from the listbox.
  
  

The listbox is loaded with POI descriptions from an Excel 2007 Table (ListObject), which can be edited by the user.

The slider controls the zoom level, when a search is made through the userform. The Ribbon search is fixed to zoom level 14.

Google is enforcing a quota limit for non-business use customers. Since 4 Street View requests are made per search in our Excel demo, the limit per 24 hours is up to 25,000 searches per viewer. The service will stop working immediately, if the limit is exceeded to prevent abuse.

---

Sub GoogleStaticStreetView(oShape As Shape, _
                        sAddress As String, _
                        lHeading As Long, _
                        Optional lHeight As Long = 512, _
                        Optional lWidth As Long = 512)
                        
    'https://developers.google.com/maps/documentation/streetview/

    Dim sURL As String
    Dim sMapsURL As String

    On Error GoTo RETURN_FALSE
    
    If bRunMode Then On Error Resume Next 'Error if quota exceeded

    If Len(sAddress) > 0 Then
        'URL-Escaped addresses
        sAddress = Replace(sAddress, " ", "+")
    Else
        Exit Sub
    End If
   
    sURL = _
    "http://maps.googleapis.com/maps/api/streetview?" & _
    "&location=" & sAddress & _
    "&size=" & lWidth & "x" & lHeight & _
    "&heading=" & lHeading & _
    "&sensor=false"

    sMapsURL = "http://maps.google.com/maps?q=" & _
    sAddress & "&t=m&layer=c&panoid=0" & _
    "&cbp=12," & lHeading & ",,0,4.18"
    
    oShape.Fill.UserPicture sURL
    oShape.AlternativeText = sMapsURL
    
    Exit Sub
    
RETURN_FALSE:
    
End Sub

Sub GoogleStaticMap(oShape As Shape, _
                    sAddress As String, _
                    Optional sMapType As String = "roadmap", _
                    Optional lZoom As Long = 12, _
                    Optional lHeight As Long = 512, _
                    Optional lWidth As Long = 512)
                    
    'https://developers.google.com/maps/documentation/staticmaps/
                    
    Dim sURL As String
    Dim sMapsURL As String
    Dim sMapTypeURL As String
    
    On Error GoTo RETURN_FALSE
    
    ' Google Maps Parameters '&t=m' = roadmap, '&t=k' = satellite
    sMapTypeURL = "m"
    If sMapType = "satellite" Then
        sMapTypeURL = "k"
    End If
    
    If bRunMode Then On Error Resume Next 'Error if quota exceeded

    If Len(sAddress) > 0 Then
        'URL-Escaped addresses
        sAddress = Replace(sAddress, " ", "+")
    Else
        Exit Sub
    End If
    
    sURL = _
    "http://maps.googleapis.com/maps/api/staticmap?center=" & _
    sAddress & "," & _
    "&maptype=" & sMapType & _
    "&markers=color:green%7Clabel:%7C" & sAddress & _
    "&zoom=" & lZoom & _
    "&size=" & lWidth & "x" & lHeight & _
    "&sensor=false" & _
    "&scale=1"

    sMapsURL = "http://maps.google.com/maps?q=" & _
    sAddress & _
    "&z=" & lZoom & _
    "&t=" & sMapTypeURL
    
    oShape.Fill.UserPicture sURL
    oShape.AlternativeText = sMapsURL
    
    Exit Sub
    
RETURN_FALSE:

End Sub

Google Maps Excel workbook running in Mac Parallels desktop

---

Google Map & Street view Panorama workbook powered by Ribbon Commander (RC)

---

Google Maps API & Pricing

As of 2018, the Google Maps platform cannot be used anonymously. 

To use the static maps, you must first activate the API in the Google Cloud Platform Console and obtain the proper authentication credentials. You need to provide an API key in each request (or a client ID if you have a Premium Plan).

Please read how to activate your API Key and about pricing. Google offers a generous free trial & free monthly credit.

---

Download

Please click icon once to download the file:
Google Maps Panorama (free-unlocked).zip

Click button once to download the RC powered version from Ribbon Commander's official website.

​Excel VBA is unlocked and runs in Excel 2007 or later!

---

VBA Chord Diagram With Hierarchical Edge Bundling

This VBA chord diagram uses the free D3.js JavaScript library to demonstrate a graphical method of displaying the inter-relationships between code procedures in the Google Maps VBA project powered by Ribbon Commander.

The data is arranged radially around a circle with the relationships between the points drawn as arcs connecting the procedures together. Instead of using straight lines to show the interconnections, this chord diagram employs a technique called 'hierarchical edge bundling' to reduce visual complexity.

The project name, module type and name is shown at the bottom of the chart when the mouse pointer hovers over a VBA procedure.

---

---

Build identical charts for your programs using the VBA Toolkit addin powered by Ribbon Commander

---

comments powered by Disqus