vba 通过VBA在Excel Listobject/Table中添加/修改/删除计算列公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13753563/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Add/Modify/delete calculated column formula in Excel Listobject/Table via VBA
提问by Peter Albert
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).DataBodyRangeobject - 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 中的公式将保持不变......
回答by Peter Albert
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).
这将覆盖任何手动值（就像自动更正的正常行为一样）。
回答by bonCodigo
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
清除复选框 Replace Text as you type
2- To prevent SPECIFIC corrections
2-防止特定更正
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.
清除该选项的相应复选框。我相信您对后者更感兴趣 - 您只是通过列表对象转储的特定数据范围。
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. AutoFillFormulasInListscontrolled 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 TO FORGET 论坛和格式。所以它会在删除表格内容之前清除数据体范围的内容。
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.
当您输入数据时，从自动内容关闭开始。
