'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Prevent Excel VBA from Adding @ and Quotes to Cell References
Question details
The user needs to stop Excel VBA from automatically inserting an @ symbol and single quotes around cell references like F3 when assigning formulas via macros.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Writing a VBA macro to fill down a formula across multiple rows, requiring a relative cell reference without unwanted character modifications by the Excel formula engine.
- Observed behavior
- When using FormulaR1C1 or Formula2R1C1 in VBA, Excel modifies a standard A1-style reference (e.g., F3) into an invalid format like @'F3' or 'F3'.
Verify whether your VBA macro is currently designed to use A1-style references (like F3) or R1C1-style references (like R3C6), as mixing these formats causes syntax errors.
Use the Formula2 Property for A1-Style References
This is the most direct solution if you prefer to write cell references in standard A1 format without Excel misinterpreting them.
The FormulaR1C1 and Formula2R1C1 properties strictly expect R1C1 notation. If you pass an A1-style reference like 'F3' to these properties, Excel fails to recognize it as a valid coordinate. Instead, it assumes it is a named range or string, wrapping it in quotes and adding the @ (implicit intersection) operator.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) interface.
Find the specific module and line of code where you are assigning the formula using FormulaR1C1 or Formula2R1C1.
Modify the code to use the Formula2 property instead. For example, change Range("A1").Formula2R1C1 = "=F3" to Range("A1").Formula2 = "=F3".
Verify that your cell reference does not contain absolute markers (dollar signs). Use F3 instead of $F$3 so the formula updates correctly when filled down rows.
Use Proper R1C1 Syntax with Formula2R1C1
If your macro design requires the R1C1 property for dynamic column and row tracking, you must supply references in correct R1C1 notation.
Experience Seamless Spreadsheet Management with WPS Office
If you frequently encounter complex VBA syntax issues or dynamic array formula quirks in Microsoft Excel, consider trying WPS Office. It provides a lightweight, highly compatible spreadsheet environment that handles complex calculations effortlessly.
Frequently Asked Questions
Why does Excel add an @ symbol to my VBA formulas?
Excel adds the @ symbol, known as the implicit intersection operator, when it encounters a potential array formula in an environment that expects a single value. Mixing A1-style references with R1C1 properties often triggers this behavior in modern Excel versions.
What is the difference between Formula and Formula2 in VBA?
The Formula property uses Excel's older formula calculation engine, while Formula2 uses the newer dynamic array engine. Using Formula2 is recommended in modern Excel builds to properly handle arrays without forcing implicit intersection (@) behavior.
Why are single quotes added around my cell reference in VBA?
Single quotes are automatically added when Excel cannot parse the provided reference string within the context of the property used. Passing an A1 reference (like F3) to an R1C1-exclusive property causes Excel to treat the reference as an unknown named range or raw string, wrapping it in quotes.
How do I make a relative reference in R1C1 notation?
To create a relative R1C1 reference, use square brackets to indicate the offset distance from the cell containing the formula. For example, R[1]C[1] refers to one row down and one column to the right, whereas R1C1 without brackets is an absolute reference to cell A1.
