vba excel-应用程序或面向对象的错误，=超链接

Question

我在Excel 2016中的基于VBA的超链接有问题。我想在所有其他床单前添加"导航"表，但我对" =超链接"有问题。我的代码如下：

Dim wbBook As workbook
Dim wsActive As Worksheet
Dim wsSheet As Worksheet
Dim lnRow As Long
Dim lnPages As Long
Dim lnCount As Long
Dim temp As Variant
Set wbBook = ActiveWorkbook
wbBook.Sheets.Add(Before:=Worksheets(1)).Name = "Navigation"
Set wsActive = wbBook.ActiveSheet
With wsActive
    .Name = "Navigation"
    With .Range("A1:A1")
        .Value = VBA.Array("Mitarbeiter")
        .Font.Bold = True
    End With
End With
lnRow = 2
lnCount = 1
For Each wsSheet In wbBook.Worksheets
    If wsSheet.Name <> wsActive.Name Then
        wsSheet.Activate
        With wsActive
            Worksheets("Navigation").Cells(lnRow, 1).Formula = _
            "=HYPERLINK(" & Chr(34) & "#" & "'" & wsSheet.Name & "'" & "!A" & lnRow & Chr(34) & ";" & Chr(34) & wsSheet.Name & Chr(34) & ")"      
        End With
        lnRow = lnRow + 1
        lnCount = lnCount + 1
    End If
Next wsSheet

我遇到的问题是，当我在超链接前添加一个" ="时，错误" anwendungs- oder objektorientierter fehler"（应用程序或面向对象的错误）会弹出。如果我在没有" ="的情况下运行宏，则该程序有效，但我必须手动在导航表中添加方程符号。

提前欢呼！

Answer 1

由于您已经在使用VBA，为什么不添加超链接的VBA功能（使用.Hyperlinks.Add）。

您可以在MSDN上阅读更多信息

我减少了ActiveSheet的使用和Activate。

代码

Option Explicit
Sub TestHyperlink()
Dim wbBook As Workbook
Dim wsActive As Worksheet
Dim wsSheet As Worksheet
Dim lnRow As Long
Dim lnPages As Long
Dim lnCount As Long
Dim temp As Variant
Set wbBook = ActiveWorkbook
Set wsActive = wbBook.Sheets.Add(Before:=Worksheets(1))
With wsActive
    .Name = "Navigation"
    With .Range("A1:A1")
        .Value = VBA.Array("Mitarbeiter")
        .Font.Bold = True
    End With
End With
lnRow = 2
lnCount = 1
For Each wsSheet In wbBook.Worksheets
    If wsSheet.Name <> wsActive.Name Then
        With wsSheet
            .Hyperlinks.Add Anchor:=Worksheets("Navigation").Range("A" & lnRow), _
                Address:="", SubAddress:="'" & .Name & "'!" & .Range("A" & lnRow).Address, _
                TextToDisplay:="#" & .Name
        End With
        lnRow = lnRow + 1
        lnCount = lnCount + 1
    End If
Next wsSheet
End Sub

Answer 2

尝试使用逗号将公式的参数分开而不是半隆。我认为将公式迫使公式忽略了本地化。

    With wsActive
        Worksheets("Navigation").Cells(lnRow, 1).Formula = _
        "=HYPERLINK(" & Chr(34) & "#" & "'" & wsSheet.Name & "'" & "!A" & lnRow & Chr(34) & "," & Chr(34) & wsSheet.Name & Chr(34) & ")"      
    End With