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

If I manually enter a formula into a column in an Excel table (i.e. ListObject), AutoCorrect applies this formula to the whole column.

如果我手动将公式输入Excel表格中的列（即ListObject），则自动更正将此公式应用于整个列。

Is there any way to control the this behavior via VBA, i.e. can I somehow modify/delete/add this formula?

有没有办法通过VBA控制这种行为，即我可以以某种方式修改/删除/添加此公式？

I know I can simply change the formula of the ListObject.ListColumns(1).DataBodyRange object - but this will overwrite any manually values entered before - while changing the formula in the UI will leave this untouched...

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

2 个解决方案

#1

Thanks to Doug's and bonCodigos comments/answers, I found the simple answer:

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

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

This will overwrite any manual value (just as the normal behavior with AutoCorrect).

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

#2

Best would be if you could show us a screen shot of your sheet. Based on that we would have articulated the answer.

如果您能向我们展示您的工作表的屏幕截图，那将是最好的。基于此，我们将阐明答案。

Here is with the general assumption. That you have one list object to dump some data to a column and rest of the cells in that column are manually interacted.

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

You could try the following manually first and see if it works for you. Still the manual one is being overtaken by the code level, then you do this in code level. :)

您可以先手动尝试以下操作，看看它是否适合您。仍然手动的一个被代码级别超越，然后你在代码级别执行此操作。 :)

The main action here is to Stop automatic corrections

这里的主要动作是停止自动更正

Go To -> Tools menu -> Click AutoCorrect Options -> In the AutoCorrect Tab ->

转到 - >工具菜单 - >单击自动更正选项 - >在自动更正选项卡 - >中

1- To prevent ALL automatic corrections

1-防止所有自动更正

Clear the check box for Replace Text as you type

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

2- To prevent SPECIFIC corrections

2-防止SPECIFIC更正

Clear the corresponding check box for the option. I believe you are more interested in the latter - specific data range that you just dump via a listobject.

清除该选项的相应复选框。我相信你对后者更感兴趣 - 你通过listobject转储的特定数据范围。

Now here is the code for disabling this feature at code level.

现在，这是在代码级别禁用此功能的代码。

When working with Excel Tables (ListObjects) there are two AutoCorrect options to consider: You can read about those two in details.

使用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)

Code you may want to use at the top of our listobject is,

您可能希望在我们的列表对象顶部使用的代码是，

Application.AutoCorrect.AutoFillFormulasInLists = False

And totally agree that it would be so much more useful if Application.AutoCorrect. AutoFillFormulasInLists controlled on a table by table basis at the ListObject level. So here is a workaround.

并且完全同意如果Application.AutoCorrect它会更有用。 AutoFillFormulasInLists在ListObject级别按表控制。所以这是一个解决方法。

So one way is to clear your table data each time. And when you clear data you can make sure for TABLE TO FORGET forumulae and formatting. So it will clear the contents of the data body range before deleting table contents.

因此，一种方法是每次清除表数据。当您清除数据时，您可以确保TABLE FOR FORGET forumulae和格式化。因此，在删除表内容之前，它将清除数据主体范围的内容。

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

End Sub

When you input the data, start with auto stuff off.

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

#1