"Subscript out of range" error for ListObject

Question

I call a sub that select and convert ranges in multiple sheets into tables. It generates the tables and names them "SIS_1", "SIS_2" and so on. To use these tables in my current sub, I can declare and set table "SIS_1" in worksheets 3, but can't declare and set table "SIS_2" in worksheet 4 and so on. Does it have anything to do with the sheet code (highlighted in red)?

I can't seem to find what the issue is. If I manually rename the "SIS_2" in excel into "SIS_x", then rename it back to "SIS_2", it seems to solve the issue and I can then declare the variable as usual. I don't understand why it's like that.

Code i want to declare the ListObject

Public Sub ImportButton_Click()

    Dim totalSheets As Integer
    Dim startSheet As Integer, endSheet As Integer
    Dim masterSIS As ListObject, sourceSIS As ListObject
    Dim i As Integer

    Set masterSIS = Worksheets("MasterSIS").ListObjects("MasterSIS")

    totalSheets = ThisWorkbook.Worksheets.Count

    Call importSheets([filePath])

    startSheet = totalSheets + 1
    endSheet = ThisWorkbook.Worksheets.Count

    Call convertToTable(startSheet, endSheet, "SIS")

    For i = 1 To endSheet - totalSheets
        Set sourceSIS = Worksheets(startSheet).ListObjects("SIS_" & i)
        Call appendTable(masterSIS, sourceSIS)
        startSheet = startSheet + 1
    Next i

End Sub

For anyone having similar problem. Hope this helps.

Found the solution here https://www.dingbatdata.com/2017/11/24/referencing-listobjects-with-vba/.

To declare LinkObject, you need to do it like this Range("TableName").ListObject.It seems that 'there will be a VBA error if the sheet changes it’s position'.

Comments are not for extended discussion; this conversation has been [moved to chat](https://chat.stackoverflow.com/rooms/195744/discussion-on-question-by-afdzal-yunus-subscript-out-of-range-error-for-listob). — Bhargav Rao, Jun 29 '19 at 16:00

Andy Fazulus · Accepted Answer · 2019-06-30T19:55:44.840

0

Found the problem with the table name wrongly generated from the loop code in previous sub. Fixed it and the error disappear.

edited Jun 30 '19 at 19:55

answered Jun 29 '19 at 19:01

Andy Fazulus

35
2
9

See [this question](https://stackoverflow.com/questions/18030637/how-do-i-reference-tables-in-excel-vba) for a discussion of how to reference a `ListObject`. Also, each [`ListObject.Name`](https://learn.microsoft.com/en-us/office/vba/api/excel.listobject.name) is unique. Referencing a `ListObject` using a `Range` reference is a "hack," as explained by [this answer](https://stackoverflow.com/a/28291086/9245853) to the linked question. – BigBen Jun 29 '19 at 21:01
Understood. Thanks for the info. Can you help explain to me the difference between `DisplayName` and `Name` property please? – Andy Fazulus Jun 30 '19 at 07:46
1

I'll refer you to the [`DisplayName`](https://learn.microsoft.com/en-us/office/vba/api/excel.listobject.displayname) and [`Name`](https://learn.microsoft.com/en-us/office/vba/api/excel.listobject.name) documentation. From a quick test, it seems like Excel updates the `Name` if you update the `DisplayName`, and vice versa. – BigBen Jun 30 '19 at 14:56

"Subscript out of range" error for ListObject

1 Answers1