Excel VBA创建归档文件后，表格添加文件链接遇编译错误求助

我正在创建一个维护数据工作簿，目前设有一个用于录入用户刚完成的维护数据的工作表，数据会同步至"Database"工作表，此外还有一个"Archives"工作表。
"Archives"工作表中的按钮可复制"Database"工作表并将其另存为指定文件夹中的独立工作簿，文件名对应工作表涵盖的日期范围。我希望该按钮同时能在"Archives"工作表的表格中新增一行，记录归档创建日期、归档涵盖的日期范围以及打开新创建归档文档的链接，但在添加文件链接时遇到问题。
注：这是我第二次使用VBA，对相关术语了解不多。

当前代码：

Sub CreateArchive()

'This module does multiple things.
'1 created an archived file and saves it in the Inspection Archive folder
'2 adds an entry into the archive table

                'Creating the file

    'file name dims
    Dim startdate As String
    Dim enddate As String
    Dim archivefilename As String
    
    'archive file path dim
    Dim path As String
    
    'archive data source dims
    Dim sourceWB As Workbook
    Dim sourceWS As Worksheet
    
    'Storing source workbook/sheet values now as it is currently the active workbook and sheet.
    Set sourceWB = ThisWorkbook
    Set sourceWS = sourceWB.Sheets("Database")
    
    startdate = sourceWS.Cells(2, 38)
    enddate = sourceWS.Cells(4, 38)
    
    'Copying data to transfer
    sourceWS.Copy
    

    'Naming archive workbook
    archivefilename = ("Site Inspections From " + startdate + " to " + enddate + ".xlsx")
    
    'Setting path to save the archive
    path = "savefilepath"
    
    'creating a new workbook off the copied data and saving it
    ActiveWorkbook.SaveAs path & "\" & archivefilename & ".xlsx"
    
    'Clearing clipboard
    Application.CutCopyMode = False
    
    
    'closes the archived workbook automatically
    Workbooks(archivefilename).Close savechanges:=True
    
    
    
                    'Adding entry to archive table
                    
    Dim newRow As ListRow
    Dim rowNum As Integer
    rowNum = ActiveWorkbook.Worksheets("Archives").ListObjects("ArchiveTable").ListRows.Count
    Set newRow = ActiveWorkbook.Worksheets("Archives").ListObjects("ArchiveTable").ListRows.Add(rowNum)
        newRow.Range(1) = Hyperlink("savefilepath" + archivefilename + ".xlsx", "Open File")
        newRow.Range(2) = Date
        newRow.Range(3) = (startdate + " to " + enddate)
       
    
End Sub

运行时出现编译错误：Sub or function not defined，错误指向Hyperlink。

错误原因 #

VBA中不能直接用Hyperlink()函数给单元格赋值，要通过Hyperlinks.Add方法来添加超链接，这是VBA操作单元格超链接的标准方式。

修正后的代码 #

Sub CreateArchive()

'This module does multiple things.
'1 created an archived file and saves it in the Inspection Archive folder
'2 adds an entry into the archive table

                'Creating the file

    'file name dims
    Dim startdate As String
    Dim enddate As String
    Dim archivefilename As String
    
    'archive file path dim
    Dim path As String
    Dim fullFilePath As String '存储完整文件路径，避免重复拼接
    
    'archive data source dims
    Dim sourceWB As Workbook
    Dim sourceWS As Worksheet
    Dim archivesWS As Worksheet '提前绑定Archives工作表对象
    
    'Storing source workbook/sheet values now as it is currently the active workbook and sheet.
    Set sourceWB = ThisWorkbook
    Set sourceWS = sourceWB.Sheets("Database")
    Set archivesWS = sourceWB.Sheets("Archives") '初始化Archives工作表
    
    startdate = sourceWS.Cells(2, 38)
    enddate = sourceWS.Cells(4, 38)
    
    'Copying data to transfer
    sourceWS.Copy
    

    'Naming archive workbook
    archivefilename = "Site Inspections From " & startdate & " to " & enddate & ".xlsx"
    
    'Setting path to save the archive
    path = "savefilepath"
    fullFilePath = path & "\" & archivefilename '拼接完整路径
    
    'creating a new workbook off the copied data and saving it
    ActiveWorkbook.SaveAs fullFilePath, FileFormat:=xlOpenXMLWorkbook
    
    'Clearing clipboard
    Application.CutCopyMode = False
    
    'closes the archived workbook automatically
    ActiveWorkbook.Close savechanges:=True '直接用ActiveWorkbook更可靠，避免文件名识别问题
    
    
    
                    'Adding entry to archive table
                    
    Dim newRow As ListRow
    Set newRow = archivesWS.ListObjects("ArchiveTable").ListRows.Add '默认在表格末尾新增行
    
    '添加超链接到新行的第一列
    archivesWS.Hyperlinks.Add _
        Anchor:=newRow.Range(1), _
        Address:=fullFilePath, _
        TextToDisplay:="Open File"
    
    newRow.Range(2) = Date
    newRow.Range(3) = startdate & " to " & enddate
       
    
End Sub

关键修正点 #

• 用Hyperlinks.Add替代Hyperlink()：这是VBA添加单元格超链接的正确方法，需要指定锚点（目标单元格）、文件路径和显示文本。
• 新增fullFilePath变量：统一存储完整文件路径，减少重复拼接操作，降低出错概率。
• 关闭归档文件改用ActiveWorkbook.Close：避免因文件名含特殊字符导致的识别失败问题。
• 简化新行添加逻辑：ListRows.Add默认会在表格末尾插入新行，无需手动计算行数。
• 用&替代+拼接字符串：VBA中推荐用&处理字符串拼接，+在遇到非字符串类型时容易出错。

内容的提问来源于stack exchange，提问作者Bryce