find all array formulas in an excel worksheet

Take a look at this example. Hope that it helps.

Sub array_formula()Dim rRange As Range, cell As RangeDim tot As IntegerSet rRange = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)    For Each cell In rRange        If cell.HasArray Then            MsgBox cell.Address & " " & cell.formula            tot = tot + 1         End If     Next cellMsgBox "total number of array formula: " & totEnd Sub

A slight upgrade on the existing (which I found quite useful thank you) which will search all sheets.

Sub debug_print_array_formulas_all_sheets()    Dim ws As Worksheet    Dim rRange As Range, cell As Range    Dim tot As Integer    For Each ws In ThisWorkbook.Sheets        On Error GoTo NotFound        ws.Unprotect        Dim v As Variant        v = ws.UsedRange.HasFormula        If IsNull(v) Or v Then            Set rRange = ws.UsedRange.SpecialCells(xlCellTypeFormulas)            tot = 0            For Each cell In rRange                If cell.HasArray Then                    Debug.Print ws.Name & ":" & cell.Address & " " & cell.Formula                    tot = tot + 1                End If             Next cell             If tot > 0 Then                Debug.Print "total number of array formula on " & ws.Name & ": " & tot                tot = 0             End If     NotFound:        End If    Next wsEnd Sub