如何缩短VBA中超出255字符限制的数组公式？

问题背景 #

在VBA中使用数组公式时，因引用的表名称过长，导致公式长度超出255字符限制（超出20-30个字符），示例公式如下：

"= IFERROR(Index(INDIRECT(""myTable[Inventory]""), Match(1, (""Vans"" = INDIRECT( ""myTable[Brand]""))*(""Shoes"" = INDIRECT(""myTable[Product]"",0))), NA())"

可行解决方法 #

• 移除INDIRECT函数，直接使用结构化引用
  INDIRECT函数会额外增加大量转义字符和函数名称长度，直接引用表列可大幅缩短公式。修改后的示例公式：

  "=IFERROR(INDEX(myTable[Inventory],MATCH(1,(""Vans""=myTable[Brand])*(""Shoes""=myTable[Product]),0)),NA())"
  

  去掉INDIRECT后减少了大量冗余字符，能有效压缩公式长度。

• 定义名称简化重复引用
  对重复出现的长表列引用，可在VBA中先定义名称，再在公式中调用：

  ' 定义名称
  ThisWorkbook.Names.Add Name:="Inv", RefersTo:="=myTable[Inventory]"
  ThisWorkbook.Names.Add Name:="BrandCol", RefersTo:="=myTable[Brand]"
  ThisWorkbook.Names.Add Name:="ProdCol", RefersTo:="=myTable[Product]"
  ' 简化后的数组公式
  Range("A1").FormulaArray = "=IFERROR(INDEX(Inv,MATCH(1,(""Vans""=BrandCol)*(""Shoes""=ProdCol),0)),NA())"
  

  用短名称替代长引用，能显著降低公式总长度。

• 使用VBA的Evaluate方法替代FormulaArray
  若公式实在无法缩短，可通过Evaluate计算结果后直接写入单元格，绕开255字符限制：

  Dim result As Variant
  result = Evaluate("IFERROR(INDEX(myTable[Inventory],MATCH(1,(""Vans""=myTable[Brand])*(""Shoes""=myTable[Product]),0)),NA())")
  Range("A1").Value = result
  

  注意：此方法直接写入计算结果而非公式本身，适合不需要保留公式的场景。

• 拆分公式到辅助单元格
  将公式部分逻辑拆分到辅助单元格，再在主公式中引用辅助结果，分散长度压力。比如先在B1单元格写入匹配逻辑，再在主单元格调用：

  Range("B1").FormulaArray = "=(""Vans""=myTable[Brand])*(""Shoes""=myTable[Product])"
  Range("A1").FormulaArray = "=IFERROR(INDEX(myTable[Inventory],MATCH(1,B1#,0)),NA())"
  

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