Excel宏生成公式触发#REF!错误，仅点击编辑栏后正常的问题求助

问题场景与现象 #

我用Excel宏实现外部数据导入、整理及计算，其中生成的公式如下：

=IFERROR(IF(MAX('FF-48455IL'!P$9:P$500)=0;"",MAX('FF-48455IL'!P$9:P$500));"")

该公式生成后直接返回错误，必须选中对应单元格点击公式编辑栏才会返回正确结果。已设置自动计算，点击「计算工作表」也无法消除错误。

用「公式求值」功能排查发现，MAX('FF-48455IL'!P$9:P$500)部分在手动编辑前返回#REF!；将这部分公式复制到其他单元格可正常计算，但原单元格仍报错。

FF-48455IL工作表的P9单元格有日期时间戳，其余单元格为空（后续将填充数据），手动为P10添加值也无法消除错误。

宏代码片段 #

生成公式的宏代码如下（变量说明：SR=10，CurT="FF-4-8455IL"，LACol="P"，nHeader=8）：

TmpStr2 = "'" & CurT & "'!" & LACol & "$" & nHeader + 1 & ":" & LACol & "$500"    
.Cells(SR, 5).formula = "=iferror(if(max(" & TmpStr2 & ")=0,"""",""""),"""")"

原因分析 #

1. 工作表名称不匹配 #

宏中CurT变量的值为FF-4-8455IL，但实际引用的工作表名称是FF-48455IL（少了一个横杠），导致公式引用了不存在的工作表，触发#REF!错误。手动点击编辑栏时，Excel重新解析可能自动匹配了正确的工作表，或无意中修正了名称。

2. 公式参数分隔符不兼容 #

VBA中Range.Formula属性要求使用英文逗号，作为公式参数分隔符，而中文Excel默认使用分号;。宏代码中用逗号编写公式，但生成到单元格后不符合中文Excel的语法规则，导致无法正常计算；手动编辑时Excel会自动将逗号转换为分号，公式恢复正常。

解决方案 #

方案1：修正工作表名称匹配问题 #

将宏中CurT变量的值改为与实际工作表名称完全一致：

CurT = "FF-48455IL" ' 去掉多余的横杠，匹配目标工作表名称

方案2：兼容公式参数分隔符 #

方式A：使用FormulaLocal属性

将宏中的.formula替换为.formulaLocal，该属性支持本地语言的参数分隔符（中文环境下为分号）：

.Cells(SR, 5).FormulaLocal = "=IFERROR(IF(MAX(" & TmpStr2 & ")=0,"""",MAX(" & TmpStr2 & ")),"""")"

注：若需跨Excel语言版本通用，建议优先使用Formula并保持英文逗号分隔符。

方式B：统一使用英文逗号分隔符

保持.formula属性，公式中使用英文逗号，Excel会自动适配本地分隔符：

.Cells(SR, 5).Formula = "=IFERROR(IF(MAX(" & TmpStr2 & ")=0,"""",MAX(" & TmpStr2 & ")),"""")"

方案3：强制触发单元格重新计算 #

若上述方案仍有问题，可在生成公式后强制触发单元格重新计算：

.Cells(SR, 5).Formula = "=IFERROR(IF(MAX(" & TmpStr2 & ")=0,"""",MAX(" & TmpStr2 & ")),"""")"
.Cells(SR, 5).Calculate ' 强制计算单个单元格

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