Excel VBA: Checks If the Active Cell is Part of an Array Formula

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