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

Very cool! We're always looking for feedback too. I'll look forward to your [uploaded] link.

Answer 2

Zack

Here is the link.

https://1drv.ms/f/s!AkH-gORCnL-QgchYTYgIPSkUC3mSWA

I ran this program this morning and it succeeded.  I then ran it again, after setting a break-point at the Listrows.Add line in New_Item2().  It failed at that line ("Add method of ListRows failed") and then Excel closed down.  I had changed nothing else.

Afterwards I did make changes, by the way, but these were only the addition of a few comment lines.

It must be down to some stupid mistake I've made, because I wrote another short application doing the same sort of thing and it works every time.

Thanks for your patience.

Regards

Alan

Answer 3

I can't reproduce your error. That being said, there are several things I would change, which I'll list below.

Change your cmdQuit_Click routine

Instead of using

    End

You should instead use

    Unload Me

You should never use the End keyword. It halts all code, including any instatiated objects/properties in memory. Generally, the operative clause is 

    Exit Sub

But in this case you're wanting to close the form, not arbitrarily end all code execution.

Knowing this, I would change your userform code to something like this

Private Sub cmdOK_Click()

    Dim Table As ListObject

    Dim NameExists As Boolean

    Dim Success As Long

    On Error Resume Next

    Set Table = Sheet1.ListObjects("tblNames")

    On Error GoTo 0

    If Not Table.DataBodyRange Is Nothing Then NameExists = ExistingItem(Table.DataBodyRange, Me.ComboBox1.Value)

    If Not NameExists Then Success = AddTableItem(Table, "Firstname", ComboBox1.Value, , True)

    Debug.Print Success

    Me.ComboBox1.Value = ""

End Sub

Function ExistingItem( _

         ByVal SearchRange As Range, _

         ByVal SearchString As String _

         ) As Boolean

    On Error Resume Next

    ExistingItem = Not SearchRange.Find(What:=SearchString) Is Nothing

    On Error GoTo 0

End Function

Function AddTableItem( _

         ByVal Table As ListObject, _

         ByVal ColumnName As String, _

         ByVal ItemValue As String, _

         Optional ByVal Position As Long = 0, _

         Optional ByVal SortTable As Boolean = False, _

         Optional ByVal UseExistingSort As Boolean = True _

         ) As Long

    Dim InsertRow As Range

    Dim NewSort As Boolean

    Dim ColumnPosition As Long

    On Error Resume Next

    ColumnPosition = Table.ListColumns(ColumnName).Index

    On Error GoTo AddTableItem_Error

    If ColumnPosition = 0 Then Exit Function

    ColumnPosition = WorksheetFunction.Min(ColumnPosition, Table.ListColumns.Count)

    Position = WorksheetFunction.Max(1, WorksheetFunction.Min(Position, Table.ListRows.Count + 1))

    If Table.DataBodyRange Is Nothing Then Set InsertRow = Table.InsertRowRange

    If Not Table.DataBodyRange Is Nothing Then Set InsertRow = Table.ListRows.Add(Position:=Position).Range

    InsertRow(1, ColumnPosition).Value = ItemValue

    If SortTable Then

        If Table.Sort.SortFields.Count > 0 And UseExistingSort Then

            Table.Sort.Apply

        Else

            Table.Sort.SortFields.Add Table.ListColumns(ColumnPosition).Range(1, 1), xlSortOnValues, xlAscending

            Table.Sort.Apply

        End If

    End If

    AddTableItem = 1

    Exit Function

AddTableItem_Error:

End Function

There are a few items to note here.  There is a logic change in how you check if a value exists in a range. Instead of looking at it as 'is unique', the above looks at the revers of 'is existing'. It's also more flexible in that the range to look in is not a parameter, making it usable in other applications (in this case, within the userform, but it could just as well be put in a standard module and be used elsewhere in the project).

I saw you were wanting to apply a sort to your table after adding a value. Since tables retain their sort [fields], there isn't any reason to keep adding a new sort field, just apply the sort which is there. This is how the last two [optional] parameters came about. Really we just need to know if you want to sort your data, and if so, do you want to use the existing sort field(s)/order(s).

The 'AddTableItem' above is represented as a function as opposed to a sub. I did this in order to return a result. This way you can interpret the results of the called method. Right now it's just printing to the Immediate window, but it illustrates returning a value from a more complex method call. You can manipulate it however you see fit inside the called function.

The last thing I want to mention is that you do not see any Select or Activate calls in my code. They're not needed. Now, if you want to activate or select something for the user to see, that is one thing, but it certainly isn't needed for the code to run. In fact, doing so will slow down your code execution tremendously. As such, it's generally recommended you don't use these actions.

I can't get this code to error on me. Can you try this code and see if you can still reproduce the error?

Answer 4

I understand that story very well, it's all too familiar to myself. I started tinkering with Excel because I thought it was cool, then a little here, a little there, pretty soon I'm using it for everything. :)

I remember reading Bill's book on macros some years ago (2007 I think?), and I really enjoyed it. It's hard to find good sources for learning VBA (which, consequentially I'm hoping to develop some courses on). Doing what you're doing, learning through application, is one of the best methods. While learning through forums/blogs can be slow - everyone claims to be an expert, you'll start seeing the same people around, or pointing to the same resources. It's a small world. ;)

One thing you said caught my attention.

"Now when a program is run twenty or thirty times without problems and then fails without any changes being made to the code"

Does this mean you're using the ListRows.Add method multiple times, as in an iteration, loop, or other recursion? If this is the case, it might give cause for concern. While I don't know definitively, I'm fairly certain there's a memory leak in Excel with this method. Loop a hundred times, adding a listrow on each iteration, it will get slower, and slower, and slower, until you want to punch your computer. This is why it's better to do it all at once, which is the reason I came up with the InsertRows routine I posted above.

If there is anything else I can do, please let me know, I'm happy to help. I love Excel and I love Tables.

Answer 5

Many thanks for the work you’ve put into this, Zack.

I have keyed in your code and tested it briefly – there were no problems.  I will be spending some time getting my head round the detail.

I now think the reason you couldn’t get my clumsy code to fail is because the apparent error in it arises from outside the code.  Since sending you the link I have written another, similar, Excel application incorporating the common routines from this one.  I merrily used it for a couple of days until yesterday, when it failed on that Add method.  Now when a program is run twenty or thirty times without problems and then fails without any changes being made to the code, I would assume either the problem is with the data or with an “external” area: Excel itself (as installed on my PC), Windows or the PC.  Since it subsequently ran a few times without crashing, I assume it’s not the data that’s the problem.

The original problem was, therefore, a red herring, but I’ve learned a great deal from you through this.  On the matter of the Activate and Select methods, I used these after following code that I found in on line forums.  I stopped using Select on your advice, but I found that, without Activate, one routine had written to the wrong Sheet.  However, I can see now that it’s not necessary.

This all started when I was asked to take over the bookkeeping for a local social club.  The previous treasurer had used a paper-based system which I found unwieldy, so I created an Excel application which worked very successfully, especially after I bought Mike Smart’s Excel 2016 Expert Skillsand extended my knowledge of formulas*.*I wanted, however, to make it easier for a future, non-technical, bookkeeper to use.  This club has two other members, out of about ninety, who use PCs  — for sending email, and even then one of them seems to feel a PC might steal his soul.  Both use a Dark Ages version of Office. This is England.

I also wanted to protect the data from errors, by introducing VBA and User Forms to validate and control input.  Little did I realise how much Visual Basic had moved on and how much I had forgotten in the last seventeen years.  As my code shows….

I had Bill Jelen’s Excel 2016 VBA and Macros, which got me started with VBA and recommends your book.  Mike Smart’s book pushed me towards a better appreciation of Excel Tables, and, as you know, I eventually got around to buying your book.

My bookkeeping project will take a while to complete, but I’m having great fun with VBA in the meantime.  If only there were more hours in the day….

Thanks again, Zack.  I’ll let you know how I get on.

Cheers

Alan