PDA

View Full Version : VBA runtime crash - Listrows.add



Mr_Taron
08-27-2013, 12:14 PM
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
08-27-2013, 01:48 PM
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
08-27-2013, 11:59 PM
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)?