vba 在excel中创建自定义超链接函数

Creating a custom hyperlink function in excel

提问by Morglor

I have searched far and wide, but can't find an answer to this simple question. I want to make a custom function in excel which will create a hyperlink.

我已经进行了广泛的搜索，但找不到这个简单问题的答案。我想在 excel 中创建一个自定义函数，它将创建一个超链接。

Excel has a built in hyperlink function that works like this:

Excel 有一个内置的超链接函数，其工作方式如下：

=Hyperlink(link_location, display_text)

I want to create a function called CustomHyperlink which takes one parameter, and returns a hyperlink to a google query with that parameter. Just for the sake of the question, lets assume that the passed parameter is a alphanumeric string, with no spaces.

我想创建一个名为 CustomHyperlink 的函数，它接受一个参数，并使用该参数返回一个指向 google 查询的超链接。为了这个问题，让我们假设传递的参数是一个字母数字字符串，没有空格。

Essentially, calling

本质上，调用

=CustomHyperlink("excel") 

should be the same as calling

应该和调用一样

=Hyperlink("http://www.google.com/search?q=excel", "excel")

This seems like such a simple task, but I absolutely cannot find a way to make this function.

这似乎是一项简单的任务，但我绝对找不到实现此功能的方法。

Can anyone offer some quick help?

任何人都可以提供一些快速帮助吗？

回答by chris neilsen

I can offer a partialsolution, one that will updatean existing hyperlink. This only makes sence if you are using it like, say

我可以提供一个部分解决方案，一个将更新现有超链接的解决方案。这仅在您使用它时才有意义，例如

CustomHyperlink(A1)

were A1contains the required serch term

是A1包含所需serch主页项

To use,

要使用，

1. enter your UDF formula in a cell, eg =CustomHyperlink(A1)
2. create a hyperlink on the cell (right click, Hyperlink...) . This can be anyhyperlink, valid or invalid
3. put the required search term in the referenced cell, eg in A1put excel

1. 在单元格中输入您的 UDF 公式，例如 =CustomHyperlink(A1)
2. 在单元格上创建一个超链接（右键单击，超链接...）。这可以是任何超链接，有效或无效
3. 将所需的搜索词放入引用的单元格中，例如A1放入excel

When the UDF runs it will updatethe hyperlink to Google the entered search term

当 UDF 运行时，它将更新超链接到 Google 输入的搜索词

Function CustomHyperlink(Term As String) As String
    Dim rng As Range

    Set rng = Application.Caller
    CustomHyperlink = Term

    If rng.Hyperlinks.Count > 0 Then
        rng.Hyperlinks(1).Address = "http://www.google.com/search?q=" & Term
    End If
End Function

回答by chris neilsen

In VBA editor you can use

在 VBA 编辑器中，您可以使用

ThisWorkbook.FollowHyperlink Address:=(strWebsite), NewWindow:=True

Which will take you to that specific website, and just build a function around that to navigate you to the site you need.

这将带你到那个特定的网站，然后围绕它构建一个功能来导航到你需要的网站。

回答by markblandford

Nice idea although this isn't possible.

好主意，虽然这是不可能的。

You seem to want to have the formula of the cell as one thing (your custom function call) and yet have the value as another (the hyperlink / URL) which simply isn't possible.

您似乎希望将单元格的公式作为一件事（您的自定义函数调用），但将值作为另一件事（超链接/URL），这是不可能的。

The correct way through VBA to add a hyperlink is to use the Hyperlinks property but it is not possible to call this property, through a Worksheet UDF (because of the reason above).

通过 VBA 添加超链接的正确方法是使用 Hyperlinks 属性，但不可能通过工作表 UDF 调用此属性（由于上述原因）。

What is wrong with just using the the built-in =Hyperlink() worksheet function? You could effectively parameterise your URL as follows (where cell A1 = Excel):

仅使用内置的 =Hyperlink() 工作表函数有什么问题？您可以按如下方式有效地参数化您的 URL（其中单元格 A1 = Excel）：

=HYPERLINK("http://www.google.com/search?q="&A1)

回答by Lunatik

You can't do this directly for the reasons creamyegg suggests, but there is a way to achieve the functionality albeit with a bit of a performance consideration.

由于 creamyegg 建议的原因，您不能直接执行此操作，但是有一种方法可以实现该功能，尽管需要考虑一些性能。

You could use the Worksheet_Changeevent to track for the presence of your UDF then process the hyperlink addition there.

您可以使用该Worksheet_Change事件来跟踪您的 UDF 的存在，然后在那里处理超链接添加。

You would need to set up an empty function to allow this to happen, otherwise Excel will throw an error whenever you entered =CustomHyperlink...in a cell.

您需要设置一个空函数来允许这种情况发生，否则只要您=CustomHyperlink...在单元格中输入，Excel 就会抛出错误。

The below should work, not really had time to test.

下面应该工作，没有时间去测试。

Private Sub worksheet_change(ByVal target As Range)
    Dim SearchValue As String
    If LCase(Left(target.Formula, 16)) = "=customhyperlink" Then
        SearchValue = Mid(target.Formula, 19, Len(target.Formula) - 20)
        target.Value = SearchValue
        target.Hyperlinks.Add target, "http://www.google.com/search?q=" & SearchValue, ,     "Search Google for " & SearchValue, SearchValue
    End If
End Sub

The performance consideration is of course the volatile Worksheet_Changeevent as this can really kill large, complex workbooks.

性能考虑当然是 volatileWorksheet_Change事件，因为这确实会杀死大型、复杂的工作簿。