Add Hyperlink in VBA UDF

Question

I've seen how to edit a hyperlink - but I need to add a hyperlink when used as a custom formula.

I get a #VALUE error and I'm not sure why. Does anyone have any ideas why I get a #VALUE error when I try to use it in a sheet as =testit39()

Public Function testit39() As String
Application.Volatile
Dim rng As range, milestoneinfo As String, milestonesymbol As String

Set rng = Application.Caller
milestoneinfo = "info"
milestonesymbol = "symbol"
If rng.Hyperlinks.Count > 0 Then
    rng.Hyperlinks(1).address = ""
    rng.Hyperlinks(1).screentip = milestoneinfo
    Else
   ThisWorkbook.ActiveSheet.Hyperlinks.Add Anchor:=rng, _
                               address:="", _
                               screentip:=milestoneinfo

    rng.Hyperlinks(1).screentip = milestoneinfo
    End If

 testit39 = milestonesymbol
 End Function

Answer 1

Following VBA Sub code snippet allows adding new Hyperlink, or editing existing in a specified sample cell "A1" (non-essential part of your code has been removed for better clarity):

Public Sub AddOrEditHyperlink(milestonesymbol As String)
    Dim rng As Range, milestoneinfo As String
    'test range
    Set rng = Range("A1")
    'sample properties
    milestoneinfo = "info"
    'if Hyperlink exists, display "Edited"
    If rng.Hyperlinks.Count > 0 Then
        rng.Hyperlinks(1).Address = ""
        rng.Hyperlinks(1).ScreenTip = milestoneinfo
        rng.Hyperlinks(1).TextToDisplay = "Edited Hyperlink"
    Else 'if Hyperlink does not exist, add and display "New"
        rng.Hyperlinks.Add _
        Anchor:=rng, _
        Address:="", _
        ScreenTip:=milestoneinfo, _
        TextToDisplay:="New Hyperlink"
    End If
End Sub

You can call this Sub from the Function that you can define (UDF) corresponding to the rest of business logic of your project (which is a bit unclear articulated):

Public Function testit39() As String
    Application.Volatile
    Dim rng As Range, milestoneinfo As String, milestonesymbol As String
    Call AddOrEditHyperlink("some Symbol")
    testit39 = milestonesymbol
End Function

Hope this will help. Best regards

Answer 2

I have found a way that is not complicated thanks to this wonderful tutorial..

http://optionexplicitvba.blogspot.co.uk/2011/04/rollover-b8-ov1.html

So essentially you put it in a hyperlink and you're free to do as you please..

=hyperlink(testit39(), "Wahoo it works!")

Answer 3

UDFs (User-defined functions) are only allowed to return a value, they may not e.g. affect other cells or do other manipulations.
When you single-step through your code, you'll see that it aborts on the ...Hyperlinks.Add-line (and returns an error value).