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

score 2 · Accepted Answer · answered Dec 21 '14 at 07:53

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!")

score 1 · Answer 2 · edited May 23 '17 at 11:53

1

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

edited May 23 '17 at 11:53

Community

1
1

answered Dec 21 '14 at 01:05

KekuSemau

6,830
4
24
34

it is actually possibly but requires complex workarounds. http://stackoverflow.com/questions/8520732/i-dont-want-my-excel-add-in-to-return-an-array-instead-i-need-a-udf-to-change – brettdj Dec 21 '14 at 02:59
Not complex if you use the hyperlink function.. http://optionexplicitvba.blogspot.co.uk/2011/04/rollover-b8-ov1.html – Wickey312 Dec 21 '14 at 07:52

Alexander Bell · Answer 3 · 2014-12-21T02:08:39.263

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

Add Hyperlink in VBA UDF

3 Answers3

Linked

Related