超链接代码显示下标超出范围错误VBA Excel [英] Hyperlink code shows Subscript out of range error vba excel

问题描述

我有一个超链接vba代码，单击该代码可激活一个隐藏的工作表.但是由于某种原因，每次我跑步时，我都会收到一条错误消息

I have a hyperlink vba code that activates a hidden worksheet when clicked. But for some reason every time I run I get an error saying

下标超出范围"

"Subscript out of Range"

在代码Worksheets(MySheet).Visible = xlSheetVisible

代码是

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Linkto = Target.SubAddress
Name = InStr(1, Linkto, "!")
If Name > 0 Then
    MySheet = Left(Linkto, Name - 1)
    Worksheets(MySheet).Visible = xlSheetVisible
    Worksheets(MySheet).Select
    MyAddr = Mid(Linkto, Name + 1)

    Worksheets(MySheet).Range(MyAddr).Select
 End If
End Sub

推荐答案

您应避免使用诸如名称"之类的变量名，它们可以是保留字.在这种情况下，我想Name将引用当前的工作表(设置了超链接的工作表)

You should avoid using variable names such as "Name" which could be reserved words. In this particular case, I guess Name will refer to the current worksheet (The one on which the Hyperlink is set)

通过将变量名称"替换为例如"myName"来尝试相同的代码

Try the same code by replacing the variable "Name" by, for example "myName"

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

  Linkto = Target.SubAddress
  myName = InStr(1, Linkto, "!")
  If myName > 0 Then

      MySheet = Left(Linkto, myName - 1)
      Worksheets(MySheet).Visible = xlSheetVisible
      Worksheets(MySheet).Select
      MyAddr = Mid(Linkto, myName + 1)
      Worksheets(MySheet).Range(MyAddr).Select

  End If
end sub

以及作为建议

• 在VBA开头使用"OPTION EXPLICIT"来强制您显式声明变量
• 使用与内容一致的变量名.您将变量称为名称"，但实际上是在其中将惊叹号的位置存储在超链接目标中……有点令人困惑……

希望可以解决您的问题...

Hope that fixes your issue...

这篇关于超链接代码显示下标超出范围错误VBA Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！