如何通过VBA查找Excel中创建时间最晚的ListObject表格？

错误原因 #

Excel的ListObject（表格）对象不存在Created属性，你代码中调用tbl.Created属于访问未定义的对象属性，因此触发Run-time error '438'。

解决方案 #

由于Excel原生未提供表格的创建时间属性，以下是两种可行的替代实现方案：

方案1：通过表格关联命名区域判断（仅适用于未修改过表格名称的场景） #

Excel的命名区域自带Created属性，而新建表格会自动生成同名的命名区域，可借此间接获取表格的创建时间：

Sub TableWorx()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim newestObjectCreatedTime As Date
    Dim eTableName As String
    Dim nm As Name
    
    newestObjectCreatedTime = #1/1/1900#
    
    For Each ws In ThisWorkbook.Worksheets
        For Each tbl In ws.ListObjects
            ' 定位表格对应的命名区域
            On Error Resume Next
            Set nm = ThisWorkbook.Names(tbl.Name)
            On Error GoTo 0
            
            If Not nm Is Nothing Then
                If nm.Created > newestObjectCreatedTime Then
                    newestObjectCreatedTime = nm.Created
                    eTableName = tbl.Name
                End If
            End If
        Next tbl
    Next ws
    
    If eTableName <> "" Then
        MsgBox "The latest table object: " & eTableName
    Else
        MsgBox "No tables found in the workbook."
    End If
End Sub

注意：如果表格名称被手动修改过，对应的命名区域名称会与表格名称不一致，此方法会失效。

方案2：提前记录表格创建时间（准确可靠，推荐） #

在创建表格时，给工作表添加自定义属性记录表格的创建时间，后续查询时直接读取该属性：

1. 先实现带时间记录的表格创建过程：

Sub CreateTableWithCreatedTime(rng As Range, tblName As String)
    Dim tbl As ListObject
    Set tbl = rng.Worksheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
    tbl.Name = tblName
    
    ' 给工作表添加自定义属性，绑定表格创建时间
    tbl.Worksheet.CustomProperties.Add Name:="Table_" & tblName & "_Created", Value:=Now()
End Sub

2. 修改查询代码读取自定义属性：

Sub TableWorx()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim newestObjectCreatedTime As Date
    Dim eTableName As String
    Dim cp As CustomProperty
    Dim currentTime As Date
    
    newestObjectCreatedTime = #1/1/1900#
    
    For Each ws In ThisWorkbook.Worksheets
        For Each tbl In ws.ListObjects
            ' 查找对应表格的自定义时间属性
            On Error Resume Next
            Set cp = ws.CustomProperties("Table_" & tbl.Name & "_Created")
            On Error GoTo 0
            
            If Not cp Is Nothing Then
                currentTime = CDate(cp.Value)
                If currentTime > newestObjectCreatedTime Then
                    newestObjectCreatedTime = currentTime
                    eTableName = tbl.Name
                End If
            End If
        Next tbl
    Next ws
    
    If eTableName <> "" Then
        MsgBox "The latest table object: " & eTableName & vbCrLf & "Created at: " & newestObjectCreatedTime
    Else
        MsgBox "No tables with creation time records found."
    End If
End Sub

对于已存在的表格，可手动执行一次属性添加操作，即可纳入查询范围。

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