I have an array entered into Excel Range. But when I individually check each cell inside range using Range.HasArray property, it returns False. What is correct way to determine if my target cell is part of an array or not?

I tried Range.FormulaArray but this field is populated even when the cell is not part of an array.

I have an array entered into Excel Range. But when I individually check each cell inside range using Range.HasArray property, it returns False. What is correct way to determine if my target cell is part of an array or not?

I tried Range.FormulaArray but this field is populated even when the cell is not part of an array.

• excel
• vba

• "I have an array entered into Excel Range" - please update your question to tell us exactly how you did that, so we're clear on how to replicate what you're asking about. – Tim Williams Commented Feb 10 at 21:50
• Hi @TimWilliams, This was an existing workbook created with older version of Excel. The older version of Excel (2019) returned Range.HasArray as True but new version of Excel (2024) returned False for same data. The Range.HasSpill is (in Excel 2024) returning True for same data as you have recommended. – samirjaiswal Commented Feb 11 at 22:37
• I can't really make much of your comment, since you've still not shared exactly how the "array" was created. – Tim Williams Commented Feb 11 at 22:41
• Sorry, I don't have details on how the data was entered. – samirjaiswal Commented Feb 11 at 23:12
• But you could look at the sheet and see that? I'm assuming you're dealing with a regular "Spill" but you don't actually say what you are working with.... – Tim Williams Commented Feb 11 at 23:14

1 Answer 1

HasArray works for array formulas (entered with Ctrl+Shift+Enter) but not for "auto-spill" formulas.

If you want to know if a range is part of a spill you need Range.HasSpill