Excel VBA: ListRows.Add doesn't like Table Names as variables?

Question

I have VBA code in an Excel project as shown below (I've only shown the relevant bits, and the italicised lines can be ignored).  Version A succeeds but version B falls over with an “Add method of ListRows” failure. 

Does this make sense to anyone? Or have I made an error blindingly obvious to everyone else?  Wouldn't be the first time.....

Version A uses literals instead of values held in strTable& strColName

Private Sub New_Item(strTable As String, strItem As String, strColName As String)

Dim ThisCol As Integer

Dim NextRow As Integer

Dim TopRow As Integer

Dim Tbl As ListObject

Dim NewRow As ListRow

Dim SortRange As String

Worksheets("Factors").Activate

Set Tbl = ActiveSheet.ListObjects("tblNames")

Tbl.ListColumns("Firstname").Range.Select

Set NewRow = Tbl.ListRows.Add(alwaysinsert:=True) 'Add a new row

Version B uses the parameter values

Private Sub New_Item(strTable As String, strItem As String, strColName As String)

Dim ThisCol As Integer

Dim NextRow As Integer

Dim TopRow As Integer

Dim Tbl As ListObject

Dim NewRow As ListRow

Worksheets("Factors").Activate

Set Tbl = ActiveSheet.ListObjects(strTable)

Tbl.ListColumns(strColName).Range.Select

Set NewRow = Tbl.ListRows.Add(alwaysinsert:=True)

The calling sub has this:

If NewItem Then Call New_Item("tblNames", ComboBox1.Value, "Firstname")

Answer 1

Thanks, Zack.  On your comment regarding recursion, I don't think it can apply here because I have run through only a handful of entries at a time, but repeating that twenty or thirty times. And that includes closing down Excel a few times, to do other things.  My PC, however, has suffered for a long time from the "Not responding" problem, which appears to occur several times daily to IE or any of the Office apps.  So it's possible a trip wire has got in.

Answer 2

Interesting. Ok, well if you see this behavior again we should investigate a little further. It would be best (for me) if you had reproduction steps I could use to re-create the error on my end.

Take care. :)

Answer 3

Hi, Zack

Just for information:

I still get the Add method failure in one application and I can't shake it.  I've carefully reproduced the code sequence in another, smaller application and get no problem.  I also found that in the main application a non-VBA table Resize operation also crashed the spreadsheet.  It seems to relate to one particular project, but is not tied to one particular table.

I discovered that the error message is (now) this:

"Automatio

I'm going to try rebuilding the main application from the small one that works, hoping the gremlins won't notice.  If that doesn't work I'll ditch the ListRows Add method and find another way.

Regards

Alan

Answer 4

Well my interest is extremely piqued now! Starting to sound like something is corrupt in the workbook. Is there any way I could get a copy of the file? (If so, my email is a gmail addy, with the first part being my whole name, but replace the space between the names with a period. I don't want to post it so bots can't get a hold of it. If not, no worries.) If we can reproduce the error and it ends up being a flaw with Tables, we can submit it to the Excel team, but if it's a corrupt workbook, at least we'll know.

As I mentioned before, I ditched the ListRows.Add method a long time ago, and recommend something like my InsertRows routine, which utilizes the Range objects Insert method. Another method, which is lightning fast, is the ListObject's Resize method. The downside with that is it only works for adding new rows, as it doesn't shift anything down. So if you're looking to insert rows prior to the end of the table it's not the best method.

Thanks for keeping me updated Alan!