通过VBA在Excel ListObject/Table中添加/修改/删除计算列公式

Question

如果我手动在Excel表格（即ListObject）的某个列中输入一个公式，AutoCorrect会将此公式应用于整个列。

是否有办法通过VBA控制此行为，即我是否可以修改/删除/添加此公式？

我知道我可以简单地更改ListObject.ListColumns(1).DataBodyRange对象的公式 - 但这将覆盖之前手动输入的任何值 - 而在UI中更改公式将保持其原样...

- Peter Albert

---

我不清楚您想要的行为是什么。关于“在UI中更改公式将不受影响”的问题，我的经验并非如此。如果我有一个表列，除了一个硬编码的单元格外，其余都采用相同的公式，然后我更改公式，我会得到一个小提示，“覆盖所有使用此公式的单元格”。如果我选择是，则硬编码单元格将被覆盖，如果选择否，则该公式仅应用于我更改的单元格。这是Excel 2010。 - Doug Glancy

@Doug - 谢谢你澄清这个问题。我曾错误地认为，在更新公式时，偏离计算列公式的单元格会保留！现在我明白了，这不是这种情况，这几乎使问题无效了！我所需要做的就是修改计算列公式，修改listObject.ListColumns（“Column name”）。DatabodyRange.FormulaR1C1即可... - Peter Albert

Answer 1

多亏了Doug和bonCodigos的评论/答案，我找到了简单的答案：

ListObject.ListColumns("Column name").DataBodyRange.FormulaR1C1 = "new formula"

这将覆盖任何手动值（就像自动更正的正常行为一样）。

- Peter Albert

---

这样做会设置默认公式吗？这样当添加新行时，公式也会被添加吗？ - William

@William - 是的，它会。 - Peter Albert

我在使用这个时突然出现了“属性无效”的错误，有什么提示吗？ - MattE

@MattE 你确定列名是正确的吗？此外，你的表是否有数据 - 如果没有，DataBodyRange 将为 Nothing，你会得到这个错误。 - Peter Albert

@PeterAlbert 我认为这可能是发生的原因，因为我正在从查询中提取数据...在运行此操作以确保有数据之前，等待查询完成加载的最佳方法是什么？ - MattE

Answer 2

最好能给我们展示一下您的表格截图，这样我们才能更好地回答您的问题。

以下是一般假设。您有一个列表对象，将一些数据转储到一列中，该列的其余单元格是手动交互的。

您可以先尝试手动操作，看看是否适用于您。如果手动操作被代码级别取代，则可以在代码级别执行此操作。 :)

主要操作是停止自动更正。

前往->工具菜单->单击自动更正选项-> 在自动更正选项卡中-> 1-防止所有自动更正

清除“替换键入的文本”复选框

2-防止特定更正

清除相应选项的复选框。我相信您更感兴趣的是后者-通过列表对象转储的特定数据范围。

---

现在这里是禁用此功能的代码。

在使用Excel表格（ListObjects）时，有两个自动更正选项需要考虑：您可以详细了解这两个选项。

* Apply new rows and columns in table 
  (VBA AutoCorrect.AutoExpandListRange Property)
* Fill formulas in tables to create calculated columns 
 (VBA AutoCorrect.AutoFillFormulasInLists Property) 

您可能想要在我们的列表对象顶部使用的代码是：

Application.AutoCorrect.AutoFillFormulasInLists = False

完全同意，如果Application.AutoCorrect.AutoFillFormulasInLists可以在ListObject级别上控制每个表格，那将会更加有用。所以这里有一个解决方法。

一种方法是每次清除表格数据。当您清除数据时，可以确保对于要忘记公式和格式的表格进行清除。因此，在删除表格内容之前，它将清除数据主体范围的内容。

Sub forgetMe()
   With Sheet1.ListObjects("myTable")
      If Not .DataBodyRange Is Nothing Then
         .DataBodyRange.ClearContents
         .DataBodyRange.Delete
      End If
   End With

End Sub

当您输入数据时，请关闭自动填充功能。

- bonCodigo