如何使用Excel VBA引用形状的超链接 [英] How do I refer to a shape's hyperlinks using Excel VBA

问题描述

我有一个电子表格，其中包含几个超链接，其中一些来自形状.我试图遍历它们，以查看它们每个指向的位置，以便稍后删除其中的一些.对于单元格中包含的超链接，以下循环有效:

I have a spreadsheet which contain several hyperlinks, some of which are from shapes. I am trying to loop through them, to see where each of them point in order to later remove some of them. For the hyperlinks contained in cells the following loop has worked:

Sub a()
  Dim ws As Worksheet, hl As Hyperlink, o As Shape

  For Each ws In Worksheets
    For Each hl In ws.Hyperlinks
      Debug.Print hl.Address
    Next
  Next
End Sub

但这似乎跳过了所有源自形状或其他对象的超链接.

But that seems to skip all the hyperlinks originating from shapes or other objects.

我还有什么方法可以遍历这些吗?我尝试过类似的东西:

Is there any way I can loop through those as well? I have tried stuff like:

Sub a()
  Dim ws As Worksheet, hl As Hyperlink, o As Shape


  For Each ws In Worksheets
    For Each o In ws.Shapes
      For Each hl In o.Hyperlinks
        Debug.Print hl.Address
      Next
    Next
  Next
End Sub

但这在 debug.print 行上给了我运行时错误91.谷歌搜索给我什么.那么，你们中的任何一个都知道如何打印地址吗?

But that gives me a runtime error 91 on the debug.print line. Googling gives me nothing. So, have any of you got an idea of how to print the addresses?

推荐答案

Shape没有 .Hyperlinks 属性，只有一个 .Hyperlink 属性，您可以如果没有关联的超链接，它将从中得到一个错误，因此您需要一个错误处理程序.例如:

A Shape doesn't have a .Hyperlinks property, only a .Hyperlink one and you'll get an error from it if there is no associated hyperlink, so you need an error handler. For example:

        On Error Resume Next
        Set hl = o.Hyperlink
        On Error GoTo 0
        If Not hl Is Nothing Then 
            Debug.Print hl.Address
            set hl = Nothing
        End If

这篇关于如何使用Excel VBA引用形状的超链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！