Sometimes, we need to check whether if some cells is a part of an array formula or not.
Macro Examples
The following Excel VBA macro displays a message if the active cell is part of an array or not.
Sub CheckIfArrayFormula() If ActiveCell.HasArray = True Then MsgBox "The active cell is part of an array." Else MsgBox "The active cell is NOT part of an array." End If End Sub
Description
[Application.]ActiveCell
ActiveCell...
The [Application.]ActiveCell property returns an active Range object that represents the current active cell.
The ActiveCell property is a member of the Excel.Global class, so we can omit the Application property.
ActiveCell.HasArray (Range.HasArray)
ActiveCell.HasArray...
The ActiveCell.HasArray (Range.HasArray property ) returns True if the cell is part of an array formula.
So, a message box shows if the active cell is part of an array or not.
Sub CheckIfArrayFormula() If ActiveCell.HasArray = True Then MsgBox "The active cell is part of an array." Else MsgBox "The active cell is NOT part of an array." End If End Sub
Properties
http://www.relief.jp/itnote/archives/excel-vba-check-if-exist-array-formulas.php
Apply to
- Excel 2013
- Excel 2010
- Excel 2007