VBA function to return hyperlink

[erichq]

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]

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]

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]

post 3

[erichq]

post 4

[erichq]

post 5

[erichq]

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]

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]

Selection.Hyperlinks.Add Anchor:=Selection, Address:=sURL

[erichq]

Thanks for your reply. Unfortunatly the function still doesn't work in Excel 2007. Is there a work-around available?

cheers

[geekgirlau]

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]

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...