0

I am trying to write a function to find the most recently created table object in an excel workbook (or I guess worksheet would do).

Running the code below returns the following:

Run-time error '438': Object doesn't support this property or method

I don't understand why but it showing the error to be on line 12. I believe it to be in the "tbl.Created", however this is used on line 11 as well with no issue.

Sub TableWorx()
      ' Get the name of most recent Table (existing table on new sheet)
  Dim ws As Worksheet
  Dim tbl As ListObject
  Dim newestObjectCreatedTime As Date
  Dim eTableName As String
  newestObjectCreatedTime = #1/1/1900# 'this is Excel's date with serial no of 0
  
  For Each ws In ThisWorkbook.Worksheets
      For Each tbl In ws.ListObjects
        If tbl.Created > newestObjectCreatedTime Then
              newestObjectCreatedTime = tbl.Created
              eTableName = tbl.Name
          End If
      Next tbl
  Next ws
  MsgBox "The latest table object: " & eTableName
  'eventually will add code to set new table name, it may end up in a separate sub
End Sub

Any suggestions how to get this or an equivalent function working? I am feeling like an ignoramus here.

Attempted running the above code as a function in a module, as a part of an existing function int "ThisWorkbook", as an independent function in "ThisWorkbook".

keep coming back to this error:

Run-time error '438': Object doesn't support this property or method

braX
  • 11,506
  • 5
  • 20
  • 33
Phentius Khan
  • 5
  • 3

1 Answers1

1

As others have pointed out, the ListObject doesn't have a .Created method nor property, so to directly get what you want isn't possible.

However, with a few assumptions (limitations) you can still figure this out. Every time a table is added to a workbook, it gets a default name in the format "Table#". # is the number of the table. So the first table in a workbook has the default name of Table1, the second has the default name of Table2, and so on. So, if all of the tables in a workbook are using their default names, then the table with the largest number in its name is the last one created. (Note that if you create a table, and then delete it, the next table will still increment its name. For example, if I have two tables in a workbook, and delete Table1, the next table I create will have the name Table3.)

So:

  • Iterate through all sheets
  • Get the last ListObject on each sheet
  • Track the table numbers
  • Return the largest (newest) table
Private Function GetLastTable() As ListObject

    ' Purpose:
    ' Return the most recently created Table, assuming all Tables are using default names
    
    Dim largestTableNumber As Long, tableName As String, tableNumber As Long, lastTable As ListObject
    
    ' Iterate through all worksheets
    Dim ii As Long
    For ii = 1 To ThisWorkbook.Sheets.Count
        
        ' The last object in the ListObjects is the most recently created table
        With ThisWorkbook.Sheets(ii)
            tableName = .ListObjects(.ListObjects.Count).Name
        End With
        
        ' Get the table's default number
        tableNumber = Replace(tableName, "Table", "")
        
        ' If this number is larger than the one in storage, this table was created more recently
        If tableNumber > largestTableNumber Then
            largestTableNumber = tableNumber
            Set lastTable = ThisWorkbook.Sheets(ii).ListObjects("Table" & tableNumber)
        End If
        
    Next ii

    Set GetLastTable = lastTable

End Function

Of course, error handling has been omitted here and the whole thing is based on some potentially prohibitive assumptions, but it's the best way I can think to try to get this info.

TehDrunkSailor
  • 633
  • 4
  • 11