Function returns #VALUE when it should return an Hyperlink

Question

I built a function :

Function GenerateHyperlink(NameFile as string)
 MyStr = "C:\Test\2. Test\" & NameFile
 With ActiveSheet
    .Hyperlinks.Add anchor:=Range(ThisWorkbook.FullName & "#'" & ActiveCell.Parent.Name & "'!" & ActiveCell.Address), Address:=MyStr, TextToDisplay:="Open File"
 End With
GenerateHyperlink = MyStr
End Function

This when I type in cell :

=GenerateHyperlink(A2)

It does put in cell : #VALUE!

and When I type in immediate window :

? GenerateHyperLink("Test.pdf")

It does display the hyperlink in cell.

What am I missing ?

Edit after comments I did the following

Function GenerateHyperlink(NameFile as string) as string
     MyStr = "C:\Test\2. Test\" & NameFile
     ActiveCell.Parent.Evaluate "DefineHy(" & ActiveCell.Address & "," & MyStr & "," & "This is a test" & ")"
     GenerateHyperlink = MyStr
End Function

sub DefineHy(ThisCell as string, ThisPath as string, ThisString as string)
With ActiveSheet
        .Hyperlinks.Add anchor:=Range(ThisCell), Address:=ThisPath, TextToDisplay:=ThisString
    End With
 End With
End Sub

Which does return a String and not an HyperLink

Functions called from the sheet will only return values. They will not create hyperlinks. There is a hacky way to do this using Application.Evaluate in the function to call a sub that will do what you want. See: https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet — Scott Craner, Dec 14 '21 at 16:15
@ScottCraner Sorry, I don't understand. Should I call this function in a sub with Application. Evaluate before ? — TourEiffel, Dec 14 '21 at 16:16
You cannot do this with a UDF. A Function called from the worksheet will only return a value. It will not change the format, create hyperlink, create another formula or anything other than return a value to the cell. — Scott Craner, Dec 14 '21 at 16:17
@ScottCraner It now return a string and still not an hyperlink. — TourEiffel, Dec 14 '21 at 16:25
You need the extra quotation marks in the Evaluate string. `"DefineHy(""" & ActiveCell.Address(False, False) & """,""" & MyStr & """)"` and you should move the TextToDisplay part into the return value of `GenerateHyperlink`. — Toddleson, Dec 14 '21 at 16:45
also by having `GenerateHyperlink = MyStr` as the last line you would be overwriting the hyperlink with a sound value. — Scott Craner, Dec 14 '21 at 16:51

Function returns #VALUE when it should return an Hyperlink

0 Answers0