使用公式生成超链接时，VBA 无法打开 Excel 超链接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14422003/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA to open Excel hyperlink does not work when hyperlink generated with a formula
提问by Steve
There seems to be a bug with Excel hyperlinks which are generated with a formula. I'm using Excel 2010. I have a spreadsheet with cells containing URLs, and my goal is to do the following two things:
使用公式生成的 Excel 超链接似乎存在错误。我正在使用 Excel 2010。我有一个包含 URL 的单元格的电子表格，我的目标是做以下两件事：
- Turn these cells into hyperlinks.
- Create a keyboard shortcut to open these hyperlinks so I don't have to use the mouse.
- 将这些单元格变成超链接。
- 创建一个键盘快捷键来打开这些超链接，这样我就不必使用鼠标了。
To do #1, initially I just used the function =HYPERLINK(). So, my URLs are in column A, and I used this formula to make hyperlinks in column B.
要做#1，最初我只是使用了函数=HYPERLINK(). 因此，我的 URL 位于 column 中A，我使用此公式在 column 中创建超链接B。
To do #2, I created the following macro which should open the hyperlink with the keyboard shortcut Ctrl+H:
要做#2，我创建了以下宏，它应该使用键盘快捷键Ctrl+打开超链接H：
Sub Open_Hyperlink()
'
' Open_Hyperlink Macro
'
' Keyboard Shortcut: Ctrl+h
'
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End Sub
The problem is that this macro only seems to work on hyperlinks which are not created using a formula. For example, if I just type into a cell http://www.google.com, Excel will automatically make this a hyperlink and the keyboard shortcut macro works, where it doesn't with formula generated hyperlinks.
问题是这个宏似乎只适用于不是使用公式创建的超链接。例如，如果我只是在一个单元格中输入http://www.google.com，Excel 会自动将其设为超链接，并且键盘快捷键宏可以工作，但它不会使用公式生成的超链接。
I've also noticed that when I right click on formula generated hyperlinks, there is no option in the drop-down menu to open the hyperlink, yet there is that option when right clicking on hyperlinks not generated by a formula.
我还注意到，当我右键单击公式生成的超链接时，下拉菜单中没有打开超链接的选项，但是右键单击不是由公式生成的超链接时有该选项。
I've found the following workaround. Rather than generate hyperlinks using a formula, I used a macro which I found here.
我找到了以下解决方法。我没有使用公式生成超链接，而是使用了一个我在这里找到的宏。
Sub HyperAdd()
'Converts each text hyperlink selected into a working hyperlink
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell
End Sub
I'm able to use the keyboard shortcut to open the hyperlinks generated with this macro. I'm wondering if anyone has or had a similar problem, and why the formula generated hyperlinks are not working for me. I would prefer to use formulas to make hyperlinks in the future, since it is simpler, so if anyone knows of a way to avoid using a macro to make hyperlinks, I'd really appreciate it.
我可以使用键盘快捷键打开使用此宏生成的超链接。我想知道是否有人遇到或遇到过类似的问题，以及为什么公式生成的超链接对我不起作用。我更愿意在未来使用公式来制作超链接，因为它更简单，所以如果有人知道避免使用宏制作超链接的方法，我会非常感激。
回答by Peter L.
I'm wondering if anyone has had a similar problem, and why the formula generated hyperlinks are not working for me.
我想知道是否有人遇到过类似的问题，以及为什么公式生成的超链接对我不起作用。
Alas, this seems to be painful truth: Excel does not add to Hyperlinkscollection formula-generated links - below is the screen from the debugger which is pointed to =HYPERLINK("http://www.google.com/";"Google"):
唉，这似乎是一个痛苦的事实：Excel 不会添加到Hyperlinks集合公式生成的链接 - 下面是调试器指向的屏幕=HYPERLINK("http://www.google.com/";"Google")：
I'm not sure whether this is a deliberate implementation or a bug, but yes, formula-generated links may NOT be opened using Hyperlinks().Followmethod.
我不确定这是故意实现还是错误，但是是的，可能无法使用Hyperlinks().Follow方法打开公式生成的链接。
However, if you're going to use keyboard shortcut for links opening, just use the following code - it will automatically convert to clickable link selected cell text and open it:
但是，如果您要使用键盘快捷键打开链接，只需使用以下代码 - 它会自动转换为可点击链接选定的单元格文本并打开它：
Sub Open_Hyperlink()
Selection.Hyperlinks.Add Anchor:=Selection, Address:=Selection.Formula
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End Sub
Just assign any shortcut and you're ready to go) Here is the sample: https://www.dropbox.com/s/d4cie7lun22quma/FollowLinks.xlsm
只需分配任何快捷方式，您就可以开始了）这是示例：https: //www.dropbox.com/s/d4cie7lun22quma/FollowLinks.xlsm
Hope that's somehow helpful. Good luck!
希望这在某种程度上有所帮助。祝你好运！
