run time error 438 at ListObjects.Add method only on older version of Excel

Question

It has been around 2 weeks since I started working on excel vba. I have a sub which converts a range of data into a table:

Sub RangeToTable(fileName, sheetname, rng, tblNm)
    With Sheets(sheetname)
       .ListObjects.Add(xlSrcRange, .Range(rng), , xlYes).Name = tblNm
       .ListObjects(tblNm).ShowHeaders = False
       .ListObjects(tblNm).TableStyle = "TableStyleLight15"
    End With
End Sub

This sub works fine on my development machine (Win 7, Office 2007). But on our lab computer which is running Win XP and office 2000, I am getting a run time error 438 object does not support this property or method at Add method. I have searched around for a solution and on MS website one of the causes of this problem is given as version mismatch, which is true in my case. Can you guys please let me know a good solution for my problem? Are there any work arounds? Does this mean that any macro which deals with tables (ListObjects), I won't be able to run on an excel version which does not support tables? Your help is appreciated, Thanks, DD.

I believe this type of Table was introduced in Excel 2007......You would need a different macro for earlier versions. — Gary's Student

Rik Sportel Rik Sportel · Accepted Answer · 2016-02-12T16:04:29

Unfortunately this is not going to work. ListObjects as they are today were implemented in Excel 2010 and exist from 2003 onwards.

For Excel 2010 and 2013 see: https://msdn.microsoft.com/en-us/library/office/ff195678(v=office.14).aspx

For 2007 see: https://msdn.microsoft.com/en-us/library/bb223938(v=office.12).aspx

For 2003 see: https://msdn.microsoft.com/en-us/library/office/aa174247(v=office.11).aspx

For 2000 and older: Doesn't exist so you need to code manually.

Chip Pearson confirms this here: http://www.excelforum.com/excel-general/485029-listobject-in-excel-2000-a.html

run time error 438 at ListObjects.Add method only on older version of Excel

1 Answers