User-Defined Function to Get Hyperlink Address from Cell

Question

I am trying to fill the contents of a cell using my own user-defined function. In particular, I am trying to make the contents of the cell be the URL address of a hyperlink cell that is in the same worksheet.

I keep getting a #VALUE! error. My function looks like this:

Function GetAddress(myCell As Range) As String
     Dim temp As String
     temp = myCell.Hyperlinks(1).Address
     GetAddress = temp
End Function

I have tried several variants that don't use temp but directly assign the GetAddress output and this still does not work. If I return myCell.Address it gives me the cell Address correctly but once I try to get the Hyperlink address it has issues. Every example I've seen to do this fails.

Does anyone know how this can be done?

Answer 1

Your code works fine, except that you just have to force the UDFs to recalculate, otherwise its result doesn't get updated. If you want it to recalculate automatically, you can use Application.Volatile (and you can drop the unnecessary temp):

Function GetAddress(myCell As Range) As String
    Application.Volatile
    GetAddress = myCell.Hyperlinks(1).Address
End Function

Now you won't have to manually force it to recalculate. Though beware that having many calls to volatile functions can really slow things down.

Example of doing it manually:

Now write something in A1:

Now add a link to it:

Function doesn't get recalculated... Force it to recalculate e.g. by clicking on the cell, pressing F2, and then Enter: