用户定义函数，从单元格获取超链接地址

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.

我正在尝试使用我自己的用户定义函数填充单元格的内容。特别地，我试图使单元格的内容成为同一个工作表中的超链接单元格的URL地址。

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.

我已经尝试了几个不使用temp但直接分配GetAddress输出的变体，但这仍然不起作用。如果我返回myCell。地址给我正确的单元地址，但一旦我试图获取超链接地址，它有问题。我看到的每个例子都失败了。

Does anyone know how this can be done?

有人知道这是怎么做到的吗?

1 个解决方案

#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):

您的代码可以正常工作，但是您只需要强制udf重新计算，否则它的结果不会被更新。如果希望它自动重新计算，可以使用Application。挥发物(你可以去掉不必要的温度):

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:

现在在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:

函数不会重新计算……强制它重新计算，例如点击单元格，按F2，然后输入: