如何解决VBA中FormulaArray属性的1004错误？

常见原因及修复方案 #

• 公式长度超出旧版Excel限制：
  旧版Excel（2013及更早）通过VBA设置数组公式时，存在255字符的长度上限。若你的公式字符数超标，可针对Excel 365/2021及以上版本，改用Formula2Array替代FormulaArray，修改后的代码如下：

  Worksheets("Discriminatory_power").Range("G11").Formula2Array = "=INDEX(Import_data!A10:E1048576,MATCH(1,(Import_data!A10:A1048576=A11)*(Import_data!D10:D1048576=O8)*(Import_data!C10:C1048576=L7),0),MATCH(L4,Import_data!A10:F10,0))"
  

• 简化公式兼容旧版Excel：
  若需适配旧版，可将重复引用定义为工作表名称来缩短公式长度：
  1. 先定义名称：

     ThisWorkbook.Names.Add Name:="DataRange", RefersTo:="=Import_data!A10:E1048576"
     ThisWorkbook.Names.Add Name:="MatchColA", RefersTo:="=Import_data!A10:A1048576"
     ThisWorkbook.Names.Add Name:="MatchColD", RefersTo:="=Import_data!D10:D1048576"
     ThisWorkbook.Names.Add Name:="MatchColC", RefersTo:="=Import_data!C10:C1048576"
     ThisWorkbook.Names.Add Name:="HeaderRow", RefersTo:="=Import_data!A10:F10"
     

  2. 简化后设置数组公式：

     Worksheets("Discriminatory_power").Range("G11").FormulaArray = "=INDEX(DataRange,MATCH(1,(MatchColA=A11)*(MatchColD=O8)*(MatchColC=L7),0),MATCH(L4,HeaderRow,0))"
     

• 检查公式语法有效性：
  确认所有工作表名称（如Import_data）、单元格引用（如A11、O8）无拼写错误，且目标工作表和单元格均存在有效数据。

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