How to Fix Excel VBA Formulas Returning #VALUE! or @ Error in Excel 365 for Mac
Question details
The user is experiencing issues where Excel VBA inserts formulas (such as RAND, RANK, or XLOOKUP) that return a #VALUE! error or contain an unexpected @ symbol.
- Product
- Excel 365
- Device & OS
- Mac
- Scenario
- Writing formulas into spreadsheet cells automatically using VBA macros in Excel 365.
- Observed behavior
- Formulas inserted via VBA fail to calculate properly, either displaying a #VALUE! error or inserting an @ symbol (implicit intersection operator) into the function string.
Create a secure backup of your macro-enabled workbook and temporarily remove any sensitive data to safely test and debug your VBA code.
Use Absolute References and Test a Single Lookup Value
Updating your VBA code to use absolute cell references and testing the formula structure on a single lookup value resolves reference shifting and calculation errors.
When writing formulas across a range using VBA, relative references can shift unexpectedly, causing array size mismatches and #VALUE! errors. By locking the lookup arrays with absolute references (adding dollar signs), you ensure stability.
Additionally, breaking down the problem by writing the formula for a single cell first helps isolate syntax issues before applying it to a larger range.
Press 'Option + F11' in Excel 365 for Mac to open the Visual Basic for Applications (VBA) editor.
Find the module and the exact line of code where you are attempting to write the formula into the worksheet using the .Formula property.
Change the relative references in your lookup and return arrays to absolute references. For example, change 'Members!G4:G627' to 'Members!$G$4:$G$627'.
Modify the VBA string to test a single lookup value first. Your code should look similar to: Range("C16:C20").Formula = "=XLOOKUP(Members!N5,Members!$G$4:$G$627,Members!$C$4:$C$627)"
Save your code and run the macro. Check the target cells in your worksheet to confirm the #VALUE! error is resolved and calculations are correct.
Switch to WPS Office for Reliable Spreadsheet Management
If you are tired of troubleshooting complex dynamic array compatibility issues and VBA errors in Excel 365 for Mac, consider WPS Office. It serves as a highly compatible, free alternative to Microsoft Office, ensuring your formulas and data calculations work seamlessly.
- 1. Download WPS Office: Visit the official WPS website and download the Mac version of WPS Office.
- 2. Open your workbook: Launch WPS Spreadsheet and open your existing .xlsm or .xlsx file directly without needing file conversion.
- 3. Calculate seamlessly: Run your calculations and manage your spreadsheet data with high precision and compatibility.
Frequently Asked Questions
Why does my VBA code insert an @ symbol before my functions in Excel 365?
Excel 365 uses the @ symbol to indicate implicit intersection in its newer dynamic array engine. If VBA inserts a formula using the standard '.Formula' property, Excel may automatically add the @ symbol to force legacy calculation behavior.
How do I stop Excel 365 VBA from adding the @ symbol?
You can prevent the @ symbol from being added by changing the property in your VBA code from '.Formula' to '.Formula2'. The '.Formula2' property tells Excel to evaluate the string using the modern dynamic array rules.
What causes the #VALUE! error when inserting XLOOKUP with VBA?
A #VALUE! error in XLOOKUP typically occurs when the lookup array and return array are not exactly the same size, or if relative cell references shift out of bounds across multiple cells. Using absolute references (e.g., $A$1:$A$100) ensures the arrays remain perfectly aligned.
