Excel hyperlinks.add锚定语法/参数

Question

下面是我用来跟踪excel文档更改的一小段代码。我得到一个运行时错误'5‘“无效的过程调用或参数”在加粗的位。我认为问题出在Hyperlinks.Add锚的语法或参数上，因为当我转到下一行时，“锚”不会大写。我的参数和语法是否正确？

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sSheetName As String
 sSheetName = "1107"

If ActiveSheet.Name <> "LogDetails" Then
 Application.EnableEvents = False
 Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(1,0).Value = ActiveSheet.Name & " - " & Target.Address(0, 0)
 Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = OldValue
 Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Value
 Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Environ("username")
 Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Now
 **Sheets("LogDetails").Hyperlinks.Add anchor:=Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 5), Address:="", SubAddress:="'" & sSheetName & "'!" & OldAddress, TextToDisplay:=OldAddress**


 Sheets("LogDetails").Columns("A:D").AutoFit
 Application.EnableEvents = True
 End If
 End Sub

Answer 1

设置SubAddress时，请考虑删除撇号

使用Excel录制的示例：

ActiveSheet.Hyperlinks.Add anchor:=Selection, Address:="", SubAddress:= _
    "Sheet1!A1", TextToDisplay:="Sheet1!A1df"

代码

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

With Excel.Application
    .EnableEvents = False
End With

Dim sSheetName As String
    sSheetName = "1107"

    If ActiveSheet.Name <> "LogDetails" Then

        Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & " - " & Target.Address(0, 0)
        Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = OldValue
        Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Value
        Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Environ("username")
        Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Now

        Dim hlink_cell As Range
        Set hlink_cell = Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 5)

        Sheets("LogDetails").Hyperlinks.Add anchor:=hlink_cell, Address:="", SubAddress:=sSheetName & "!" & OldAddress, TextToDisplay:=OldAddress

        Sheets("LogDetails").Columns("A:D").AutoFit

    End If

With Excel.Application
    .EnableEvents = True
End With

End Sub