How to Fix Excel VBA Adding @ Operator (Use Formula2R1C1)
Question details
The user needs to fix a VBA macro that incorrectly adds the implicit intersection operator (@) when converting array formulas between relative, absolute, and mixed references.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Converting array formulas using VBA macros.
- Observed behavior
- The macro adds an unwanted '@' symbol to the formulas, which triggers legacy implicit intersection and disrupts modern dynamic array behavior.
Ensure you have saved and backed up your Excel workbook containing the VBA macro before modifying the code.
Use Formula2R1C1 Instead of FormulaR1C1
Update your VBA code to use the dynamic-array-aware Formula2R1C1 property, which preserves modern array behavior and prevents the @ operator.
In modern versions of Excel, dynamic arrays are the default calculation engine. The older FormulaR1C1 property triggers Excel's legacy behavior, which automatically inserts the implicit intersection operator (@) to force the formula to return a single value.
Using Formula2R1C1 tells the application to treat the formula as a modern dynamic array, preventing the unwanted @ symbol and allowing the results to spill across cells.
Press ALT + F11 in your spreadsheet to open the Visual Basic for Applications (VBA) editor.
In the Project Explorer pane, find the module and the specific macro where the formula conversion is taking place.
Find the line using ConvertFormula and replace .FormulaR1C1 with .Formula2R1C1. For example, change it to: .Formula2R1C1 = Application.ConvertFormula(.Formula2R1C1, xlR1C1, xlR1C1, absRelMode, oneCell)
Save your VBA project, close the editor, and run the macro again to verify that the @ operator is no longer added.
Try WPS Office for Seamless Spreadsheet Management
If you are frustrated by complex formula behaviors and legacy macro intersections, consider switching to WPS Office. It offers a lightweight, highly compatible spreadsheet tool that handles complex formulas and data processing with ease.
- 1. Download and Install: Get WPS Office Free from the official website and install it on your computer.
- 2. Open Your Workbook: Launch WPS Spreadsheet and open your existing .xlsm or .xlsx files directly without any formatting loss.
- 3. Edit and Save: Work on your formulas and macros with full compatibility, then save your progress.
Frequently Asked Questions
What does the @ symbol mean in Excel formulas?
The @ symbol is the implicit intersection operator introduced in modern spreadsheet engines. It indicates that a formula which could return an array is being forced to return a single value, ensuring backward compatibility with older software versions.
What is the difference between FormulaR1C1 and Formula2R1C1?
FormulaR1C1 is the legacy property that triggers older formula behavior, often adding the @ symbol to array formulas. Formula2R1C1 is the newer, dynamic-array-aware property that allows formulas to spill across multiple cells without implicit intersection.
Why did my VBA macro suddenly start adding @ to formulas?
This typically happens after updating to a version of the software that supports dynamic arrays (like Microsoft 365 or Excel 2021). Older macros using .Formula or .FormulaR1C1 are treated as legacy code, prompting the engine to insert the @ operator to maintain old calculation behaviors.
