How to Prevent Excel from Adding @ to Array Formulas in VBA
Question details
The user needs to prevent Excel from automatically inserting the implicit-intersection operator (@) when writing array formulas via VBA.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Writing dynamic-array or standard array formulas to worksheet cells using VBA macros.
- Observed behavior
- Excel automatically inserts the @ symbol when the legacy Range.Formula property is used, which alters how range expressions evaluate.
Verify that your target version of Excel supports dynamic arrays (Microsoft 365 or Excel 2021+), as older versions do not recognize the required property.
Use the Formula2 Property in VBA
Replace the legacy Formula property with Formula2 to instruct Excel to evaluate the formula as a modern dynamic array, avoiding the @ symbol.
With the introduction of dynamic arrays, Excel updated the traditional Range.Formula property to apply implicit intersection (the @ symbol) by default to maintain backward compatibility. To bypass this behavior and write modern array formulas correctly, you must use Range.Formula2.
Press ALT + F11 to launch the Visual Basic for Applications editor in your Excel workbook.
Find the specific line of macro code where you are assigning the array formula to a range, such as Range('N2').Formula.
Update the property name from .Formula to .Formula2. For example: Range('N2').Formula2 = '=INDEX($E$3:$E$1029,MATCH(MIN(ABS(E3:E1029-$N$1)),ABS($E$3:$E$1029-$N$1),0))'.
Run the updated VBA macro. Check the target cell in the worksheet to verify that the formula is inserted without the @ symbol.
Try WPS Office for Seamless Spreadsheet Macros
If you are frustrated by Excel's changing formula syntax and forced implicit intersections, consider switching to WPS Office. It provides robust support for complex array formulas and VBA macros in a free, lightweight suite.
Frequently Asked Questions
What does the @ symbol mean in an Excel formula?
The @ symbol represents the implicit intersection operator. It limits an array formula to return a single value corresponding to the cell's specific row or column. Excel added it to ensure older formulas calculate exactly as they did before dynamic arrays were introduced.
Why do I get an 'Object doesn't support this property' error with Formula2?
This runtime error occurs if you run the VBA macro on an older version of Excel (like 2016 or 2019) that does not feature the dynamic array engine. The Formula2 property is only recognized in Microsoft 365, Excel 2021, and newer versions.
Can I remove the @ symbol manually from the worksheet?
Yes, you can manually delete the @ symbol in the formula bar and press Enter. However, if your macro runs again using the legacy .Formula property, Excel will automatically reinsert the @ symbol.
