VBA runtime crash - Listrows.add

[Mr_Taron]

Has anyone ever run into this problem before?

I have an extensive .xlsm which uses the simple command line :

ThisWorkbook.worksheets(1).listobjects(1).listrows.add

And 2 out of 3 times, it will cause a run-time error which will then crash Excel and restart it.


The full piece of code is as follow (however even the simple example above bugs):

Dim iTable As ListObject
    Dim newRow As ListRow
    
    sheetsArray = getSheetsArray

For Each iSheetName In sheetsArray
        Set iTable = ThisWorkbook.Worksheets(iSheetName).ListObjects(1)
        
        Set newRow = iTable.ListRows.Add(Position:=iTable.ListRows.Count + 1)
        
        transferDataToRow newRow, iTable
Next iSheetName

Has anyone ever had this bug, which seems to be more of an Excel instability then an error with the code?

Any solutions? The fact that I cant add listrows to listobjects seems pretty disastrous!

[SamT]

For Each iSheetName In sheetsArray

If sheetsArray is in fact a String array, then you should use

For i = LBound(sheetsArray) To i = UBound(sheetsArray)
    Set iTable = ThisWorkbook.Worksheets(sheetsArray(i)).ListObjects(1) 
'
'
'

[p45cal]

Is every sheet in SheetsArray absolutely guaranteed to have at least 1 listobject in it?
If there's an On Error Resume Next somewhere before your quoted code, and there isn't a listobject in one of the sheets then iTable may not be the listobject you expect. I haven't tested whether your existing Set iTable line returns Nothing if there is no listobject, or doesn't change what it was before, but maybe a
Set iTable = Nothing
within the loop before the existing Set iTable line?

Also (again, I haven't checked this) but are all your sheets unprotected (or you've used UserInterfaceOnly with a .protect statement.somewhere)?