2

I have searched far and wide, but can't find an answer to this simple question. I want to make a custom function in excel which will create a hyperlink.

Excel has a built in hyperlink function that works like this:

=Hyperlink(link_location, display_text)

I want to create a function called CustomHyperlink which takes one parameter, and returns a hyperlink to a google query with that parameter. Just for the sake of the question, lets assume that the passed parameter is a alphanumeric string, with no spaces.

Essentially, calling

=CustomHyperlink("excel")

should be the same as calling 

=Hyperlink("http://www.google.com/search?q=excel", "excel")

This seems like such a simple task, but I absolutely cannot find a way to make this function.

Can anyone offer some quick help?

Morglor
  • 347
  • 2
  • 7
  • 15

4 Answers4

1

I can offer a partial solution, one that will update an existing hyperlink. This only makes sence if you are using it like, say

CustomHyperlink(A1)

were A1 contains the required serch term

To use,

  1. enter your UDF formula in a cell, eg =CustomHyperlink(A1)
  2. create a hyperlink on the cell (right click, Hyperlink...) . This can be any hyperlink, valid or invalid
  3. put the required search term in the referenced cell, eg in A1 put excel

When the UDF runs it will update the hyperlink to Google the entered search term

Function CustomHyperlink(Term As String) As String
    Dim rng As Range

    Set rng = Application.Caller
    CustomHyperlink = Term

    If rng.Hyperlinks.Count > 0 Then
        rng.Hyperlinks(1).Address = "http://www.google.com/search?q=" & Term
    End If
End Function
chris neilsen
  • 52,446
  • 10
  • 84
  • 123
  • It does have the extra step of manually create the link but it is a very ingenious solution and works pretty well. Thanks! – tozevv Sep 12 '14 at 09:04
0

In VBA editor you can use

ThisWorkbook.FollowHyperlink Address:=(strWebsite), NewWindow:=True

Which will take you to that specific website, and just build a function around that to navigate you to the site you need.

  • 1
    If this was wrapped into a worksheet UDF, then upon any recalculation of the page / workbook etc. a new brower window will be loaded. I guess that wouldn't be ideal. – markblandford Mar 05 '12 at 09:56
0

Nice idea although this isn't possible.

You seem to want to have the formula of the cell as one thing (your custom function call) and yet have the value as another (the hyperlink / URL) which simply isn't possible.

The correct way through VBA to add a hyperlink is to use the Hyperlinks property but it is not possible to call this property, through a Worksheet UDF (because of the reason above).

What is wrong with just using the the built-in =Hyperlink() worksheet function? You could effectively parameterise your URL as follows (where cell A1 = Excel):

=HYPERLINK("http://www.google.com/search?q="&A1)
markblandford
  • 3,153
  • 3
  • 20
  • 28
0

You can't do this directly for the reasons creamyegg suggests, but there is a way to achieve the functionality albeit with a bit of a performance consideration.

You could use the Worksheet_Change event to track for the presence of your UDF then process the hyperlink addition there.

You would need to set up an empty function to allow this to happen, otherwise Excel will throw an error whenever you entered =CustomHyperlink... in a cell.

The below should work, not really had time to test.

Private Sub worksheet_change(ByVal target As Range)
    Dim SearchValue As String
    If LCase(Left(target.Formula, 16)) = "=customhyperlink" Then
        SearchValue = Mid(target.Formula, 19, Len(target.Formula) - 20)
        target.Value = SearchValue
        target.Hyperlinks.Add target, "http://www.google.com/search?q=" & SearchValue, ,     "Search Google for " & SearchValue, SearchValue
    End If
End Sub

The performance consideration is of course the volatile Worksheet_Change event as this can really kill large, complex workbooks.

Lunatik
  • 3,838
  • 6
  • 37
  • 52