在excel中创建自定义超链接功能 [英] Creating a custom hyperlink function in excel

问题描述

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

Excel具有内置的超链接功能，如下所示：

  =超链接（link_location，display_text）
  

我想创建一个名为CustomHyperlink的函数，它使用一个参数，并返回一个超链接到具有该参数的google查询。只是出于问题的原因，我们假设传递的参数是一个字母数字字符串，没有空格。

本质上，调用

  = CustomHyperlink（excel）
  

应该与调用

  =超链接（http://www.google.com/search?q=excel ，excel）
  

这似乎是一个这么简单的任务，但我绝对找不到方法

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

解决方案

p>我可以提供一个部分解决方案，一个将更新现有超链接的解决方案。如果您使用的话，这只会使sence成为例如

  CustomHyperlink（A1）
  

A1 包含所需的服务条款

要使用

1. 在单元格中输入您的UDF公式，例如 = CustomHyperlink（A1）


2. 在单元格上创建一个超链接（右键单击Hyperlink ...）。这可以是任何超链接，有效或无效


3. 将所需的搜索字词放在引用的单元格中，例如在 A1 put excel

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

 函数CustomHyperlink（Term As String）As String 
 Dim rng As Range 
 
设置rng = Application.Caller 
 CustomHyperlink = Term 
 
如果rng.Hyperlinks.Count> 0然后
 rng.Hyperlinks（1）.Address =http://www.google.com/search?q=&期限
结束如果
结束函数
  

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 has a built in hyperlink function that works like this:

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

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?

解决方案

I can offer a partial solution, one that will update an existing hyperlink. This only makes sence if you are using it like, say

CustomHyperlink(A1)

were A1 contains the required serch term

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 any hyperlink, valid or invalid
3. put the required search term in the referenced cell, eg in A1 put excel

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

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

这篇关于在excel中创建自定义超链接功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！