Excel VBA ListRows.Add失败 [英] Excel VBA ListRows.Add Fails

问题描述

我可能缺少一些简单的东西，但是ListRows.Add给我带来了悲伤.功能如下:

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

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

在大多数情况下，这可以正常工作.但是，只要函数在我的工作表中的某个表上运行，对ListRows.Add的调用之后的行就永远不会执行(至少这是调试器所指示的)，并且该行也不会添加到表中.有什么想法/想法吗?

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?

更新:

这是自发布以来我学到的东西.如果我向Sub提供存根数据，则可以正常工作.例如:

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

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

注意:getTableObject在工作表中循环，并返回具有匹配名称的ListObject.

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

似乎与处理代码调用的上下文有关.在失败的情况下，公式(函数调用)已放置在各个工作表的各个单元格中.该公式包含对其他单元格中数据的引用.当其他单元格中的数据更改时，将调用该公式，该公式又调用上面给出的addEmployee Sub.这是失败的情况.

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.

推荐答案

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

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

开发自定义功能时， 重要的是要了解一把钥匙 您所使用的功能之间的区别 从其他VBA程序调用 您在工作表中使用的功能 公式.使用的功能程序 工作表公式必须是被动的. 例如，函数中的代码 过程无法操纵范围或 更改工作表上的内容.一个 例子可以帮助弄清楚这一点.

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.

也就是说，我应该指出一个 明显的例外.有可能 更改单元格 comment 中的文本，方法是 使用自定义的VBA功能.

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

Walkenbach，J.(2007).微软 Office Excel 2007电源编程 使用VBA.约翰·威利&儿子，第280页.

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

这篇关于Excel VBA ListRows.Add失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！