录制宏跨工作簿复制数组公式时触发FormulaArray属性设置错误求助

Hey there, let’s tackle this annoying FormulaArray error you’re hitting when running your recorded macro. I’ve dealt with this a bunch of times, so here’s what’s going on and how to fix it:

1. Fix Hardcoded External References in the Array Formula #

When you record a macro copying an array formula between workbooks, Excel automatically hardcodes the source workbook’s full name (and path if it’s saved) into the formula. If the source workbook’s name changes later, or it’s not open when you run the macro, this breaks the FormulaArray assignment.

• How to adjust:
  • Always open both the source and target workbooks before running the macro, so you can use simplified references (like Sheet1!$A$1:$A$10 instead of '[SourceWorkbook.xlsx]Sheet1'!$A$1:$A$10).
  • If you need dynamic references, replace hardcoded names with VBA variables that pull the current workbook name, like:

    Dim sourceWB As Workbook
    Set sourceWB = Workbooks("SourceWorkbook.xlsx")
    targetRange.FormulaArray = "=SUM(IF(" & sourceWB.Sheets("Sheet1").Range("A1:A10").Address(True, True, xlA1, True) & ">5," & sourceWB.Sheets("Sheet1").Range("B1:B10").Address(True, True, xlA1, True) & ",0))"
    

2. Handle Long Array Formulas (255+ Character Limit) #

Older Excel versions (pre-2016) have a strict 255-character limit for the FormulaArray property. If your array formula is longer than that, assigning it directly will throw the error—even if the formula works when you enter it manually with Ctrl+Shift+Enter.

• Workaround:
  • First set the formula as a regular formula using the Formula property, then convert it to an array formula. This bypasses the character limit. Example code:

    Dim targetRange As Range
    Set targetRange = Workbooks("TargetWorkbook.xlsx").Sheets("Sheet1").Range("C1:C10")
    
    ' Set as a regular formula first
    targetRange.Formula = "=SUM(IF(SourceWorkbook.xlsx!Sheet1!$A$1:$A$10>5,SourceWorkbook.xlsx!Sheet1!$B$1:$B$10,0))"
    ' Convert to array formula
    targetRange.FormulaArray = targetRange.Formula
    

  • This trick works for all Excel versions, including newer ones where the limit is lifted.

3. Match Target Range Dimensions to the Array Formula #

Array formulas are designed to output to a specific range size. If your target range is smaller, larger, or a different shape than the original range the formula was written for, you’ll get this error.

• Check and adjust:
  • Look at the source range where the array formula lives (e.g., A1:A5). Make sure your target range is the exact same size (e.g., C1:C5), not a single cell or a mismatched number of rows/columns.

4. Quick Troubleshooting Check #

Before tweaking your macro, try manually entering the array formula into the target workbook (using Ctrl+Shift+Enter). If that works, the issue is definitely with how the macro is handling the formula assignment—not the formula itself.

内容的提问来源于stack exchange，提问作者Babalola Naheem Ganiyu