PDA

View Full Version : Solved: VBA function to return hyperlink



erichq
01-17-2009, 03:51 AM
Hi all,

I would like to program an Excel function that supports a Google search for search criteria, which are stored in Excel worksheet.

The function should return a hyperlink to google and conduct a google search upon clicking the hyperlink.



Public Function gs(sSearchstring As String, Optional sGooglesite = "de")

Dim sGoogle, sGoogle1, sGoogle2, sURL As String

'please replace "URL of google" with the URL of google, as I am
'not allowd to post links
sGoogle = "URL of google/search?hl=" & sGooglesite & "&q="
sGoogle2 = "&btnG=Google-Suche&meta="

sGoogle1 = Replace(sSearchstring, " ", "+")

sURL = sGoogle & sGoogle1 & sGoogle2
gs = sURL

End Function



The function above returns the correct URL in form of a string. How can I adjust the function to return a hyperlink instead of the string? I am aware that =hyperlink(gs("search criteria")) delivers the desired result, but this solution is not very userfriendly.

I really would appreciate your help, as I don't how to address this problem.


Thanks

Bob Phillips
01-17-2009, 04:08 AM
This does nothing for me, but is it what you want?



Public Function gs(sSearchstring As String, Optional sGooglesite = "de")

Dim sGoogle, sGoogle1, sGoogle2, sURL As String

'please replace "URL of google" with the URL of google, as I am
'not allowd to post links
sGoogle = "URL of google/search?hl=" & sGooglesite & "&q="
sGoogle2 = "&btnG=Google-Suche&meta="

sGoogle1 = Replace(sSearchstring, " ", "+")

sURL = sGoogle & sGoogle1 & sGoogle2
gs = sSearchstring

Application.Caller.Hyperlinks.Add Anchor:=Application.Caller, Address:=sURL

End Function

erichq
01-17-2009, 04:12 AM
Wow, that is what I intended. Thank you so much, that was really fast.

If you want to use the function, you would have to replace in

sGoogle = "..." the "URL of google" with hxxp://yyy.google.com
then it should work.

I cant do it myself as I am not yet allowed to post links.

Cheers,
Erich

erichq
01-17-2009, 04:42 AM
post 3

erichq
01-17-2009, 04:42 AM
post 4

erichq
01-17-2009, 04:46 AM
post 5

erichq
01-17-2009, 04:49 AM
Here the ready to cut&paste&go vba code



Public Function gs(sSearchstring As String, Optional sGooglesite = "en")

Dim sGoogle, sGoogle1, sGoogle2, sURL As String

sGoogle = "http://www.google.com/search?hl=" & sGooglesite & "&q="
sGoogle2 = "&btnG=Google-Suche&meta="

sGoogle1 = Replace(sSearchstring, " ", "+")

sURL = sGoogle & sGoogle1 & sGoogle2
gs = sSearchstring

Application.Caller.Hyperlinks.Add Anchor:=Application.Caller, Address:=sURL

End Function

thanks for your help

erichq
05-28-2009, 09:23 AM
Hi,

the function stopped working in Excel 2007. It would always return "#Value!".

The problem relates to the row:

"Application.Caller.Hyperlinks.Add Anchor:=Application.Caller, Address:=sURL"

Any idea what the problem could be and on how to fix the issue?

cheers

mdmackillop
05-28-2009, 12:12 PM
Selection.Hyperlinks.Add Anchor:=Selection, Address:=sURL

erichq
12-17-2009, 03:25 AM
Thanks for your reply. Unfortunatly the function still doesn't work in Excel 2007. Is there a work-around available?

cheers

geekgirlau
12-22-2009, 09:02 PM
I couldn't get this working in Excel 2007 either, but the hyperlink function does work.

Why not set up the search strings in a range of cells? Your formula would then be =HYPERLINK(gs(A1),"Friendly name"), with A1 containing the search string.

erichq
01-08-2010, 05:01 AM
thanks for your help. But I was looking for a more user friendly way to cope with that task, meaning a UDF.

It is kind of funny that MS plays around with VBA from release to release stopping programms from working...