1

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.

Community
  • 1
  • 1
user5916275
  • 11
  • 1

1 Answers1

0

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

Rik Sportel
  • 2,661
  • 1
  • 14
  • 24
  • @Jeeped@Rik Sportel, thank you guys for your answers...I did use your answers to back me up in suggesting my manager that this is not going to work. Updating our lab computers seems to be the only solution for us. Thanks! - DD – user5916275 Feb 13 '16 at 01:03
  • Well it is possible to code the same thing against 2000, but it is so outdated that updating the software (and indeed possibly the computers) is indeed a good idea. – Rik Sportel Feb 15 '16 at 19:35