Excel VBA设置Formula2后列范围出现多余括号问题咨询

核心原因 #

这是Excel 365（订阅版）近期更新引入的公式解析Bug，针对跨关闭工作簿的整列引用（如B:B），Excel会错误地将其格式化为B:(B)。VB.NET中出现相同问题，是因为两者都调用了Excel的COM接口处理公式解析，并非代码本身的疏漏。

代码小修正 #

首先注意你的VBA代码有个笔误：Activecell..Formula2多了一个点，应改为ActiveCell.Formula2，不过这不是导致括号异常的直接原因。

验证方法 #

打开外部工作簿States.xlsx后再运行代码，生成的公式会正常显示B:B——这是因为实时引用打开的工作簿时，Excel的解析逻辑与引用关闭的工作簿不同，Bug仅触发于关闭的外部工作簿引用。

临时解决方案 #

1. 改用绝对引用的整列写法：
   在引用中添加美元符号，强制Excel使用常规范围格式：

   ActiveCell.Formula2 = "=XLOOKUP(RC[-100],'[" & wbState.Name & "]" & wsState.Name & "'!$B:$B,'[" & wbState.Name & "]" & wsState.Name & "'!$A:$A)"
   

2. 使用具体行范围替代整列：
   直接指定最大行范围（Excel 365为1048576行）：

   ActiveCell.Formula2 = "=XLOOKUP(RC[-100],'[" & wbState.Name & "]" & wsState.Name & "'!B1:B1048576,'[" & wbState.Name & "]" & wsState.Name & "'!A1:A1048576)"
   

3. 先写入R1C1格式再转换：
   绕开A1格式的解析Bug：

   ActiveCell.FormulaR1C1 = "=XLOOKUP(RC[-100],'[" & wbState.Name & "]" & wsState.Name & "'!C2,'[" & wbState.Name & "]" & wsState.Name & "'!C1)"
   ActiveCell.Formula2 = ActiveCell.Formula2 ' 转换为A1格式
   

4. 回滚Excel版本：
   若需要立即恢复正常，可在Office账户的更新历史中回滚到之前无此Bug的版本，等待微软推送修复补丁。

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