如何防止Excel插入行/列时VBA自定义函数自动重算？

问题还原 #

你定义的VBA自定义函数如下：

Public Function calc_x(ByVal x As Integer)
    ...do some stuff
    calc_x = x+x
End Function

在Excel工作表中：

• A1单元格内容：20
• A2单元格公式：=calc_c(A1)（注：此处应为笔误，正确调用应为=calc_x(A1)）

但插入新行/列时，即使操作区域与A1、A2无关，该函数仍会自动重新计算。

解决方案 #

1. 显式设置函数为非易失性 #

在自定义函数开头添加Application.Volatile False，明确告知Excel：该函数仅在引用单元格的值发生变化时才重算，不会因工作表结构变更（插入/删除行、列）触发计算。修改后的函数如下：

Public Function calc_x(ByVal x As Integer)
    Application.Volatile False
    ...do some stuff
    calc_x = x + x
End Function

注：VBA自定义函数默认是非易失性的，但显式声明可避免Excel在特殊场景下误触发重算。

2. 修正公式调用笔误 #

确保A2单元格的公式正确指向自定义函数：

=calc_x(A1)

3. 全局计算选项调整（不推荐） #

若需全局控制Excel重算行为，可进入「Excel选项 → 公式 → 计算选项」选择「手动」，但此设置会影响所有公式，仅在必要场景使用，日常建议保持「自动」，通过函数本身控制重算逻辑。

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