Excel VBA写入公式自动生成带@结构化引用的解决方法

问题核心触发逻辑 #

• 新版Excel（365/2021及后续版本）的Formula2属性是适配动态数组模型的写入接口，写入时会自动对未显式标记数组范围的跨表整列引用补全隐式交集@标识，默认将数组场景的引用强制转为单值取值，直接破坏原有数组运算逻辑。
• 即便切换为旧版FormulaLocal属性写入，只要公式内存在裸整列引用，Excel的自动公式规范化规则依然会补全@标识，极端场景下甚至会在整个公式最前端追加@，将数组运算退化为单值取值。
• 原公式中=与函数名之间的多余空格，会提升自动规范化的触发概率。

可直接落地的修改方案 #

• 替换公式写入接口，强制标记数组运算模式
  放弃Selection.Formula2的写入方式，改用传统公式写入属性，写入后通过FormulaArray属性显式将单元格标记为数组公式，从机制上跳过Excel的自动@补全。
  注意：Formula/FormulaArray属性固定使用英文语法规则（参数分隔符为逗号，函数名用英文），不受系统区域语言影响；如果需要适配德语区的语法规则（分号分隔、德语函数名），可改用FormulaLocal/FormulaArrayLocal属性写入。对应的替换代码如下：

  ' 移除等号后多余空格，避免触发自动规范化
  ' 英文语法写法（适配Formula属性）
  Formel = "=VLOOKUP(Auswertung!A" & Nr & "&B" & Nr & "&" & Bux & "1,CHOOSE({1,2,3},Daten!A:A&Daten!B:B&Daten!C:C,Daten!D:D),2,FALSE)"
  ' 先写入基础公式
  aus.Formula = Formel
  ' 显式标记为数组公式，阻止自动添加@标识
  aus.FormulaArray = aus.Formula
  

• 调整引用写法，绕开自动@的识别规则
  Excel自动补@的核心触发条件是“裸整列引用（如直接写Daten!A:A）被放在数组参数位置”，只要把整列引用改写为明确行范围的等效引用，就不会被判定为需要隐式交集。
  把公式内的整列引用从Daten!A:A&Daten!B:B&Daten!C:C、Daten!D:D，替换为Daten!A2:A1048576&Daten!B2:B1048576&Daten!C2:C1048576、Daten!D2:D1048576，功能与原整列引用完全一致，但写法上不属于“裸列引用”，不会被自动补@，替换后可直接用Formula2写入，不会出现标识错乱。
• 临时关闭应用级的公式自动转换规则
  在VBA工程开头临时关闭列表自动填充公式的转换规则，从全局层面阻断结构化引用、@标识的自动添加：

  ' 写入公式前执行一次全局设置
  Application.AutoCorrect.AutoFillFormulasInLists = False
  

验证效果 #

按上述方法修改后，写入的公式将与预期的德语/英语版规则完全一致，不会出现前置@、参数内@Daten!A:A这类冗余标识，数组运算逻辑可以正常触发。

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