Can UDF return a customized hyperlink?

[ucdcrush]

I am working on a UDF which returns 3 values across 3 cells (ie select 3 cells, enter "=geocode(a1:c1)" as an array formula, and it returns lat, lon, and something else from the address in a1:c1.

The something else part is supposed to be a clickable hyperlink which is customized to display the particular latitude and longitude location on Google maps website.

However, I do not know how to store the hyperlink in the cell (from the UDF) so that it's clickable. I thought I could set the cell to be the HYPERLINK function which included the lat/lon link, but when I try, it just returnsa cell containing what seems like plain text containing the function
=HYPERLINK("http://www.google...","link")
It's not clickable, and as far as I can tell, it's plain text.

Is there a way to return, from the UDF, a cell containing a working Hyperlink function? Or another way to do this? I was thinking a separate VBA program which could process each cell in a range, by creating a hyperlink to thevalue of the cell, but I'd like to see if a more direct approach from the UDF is possible.

Thanks.

 

[Living the Dream]

Hi

From my brief look at your problem you face the following with direct
Geo-Coding, here's a coupe of locations where you can read up on the
Whats What.

https://developers.google.com/maps/documentation/staticmaps/

https://developers.google.com/maps/documentation/geocoding/#ReverseGeocoding

You, and or your users of this will require a Google Account, at which
time can set up the required uses of Google's ( Maps API v3 & or Google
Maps Coordinate API ) of which both have daily limitation ( 25,000 &
1,000 respectively ), anything beyond this means you have to start
paying for it.

You will also have to Create an oAuth 2.0 Client ID for API Access.

Anyways good luck with your venture, I put together this little chunk of
code, but without setting up the required API Client & Access, it gives
an Error 404.

HTH
Mick.

Sub Goto_myMap()

Dim IE As Object
Dim myLon As Range, myLat As Range
Dim myMapRef As String

Set IE = CreateObject("InternetExplorer.Application")
Set myLat = [A2]
Set myLon = [B2]

myMapRef = myLat & "," & myLon

If Not myMapRef = "" Then
IE.Visible = True
IE.navigate "http://maps.googleapis.com/maps/api/staticmap?Center="
& myMapRef & "&sensor=false"
End If

Set IE = Nothing
Set objElement = Nothing
Set objCollection = Nothing

Application.StatusBar = ""

End Sub

 

[Living the Dream]

Hmmm

I noticed in my previous response the URL link looked incomplete so I
looked at it from this angle. It still throws up a Error 404, but it now
related to Client/API setup access, of which I am hopeful will be
remedied once you setup Google's requirements.

Fingers Crossed.

Cheers
Mick.

Sub Goto_myMap()

Dim IE As Object
Dim myLon As Range, myLat As Range
Dim myAdd1 As String, myAdd2 As String, myAdd3 As String
Dim myMap As String

Set IE = CreateObject("InternetExplorer.Application")
Set myLat = [A2]
Set myLon = [B2]

myAdd1 = "http://maps.googleapis.com/maps/api/staticmap?Center="
myAdd2 = myLat & "," & myLon
myAdd3 = "&sensor=False"

myMap = myAdd1 & myAdd2 & myAdd3

If Not myMap = "" Then
IE.Visible = True
IE.navigate myMap
End If

Set IE = Nothing
Set objElement = Nothing
Set objCollection = Nothing

Application.StatusBar = ""

End Sub

 

[Living the Dream]

Right-E-Oh then

I did some more playing and found there is an issue in the URL string so
I went back to basics to create the entire URL inside a cell albeit it
requires helper cells to create it.

Once again, Google is very sheepish about allowing anyone to directly
navigate their site as it still will not allow you to go straight to the
map without the API's.

Once you get past Google's API Calls, you might be home free, and if
not, then it's all for naught.

Good luck from this point

Cheers
Mick.

So, for the URL, you can change the cell addresses to whatever you want,
I will use the following for this example.


A2 = "http://maps.googleapis.com/maps/api/staticmap?Center="
B2 = "<Your Lat Numbers>"
C2 = ","
D2 = "<Your Lon Numbers>"
E2 = "&sensor=False"

You must wrap Cells A2 - E2 in double quotes ""

F2 = A1&B2&C2&D2&E2
G2 = Hyperlink(F2)

Sub Goto_myMap()

Dim IE As Object
Dim myMap As range

Set IE = CreateObject("InternetExplorer.Application")
Set myMap = Sheets("YourSheet").Range("G2")

If Not myMap = "" Then
IE.Visible = True
IE.navigate myMap
End If

Set IE = Nothing
Set objElement = Nothing
Set objCollection = Nothing

Application.StatusBar = ""

End Sub