VBA添加公式后如何自动刷新表格计算？

问题场景 #

我编写了VBA代码，为表格添加名为test的列，并创建同名工作表test。随后通过以下VBA代码为该表格列的单元格设置公式：

Sheets("Data (HIDE)").ListObjects("DataTable").ListColumns(Range("B2").Value).DataBodyRange.Cells(1).Formula = "=IFERROR(VLOOKUP(A6,'" & Range("B2").Value & "'!$A$2:$B$1500,2,FALSE),"""")"

当我在test工作表中填充待查找的数据后，返回表格发现VLOOKUP公式不会自动计算所有行，必须双击包含公式的单元格并按下回车才会生效。请问能否通过VBA实现无需手动操作即可让表格中的VLOOKUP公式自动刷新/重新计算？

解决方案 #

1. 直接为整列设置公式（推荐）

Excel表格（ListObject）支持直接为整列设置公式，无需单独设置第一行再手动填充。使用结构化引用替换固定单元格引用，表格会自动将公式应用到所有行：

Dim targetColumn As ListColumn
Set targetColumn = Sheets("Data (HIDE)").ListObjects("DataTable").ListColumns(Range("B2").Value)

' 替换[@[A列表头]]为你表格中A列的实际表头名称
targetColumn.Formula = "=IFERROR(VLOOKUP([@[A列表头]],'" & Range("B2").Value & "'!$A$2:$B$1500,2,FALSE),"""")"

这种方式会让公式自动适配表格的新增行，且填充后立即计算。

2. 填充公式并强制计算

如果已经设置了第一行的公式，可以通过FillDown将公式批量填充到整列，再强制触发计算：

Dim tbl As ListObject
Dim targetCol As ListColumn

Set tbl = Sheets("Data (HIDE)").ListObjects("DataTable")
Set targetCol = tbl.ListColumns(Range("B2").Value)

' 将第一行公式填充到整列数据区域
targetCol.DataBodyRange.FillDown

' 强制重新计算整个工作簿
Application.CalculateFull

3. 确保自动计算模式开启

有时公式不自动计算是因为Excel处于手动计算模式，可以在VBA中强制设置为自动计算：

' 切换到自动计算模式
Application.Calculation = xlCalculationAutomatic

内容的提问来源于stack exchange，提问作者Buracku