Excel VBA: Checks If Active Sheet has Array Formulas

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 sheet has array formulas or not.

Sub CheckIfHasArrayFormula()

  If Cells.HasArray = True Then
    MsgBox "The active sheet has array formulas."
  Else
    MsgBox "The active sheet does not have array formulas."
  End If

End Sub

Description

[Application.]Cells

Cells...

The Application.Cells property returns the Range object represents ALL ranges on an active sheet.
So we do not need to loop through.

obj-Excel-Application-Cells

The Cells property is a member of the Excel.Global class, so we can omit the Application property.

obj-Excel-Cells

Cells.HasArray (Range.HasArray)

  Cells.HasArray...

The Cells.HasArray (Range.HasArray property ) returns True if the cell is part of an array formula.

So, a message box shows if the active sheet has array formulas or not.

Sub CheckIfHasArrayFormula()

  If Cells.HasArray = True Then
    MsgBox "The active sheet has array formulas."
  Else
    MsgBox "The active sheet does not have array formulas."
  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