vba 将列公式保留在表格标题中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25743857/
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
Keeping column formulas in a table header
提问by Ayusman
Is it possible to have the formulas that I need applied on columns be saved or applied to a column header or some kind of metadata so that as and when I add new rows to my Excel table the Formulas get applied to the columns?
是否可以将我需要应用于列的公式保存或应用于列标题或某种元数据，以便在我向 Excel 表中添加新行时，公式会应用于列？
Scenarion:
场景：
I am creating a template Table, which will have no rows at first. On a separate sheet (or same sheet for that matter) once the user selects the number of rows to be generated in the table, I dynamically add rows to the table using VBA.
我正在创建一个模板表，它最初没有行。在单独的工作表（或与此相关的同一工作表）上，一旦用户选择了要在表格中生成的行数，我就会使用 VBA 动态地将行添加到表格中。
The idea is I may not have any rows in the table at beginning OR user may have deleted rows manually. When I programmatically add new rows, I want the Formulas applied on the cells as well. Most of the formulas I am using are either of the three types:
这个想法是我开始时表中可能没有任何行，或者用户可能手动删除了行。当我以编程方式添加新行时，我也希望将公式应用于单元格。我使用的大多数公式都是以下三种类型之一：
Structured table reference, Excel functions like SUM, AVERAGE etc and custom function names.
结构化表引用、SUM、AVERAGE 等 Excel 函数以及自定义函数名称。
Updated:
更新：
Here is what I have tried:
这是我尝试过的：
1> tried applying the formula to the header itself.
Result: The header it self changes with #REF!error. I think the behavior is correct. So it's a no-go option.
1> 尝试将公式应用于标题本身。结果：它自己更改的标题#REF!错误。我认为这种行为是正确的。所以这是一个不可行的选择。
2> Tried creating one row and apply the formula to the row. That works, but the problem is, I do not want a dummy row to begin with.
2> 尝试创建一行并将公式应用于该行。那行得通，但问题是，我不想要一个虚拟行开始。
3> Using VBA code to add row to the table using
3> 使用 VBA 代码向表中添加行
ActiveWorkbook.Worksheets("Sheet3").ListObjects("Table2").ListRows.Add AlwaysInsert:=True
inside a for loop. The new rows retain the visual style sheets, but does not seem to retain the formulas. Just blank cells.
在 for 循环内。新行保留视觉样式表，但似乎不保留公式。只是空白单元格。
回答by dee
Could the fomrmulas be in header cell commnets?
公式可以在标题单元格commnets 中吗？
And then with VBA add the formula for the current row:
然后使用 VBA 添加当前行的公式：
Sub test()
Dim headerCells As Range
Set headerCells = Range("B2:E2")
OnNewRow 3, headerCells
End Sub
Sub OnNewRow(newRow As Integer, headerCells As Range)
Dim headerCell As Range, targetCell As Range, formulaFromComment As String
For Each headerCell In headerCells
formulaFromComment = GetFormulaFromComment(headerCell)
If (formulaFromComment = "") Then _
GoTo NextHeaderCell
Set targetCell = Intersect(headerCells.Worksheet.Rows(newRow), _
headerCell.EntireColumn)
AddFormula newRow, targetCell, formulaFromComment
NextHeaderCell:
Next
End Sub
Sub AddFormula( _
newRow As Integer, _
targetCell As Range, _
formula As String)
formula = Replace(formula, "{ROW}", newRow)
targetCell.formula = formula
End Sub
Function GetFormulaFromComment(headerCells As Range) As String
' TODO
GetFormulaFromComment = "=SUM($C${ROW}:$E${ROW})"
End Function
回答by skube
Not sure about Table templates or VBA but perhaps there is another option by using =ARRAYFORMULA()
不确定表格模板或 VBA，但也许还有另一种选择 =ARRAYFORMULA()
For example, say you had a header row and 3 columns and wanted your last column to be the product of the first two. In cell C2you could enter the following:
例如，假设您有一个标题行和 3 列，并且希望您的最后一列是前两列的乘积。在单元格中，C2您可以输入以下内容：
=ARRAYFORMULA(A2:A*B2:B)
This has threebenefits:
这有三个好处：
- Skips the first row completely
- Effectively applies the formula to every row which is useful if you later decide to insert a row (your question)
- Only one location to modify the formula for every single row
- 完全跳过第一行
- 有效地将公式应用于每一行，如果您以后决定插入一行（您的问题），这很有用
- 只有一个位置可以修改每一行的公式
Although, it may not be immediately obvious where how/where the cells are being calculated. (hint: ctrl+~may help)
虽然，如何/在哪里计算单元格可能不是很明显。（提示：ctrl+~可能有帮助）
