none
VBA runtime crash - Listrows.add

    Question

  • Has anyone ever run into this problem before?

    I have an extensive .xlsm which uses the simple command line :
    [CODE]ThisWorkbook.worksheets(1).listobjects(1).listrows.add[/CODE]
    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):

    [CODE]
    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[/CODE]



    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!
    Tuesday, August 27, 2013 7:47 PM

All replies

  • Re:  "Set newRow = iTable.ListRows.Add(Position:=iTable.ListRows.Count + 1)"

    According to the help file...
      "Position:   Optional Integer. Specifies the relative position of the new row."
      "If Position is not specified, a new bottom row is added."

    Worth a try...
    Set newRow = iTable.ListRows.Add

    '---
    Jim Cone
    Portland, Oregon USA
    Special Sort Excel add-in (30+ ways to sort)
    https://jumpshare.com/b/O5FC6LaBQ6U3UPXjOmX2

    Wednesday, August 28, 2013 1:46 AM
  • Originally, I was using that exact line :

    Set newRow = iTable.ListRows.Add

    It generated the runtime crash, so I found that using a parameter fixed it... for 2days.

    I don't know what to do anymore. I stumbled upon what seems to be an instability in Excel itself (as suggested in some other posts from January 2013 by different people).

    Wednesday, August 28, 2013 3:18 AM
  • The only other thing I can think of deals with the workbook size...

    Do the tables encompass entire columns?
    (you can't add a row when the bag is full)

    Too many sheets possibly?
    (Several hundred could be a problem)

    Workbook size in MB?
    (better keep in below 50 MB - below 10 is even better)

    Another thought...
      locked/protected rows on some of the sheets?
      errors in formulas on a sheet?
    '---
    Jim Cone


    • Edited by James Cone Wednesday, August 28, 2013 3:41 AM typo
    Wednesday, August 28, 2013 3:40 AM
  • Yeah, it's the locked/protected sheet that started it all and my entire Excel has been unstable ever since.

    But it really seems like something with Excel, as I should just get a regular error in VBA, not a crash.

    Because otherwise my file is only 115kb (it's an empty form that transfer all data to an Excel workbook used only for storing data)

    Wednesday, August 28, 2013 5:15 AM

  • Re:  "my entire Excel has been unstable ever since"

    A couple of things to try, other than reinstalling Excel...

    Copy all of the code in the module out to Notepad
    Delete the module (right-click it)
    Insert a new module and name it
    Copy the code from the Notepad file and paste into the new module.
    Compile, save and test.

    And/or

    When opening the file, do an 'Open and Repair'.
    In the File | Open dialog the 'Open' button has a small arrow on it.
    Clicking the arrow provides the repair option.
    '---
    Jim Cone
    Wednesday, August 28, 2013 1:14 PM