VBA & Macro Problems

How to Prevent Excel VBA from Adding Implicit Intersection Operator (@) to FILTER

John Wilson Oct 12, 2026 871 views

Question details

The user needs to stop Excel from automatically inserting the implicit intersection operator (@) when writing a dynamically generated FILTER formula via a VBA macro.

How to Prevent Excel VBA from Adding the Implicit Intersection Operator (@) to FILTER Formulas

Product: Excel
Device & OS: not provided
Scenario: Writing a dynamic array FILTER formula to a worksheet using an Excel VBA macro.
Observed behavior: Excel forces the implicit intersection operator (@) into the formula, which breaks the dynamic array behavior and causes the FILTER function to return a single value instead of the expected spill range.

Before you start

Ensure you are using a modern version of Excel that supports Dynamic Arrays (such as Microsoft 365 or Excel 2021 and later), as older versions do not natively support the FILTER function or spill ranges.

Solution 1Recommended

Use the Formula2 Property in VBA

Replace the legacy .Formula property with .Formula2 in your VBA code to instruct Excel to evaluate the input as a dynamic array formula.

When Microsoft introduced dynamic arrays, they also added the .Formula2 property to the VBA object model. The standard .Formula property is retained for backward compatibility and forces implicit intersection (adding the @ symbol) to mimic the behavior of older Excel versions.

By explicitly calling .Formula2, you bypass this legacy behavior, allowing modern functions like FILTER, UNIQUE, and SORT to spill their complete result ranges without interference.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor.

Locate the Target Macro

Find the specific module and macro where you are dynamically generating and writing the FILTER formula to the worksheet cell.

Update the Formula Property

Identify the line of code using the .Formula property (e.g., targetCell.Formula = formulaText) and change it to .Formula2 (e.g., targetCell.Formula2 = formulaText).

Run and Verify

Run the updated macro. Check the target cell in your worksheet to verify that the @ symbol is gone and the FILTER formula is successfully spilling the results across the adjacent cells.

Dynamic Array Compatibility: The .Formula2 property works universally for all dynamic array functions and standard formulas. Adopting it as your default for newer Excel versions will prevent unexpected legacy behaviors.

Free Microsoft Office alternative

Try WPS Office for Seamless Spreadsheet Management

Tired of troubleshooting legacy VBA quirks and implicit intersection operators? WPS Office offers a highly compatible, modern spreadsheet environment with native support for advanced dynamic array functions and seamless data processing.

1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
2. Open Your Workbook: Launch WPS Spreadsheet and easily open your existing .xlsx or .xlsm files.
3. Process Data Natively: Enjoy native support for dynamic arrays and complex spreadsheet tasks without worrying about forced legacy intersections.

Fully compatible with Microsoft Excel file formats (.xlsx, .xlsm, .csv).Native support for modern dynamic array functions like FILTER, SORT, and UNIQUE.Free and lightweight alternative to heavy Microsoft Office installations.Familiar user interface requiring zero learning curve for seasoned Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

What is the implicit intersection operator (@) in Excel?

The @ symbol is used by Excel to indicate implicit intersection. It is a legacy behavior that forces a formula capable of returning multiple values to return only a single value corresponding to the row or column of the formula cell.

Why does my VBA macro insert an @ symbol automatically?

When you use the standard .Formula property in VBA, Excel applies legacy compatibility rules, assuming the formula is not a dynamic array. This triggers the automatic insertion of the @ symbol to prevent the formula from spilling.

Does the Formula2 property work in older versions of Excel?

No, the .Formula2 property is only available in Excel versions that support dynamic arrays, such as Microsoft 365, Excel 2021, and newer. Using it in older versions will result in a runtime error.

How do I fix a #SPILL! error after removing the @ symbol?

A #SPILL! error occurs when a dynamic array formula does not have enough empty cells to display all of its results. Ensure that the range below and to the right of your FILTER formula is completely clear of other data or merged cells.

Excel & Data Problems