UDF return Hyperlink RRS feed

  • Question

  • Hi,

    I've been trying for enough time to think about giving up even though I can't =D.

    I'm trying to create a UDF to return a Hyperlink.

    I want to call this Function from a cell like a Formula.

    After many shoots I've tried:

    Function getLink(TS as string)
    'do evaluations to get myURL
    getLink = Evaluate("=hyperlink(""" & myURL& """,""Click me"")")
    End Function

    It returns Click Me as a String, but not a link.

    Believe me I give many shoots to get into this, but none of them worked.

    Friday, June 14, 2013 1:16 AM

All replies

  • The solution is much more complicated because worksheet and range object can not be access through UDF.

    Actually, the hyperlink is created by calculate event with following solution.

    You can try following steps

    1. Insert a class module and name it "clsSht" and add following code:

    Public WithEvents Sht As Worksheet
    Public Rng As Range
    Public myURL As String
    Private Sub Sht_Calculate()
        On Error Resume Next
        Sht.Hyperlinks.Add Rng, myURL
    End Sub

    2. Insert a module and add following code:

    Public newLink As clsSht
    Function getLink(TS As String)
        Dim myURL   As String
        Dim Sht     As Worksheet
        Dim Rng     As Range
        'do evaluations to get myURL
        myURL = ""
        'Add Hyperlink
        Set newLink = New clsSht
        Set newLink.Rng = Application.Caller
        Set newLink.Sht = Application.Caller.Parent
        newLink.myURL = myURL
        'Show Display text
        getLink = "Click Me"
    End Function

    3. Try it

    Friday, June 14, 2013 2:10 AM