find all array formulas in an excel worksheet

Question

is there a way to find all array formulas in a given excel spreadsheet?

Solution 1

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

Sub array_formula()
Dim rRange As Range, cell As Range
Dim tot As Integer
Set 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 cell
MsgBox "total number of array formula: " & tot
End Sub

Solution 2

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 ws
End Sub