Excel VBA ListRows.Add 失败

Excel VBA ListRows.Add Fails

提问by Adam

I'm probably missing something simple, but ListRows.Add is giving me grief. Here's the function:

我可能遗漏了一些简单的东西，但 ListRows.Add 给我带来了悲伤。这是函数：

Sub addEmployee(employeeName As String, tableToAddTo As ListObject)
   Dim newRow As ListRow

   Set newRow = tableToAddTo.ListRows.Add()
   newRow.Range.Cells(1, 1).Value = employeeName
   tableToAddTo.Sort.Apply
End Sub

In most cases, this works fine. However, whenever the function runs on a certain table in my worksheet, the lines following the call to ListRows.Add are never executed (at least that's what the debugger indicates) and the row does not get added to the table. Any thoughts/ideas?

在大多数情况下，这可以正常工作。但是，每当函数在我的工作表中的某个表上运行时，调用 ListRows.Add 之后的行永远不会执行（至少调试器是这样指示的）并且该行不会添加到表中。任何想法/想法？

UPDATE:

更新：

Here's what I've learned since the post. If I feed the Sub with stub data it works fine. For example:

这是我自从发布帖子后学到的东西。如果我为 Sub 提供存根数据，它工作正常。例如：

Sub driver() 
    Dim myTable As ListObject 
    Set myTable = getTableObject("myTableName") 
    Call addEmployee("myName", myTable)
End Sub

Note: getTableObject cycles through the worksheets and returns the ListObject with the matching name.

注意：getTableObject 循环遍历工作表并返回具有匹配名称的 ListObject。

It seems to be an issue dealing with the context in which the code is called. In the case that fails, a formula (Function call) has been placed in various cells of various worksheets. The formula contains references to data in other cells. When the data in the other cells changes, the formula is invoked, which in turn calls the addEmployee Sub that is given above. This is the case that fails.

处理调用代码的上下文似乎是一个问题。在失败的情况下，在各种工作表的各个单元格中放置了一个公式（函数调用）。该公式包含对其他单元格中数据的引用。当其他单元格中的数据发生更改时，将调用公式，进而调用上面给出的 addEmployee Sub。这是失败的情况。

回答by Adam

It appears that what I was trying to do is not possible.

看来我试图做的事情是不可能的。

When you develop custom functions, it's important to understand a key distinction between functions that you call from other VBA procedures and functions that you use in worksheet formulas. Function procedures used in worksheet formulas must be passive. For example, code within a Function procedure cannot manipulate ranges or change things on the worksheet. An example can help make this clear.

You might be tempted to write a custom worksheet function that changes a cell's formatting. For example, it could be useful to have a formula that uses a custom function to change the color of text in a cell based on the cell's value. Try as you might, however, such a function is impossible to write. No matter what you do, the function won't change the worksheet. Remember, a function simply returns a value. It cannot perform actions with objects.

That said, I should point out one notable exception. It is possible to change the text in a cell commentby using a custom VBA function.

Walkenbach, J. (2007). Microsoft Office Excel 2007 Power Programming with VBA. John Wiley & Sons, p 280.

开发自定义函数时，了解从其他 VBA 过程调用的函数与在工作表公式中使用的函数之间的主要区别非常重要。工作表公式中使用的函数过程必须是被动的。例如，Function 过程中的代码不能操作范围或更改工作表上的内容。一个例子可以帮助说明这一点。

您可能想编写一个自定义工作表函数来更改单元格的格式。例如，使用自定义函数根据单元格的值更改单元格中文本颜色的公式可能很有用。然而，尽你所能，这样的函数是不可能写出来的。无论您做什么，该函数都不会更改工作表。请记住，函数只是返回一个值。它不能对对象执行操作。

也就是说，我应该指出一个值得注意的例外。可以使用自定义 VBA 函数更改单元格注释中的文本。

沃肯巴赫，J.（2007 年）。Microsoft Office Excel 2007 使用 VBA 进行高级编程。约翰威利父子公司，第 280 页。

回答by swarajk1

Sorry for the irrelevance to the question, but this is the only StackOverflow post that comes up on Google when searching for similar crash, due to another cause. The crash on ListRows.Addbugged me extensively for quite a while, so sharing the issue which was causing it for me.

很抱歉与该问题无关，但由于其他原因，这是在搜索类似崩溃时出现在 Google 上的唯一 StackOverflow 帖子。崩溃ListRows.Add困扰了我很长一段时间，所以分享导致我崩溃的问题。

There's a table (say MySourceTable) on a worksheet. In my UserForm, a ComboBox's RowSource is a column from this table(MySourceTable[ColName]). On clicking another button on the same UserForm, I was inserting the new row using ListRows.Addinto this table and crashing Excel. If this scenario is similar to yours, read on.

工作表上有一个表格（比如 MySourceTable）。在我的用户窗体中，ComboBox 的 RowSource 是该表(MySourceTable[ColName]) 中的一列。单击同一用户窗体上的另一个按钮时，我将新行插入ListRows.Add到该表中并导致Excel 崩溃。如果这种情况与您的情况类似，请继续阅读。

The crash is being caused by the ComboBox, which is still attached to the table, and is unable to process the insertion of rows (please mind that it's still instantiated because I'm still on the UserForm when inserting the new row). After a lot of pain, the following lines above and below the insertion solved it for good:

崩溃是由 ComboBox 引起的，该 ComboBox 仍然连接到表，并且无法处理行的插入（请注意它仍然被实例化，因为在插入新行时我仍然在用户窗体上）。经过一番痛苦之后，插入上方和下方的以下几行彻底解决了这个问题：

    comboSetSelect.RowSource = ""
    Set newRow = myTable.ListRows.Add
    comboSetSelect.RowSource = "MySourceTable[ColName]" 'col set in properties

This detaches the ComboBox from the table before inserting the new row and once insertion is done, updates the ComboBox again. Further, the newly inserted row's value (if updated right after insertion) shows up as well in ComboBox.

这会在插入新行之前从表中分离 ComboBox，一旦插入完成，将再次更新 ComboBox。此外，新插入的行的值（如果在插入后立即更新）也会显示在 ComboBox 中。

回答by SandPiper

I understand this is a really old question, but it is one that I have come across numerous times while trying to answer for myself. I still do not understand the specifics of why this is happening, but here's my workaround.

我知道这是一个非常古老的问题，但这是我在试图为自己回答时遇到过无数次的问题。我仍然不明白为什么会发生这种情况的具体细节，但这是我的解决方法。

Sub AddRow()
     Dim Tbl as ListObject
     Dim MyColumn as Integer

     Tbl.ListRows(1).Range.EntireRow.Insert
     Tbl.ListColumns(MyColumn).DataBodyRange.Cells(1,1).Value = "My Value"
End Sub

By adding a new row above the first existing entry, the table automatically adjusts down as intended, then I can start writing my values into the table.

通过在第一个现有条目上方添加一个新行，表格会自动按预期向下调整，然后我可以开始将我的值写入表格中。

Again, sorry for reviving an old post, but I was unable to find an adequate solution elsewhere, so this is my own workaround I hoped would be helpful to others in the future.

再次，很抱歉恢复旧帖子，但我无法在其他地方找到合适的解决方案，所以这是我自己的解决方法，我希望将来能对其他人有所帮助。

回答by Bhanu Sinha

In my case table was bound by a listbox

在我的情况下，表受列表框约束

 Dim intClientRow As Integer
Dim strLstSource As String
strLstSource = frmHNW.lstB01People_List.RowSource
frmHNW.lstB01People_List.RowSource = ""
intClientRow = UpdateTableFromFrame(frmHNW.fraB01People, "Clients_tblClientDetails", True)
If intClientRow > 0 Then
    UpdateTableFromList frmHNW.lstB01Contact_Method, "ClientPeople_tblClientContactMethod", "=getClientID(" & intClientRow & ")"
    UpdateTableFromList frmHNW.lstB01Pref_Days, "ClientPeople_tblClientPrefDay", "=getClientID(" & intClientRow & ")"
    UpdateTableFromList frmHNW.lstB01Pref_Time, "ClientPeople_tblClientPrefTime", "=getClientID(" & intClientRow & ")"
End If

frmHNW.lstB01People_List.RowSource = strLstSource

UpdateTableFromFrame forms applies lisrows.add method to the source table of the listbox. After hours of testing I found that when I don't remove the list source my listrows.add method fails. So I removed rowsource of the listbox before calling UpdateTableFromFrame and restored back once row addtion is done. This solved the problem

UpdateTableFromFrame 表单将 lisrows.add 方法应用于列表框的源表。经过数小时的测试，我发现当我不删除列表源时，我的 listrows.add 方法失​​败。所以我在调用 UpdateTableFromFrame 之前删除了列表框的 rowsource 并在行添加完成后恢复。这解决了问题

回答by Mor Sagmon

This also bugged me for a while. For me, it seemed that calling ListRows.Add multiple times in a loop - caused this error to occur, and sometimes crashed Excel. The solution that worked for me: I added DoEvents command after the ListRows.Add command:

这也困扰了我一段时间。对我来说，似乎在循环中多次调用 ListRows.Add - 导致发生此错误，有时还会导致 Excel 崩溃。对我有用的解决方案：我在 ListRows.Add 命令之后添加了 DoEvents 命令：

tbl.ListRows.Add
DoEvents

回答by Rycore

I know this is an old thread, I don't want to re-post and I beleive I have a solution that might help others.

我知道这是一个旧线程，我不想重新发布，我相信我有一个可能会帮助其他人的解决方案。

Quick bit of info about my scripts, I have ListRows.Addin Private Sub BTN_Save_DblClickI removed all my code and left only this module, this to my surprise worked, so I started implementing each sub and function until I got to the one that was causing the problem.

我的脚本信息的快速一点，我有ListRows.Add在私人小组BTN_Save_DblClick我删除了所有我的代码只留下这个模块，这让我吃惊的工作，所以我开始实施的每个子和函数，直到我到了，这就是一个导致问题。

It turns out that (for me) because I had UserForm_Initialize()as a Private Subinstead of a Public Sub, this caused ListRowsto fail, crashing excel in the process..

事实证明（对我而言）因为我将UserForm_Initialize()作为Private Sub而不是Public Sub，这导致ListRows失败，在此过程中使 excel 崩溃。

So if you are having this issue, and you have UserForm_Initialize(), make sure it is set to Public instead of private.

因此，如果您遇到此问题，并且您有UserForm_Initialize()，请确保将其设置为 Public 而不是 private。

Anyway if that doesn't work, then it's a simple process of elimination, remove all your code except the one with ListRowsand add each sub/function testing each time, until you find the one that's causing the problem.

无论如何，如果这不起作用，那么这是一个简单的消除过程，删除除ListRows之外的所有代码，并每次添加每个子/函数测试，直到找到导致问题的那个。

Hope this helps.

希望这可以帮助。

EDITSo this was a temporary fix, it started crashing again, so I removed the line of code within the userform_initialize and it fixed it again.. this is so strange because all it does is ComboBox1.RowSource=NamedRange.

编辑所以这是一个临时修复，它再次开始崩溃，所以我删除了 userform_initialize 中的代码行并再次修复它..这太奇怪了，因为它所做的只是ComboBox1.RowSource=NamedRange。

I never had this sort of issue with Excel VBA before.

我以前从未在 Excel VBA 上遇到过这种问题。

回答by Tom Juergens

Another cause for ListObject.ListRows.Addto fail can be worksheet protection. Since I didn't see this mentioned in any of my search results, I'd like to add it here for future reference.

ListObject.ListRows.Add失败的另一个原因可能是工作表保护。由于我在任何搜索结果中都没有看到这一点，因此我想将其添加到此处以供将来参考。

If a worksheet has been protected with

如果工作表已被保护

worksheet.Protect 

be sure to

务必

worksheet.Unprotect 

it before applying ListObject.ListRows.Add!

申请前ListObject.ListRows.Add吧！

回答by Andreas Dietrich

In short

简而言之

tab_.ListRows(1).Range.EntireRow.Insertmay(!) work instead of tab_.ListRows.Add.
(Like already suggested in SandPiper's answer)

tab_.ListRows(1).Range.EntireRow.Insertmay(!) 工作而不是tab_.ListRows.Add.
（就像SandPiper 的回答中已经建议的那样）

encapsulating it in a more general functionthat also returns ListRow:

将它封装在一个更通用的函数中，该函数也返回ListRow：

Function TabRowAdd(tab_ As ListObject) As ListRow
    tab_.ListRows(1).Range.EntireRow.Insert
    Set TabRowAdd = tab_.ListRows(1)
End Function

you would use it like this: TabRowAdd(tab_)instead of tab_.ListRows.Add

你会像这样使用它：TabRowAdd(tab_)而不是tab_.ListRows.Add

Possible Cause

可能的原因

If there are other non-table cells underneath (or maybe even beneath) the current table, it wont work, because (I guess) the "shifting-down" of this other content could cause bad side-effects or could be more complicated.

如果当前表格下方（或什至下方）还有其他非表格单元格，则它将不起作用，因为（我猜）其他内容的“下移”可能会导致不良副作用或更复杂。

So e.g. imagine the following table layout and how should the insert of a row in table 1be accomplished straight-forward? (generally bad/stupid layout - I know :) )

例如，想象一下下面的表格布局，以及如何直接在表格 1 中插入一行？（通常糟糕/愚蠢的布局 - 我知道:)）

| table 1 header |  | table 2 header |
|         row 1  |  |         row 1  |
|         row 2  |  |         row 2  |         
                    |         row 3  |  | table 5 header |
| table 3 header |  |         row 4  |  |         row 1  |
|         row 1  |                      |         row 2  |
|         row 2  |                      |         row 3  |
                                        |         row 4  |
    | table 4 header      |             |         row 5  |
    |         row 1       |

=> this is not easy and that's why I guess they may just check, if there is non-table stuff beneath or underneath the to-be-manipulated (insert row/col, delete row/col) table and thus throw some 1004exception.
(maybe even following "non-A-column-starting" tables would have that problem already - I did not check it)

=> 这并不容易，这就是为什么我猜他们可能只是检查，如果在要操作的（插入行/列，删除行/列）表下方或下方有非表内容，从而引发一些1004异常。
（也许即使遵循“非A列开始”表也会有这个问题 - 我没有检查它）