locked
Automation error ListRows.Add RRS feed

  • Question

  • Firstly, I'm a self-taught developer and haven't been at this for very long so please bear this in mind and be patient with me!

    I have a very simple (unfinished for now due to the problem) function for adding a row to the bottom of a ListObject and fill it with some data.

    Public Function CreateNewRecord(ByRef lTbl as ListObject, asRec() as String) as Boolean
    
    Dim lRow as ListRow
    Dim i as Integer
    
    Set lRow = lTbl.ListRows.Add
    
    For i = LBound(asRec) to UBound(asRec)
    lRow.Range(1, i + 2) = asRec(i)
    Next
    
    CreateNewRecord = True
    End Function

    I've tried calling the function from a user form and from code in the same module.

    The first time I run this, I get:

    Run-time error '-2147417848 (80010108)': Method 'Add' of object 'ListRows' failed.

    Excel then crashes immediately, and restarts itself, recovering the file as it was last saved.

    I've modified the code to add a column before adding a row as a test of the methodology and it works perfectly. However, as soon as I try to add a row, I get the error.

    Can anyone help me please?

    • Edited by Clive St Helmet Wednesday, April 15, 2015 4:35 PM More information
    Wednesday, April 15, 2015 4:33 PM

Answers

  • Thanks for the help.

    I've created a new workbook, inserted the code provided and had no problems whatsoever. I went back to my workbook, tried a different table and again couldn't replicate the issue. I then went back to the original table and tried inserting a row and it crashed Excel once again. So I'm blaming the table (and possibly the worksheet it is on). I've deleted both, created a new worksheet and a new table with a different name and have not had the same problem.

    I'm still not certain what happened but I can only assume that the listobject in question had become corrupted somehow.

    Thanks for the help!

    **EDIT**

    Okay, I think I can recreate the fault now. I'm calling this function from a userform, which is launched from another userform, which contains a listbox with the row source set to the table I'm editing. If the rowsource is removed from the listbox, the problem goes away. If the rowsource is populated with this table's name, then even if I have unload the form before trying to insert a row into the table, Excel crashes. This happens to any table that I set as the rowsource for this listbox.

    I've now fixed it by setting the rowsource when the form initialises and clearing the rowsource when the form terminates.

    Thursday, April 16, 2015 9:47 AM

All replies

  • That issue must be related to your file, I can not reproduce the behavior.

    Make a new file,
    write some headings into the top row,
    if you like some data below,
    format them as table and
    execute this macro:

    Sub Test()
      Dim LO As ListObject
      Dim LR As ListRow
      Dim Data() As String, i As Integer
      
      'Get the table
      Set LO = ActiveSheet.ListObjects(1)
      
      'Create some random data
      ReDim Data(1 To LO.ListColumns.Count)
      For i = 1 To UBound(Data)
        Data(i) = Chr(65 + Rnd * 26)
      Next
      
      'Add a row
      Set LR = LO.ListRows.Add
      'Flush the array to the sheet
      LR.Range.Value = Data
    End Sub
    

    Works!?

    If you need further help please upload your file (maybe with anonymous data) on an online file hoster like www.dropbox.com and post the download link here.

    Andreas.

    Wednesday, April 15, 2015 6:12 PM
  • Hi Clive,

    I can’t reproduce that issue too.

    Please refer to Andreas said that try it in a new file or share the sample file.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, April 16, 2015 6:24 AM
  • Thanks for the help.

    I've created a new workbook, inserted the code provided and had no problems whatsoever. I went back to my workbook, tried a different table and again couldn't replicate the issue. I then went back to the original table and tried inserting a row and it crashed Excel once again. So I'm blaming the table (and possibly the worksheet it is on). I've deleted both, created a new worksheet and a new table with a different name and have not had the same problem.

    I'm still not certain what happened but I can only assume that the listobject in question had become corrupted somehow.

    Thanks for the help!

    **EDIT**

    Okay, I think I can recreate the fault now. I'm calling this function from a userform, which is launched from another userform, which contains a listbox with the row source set to the table I'm editing. If the rowsource is removed from the listbox, the problem goes away. If the rowsource is populated with this table's name, then even if I have unload the form before trying to insert a row into the table, Excel crashes. This happens to any table that I set as the rowsource for this listbox.

    I've now fixed it by setting the rowsource when the form initialises and clearing the rowsource when the form terminates.

    Thursday, April 16, 2015 9:47 AM