Legacy Array Formula Finder: Uncover Every Hidden CSE Formula Before It Breaks

TL;DR: You try to edit a cell in an inherited workbook and Excel snaps back with “You can’t change part of an array.” You didn’t even know it was an array formula — there’s no visible indicator unless you click into the cell and notice the { } braces. This macro finds every CSE array formula, tells you whether it’s a single-cell or the dangerous multi-cell kind, and hyperlinks you straight to each one. Your original data is never touched.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook that may contain legacy Ctrl+Shift+Enter formulas — especially inherited files from pre-2019 Excel, before dynamic arrays became standard
• The macro scans every sheet — no designated tabs required

What the macro does NOT do:

• It does not modify any cells on your original sheets. All output goes to a new “Legacy-Arrays” report tab
• It does not convert or remove the array formulas. It reports them for your review — you decide what to keep, convert, or replace
• It does not scan for dynamic array formulas (formulas that spill without Ctrl+Shift+Enter in Excel 365). Those are normal formulas and don’t cause the “can’t change part of an array” error

Limitations:

• HasArray returns True for both legacy CSE arrays and cells that happen to be inside a dynamic array spill range in Excel 365. The macro checks FormulaArray to distinguish — if the formula contains @ operators or lacks curly braces in the stored formula text, it’s likely a dynamic array
• Very large multi-cell arrays (hundreds of cells) will produce one row per cell in the report. The “Array Range” column groups them visually by showing the full merged range address
• Protected sheets are skipped — the macro can’t inspect formula properties on a protected sheet

#The Macro

Option Explicit

Sub FindLegacyArrays()
    ' ── Legacy Array Formula Finder ────────────────────
    ' Scans every sheet for CSE (Ctrl+Shift+Enter) array
    ' formulas — the invisible killers that block cell
    ' editing and hide from formula view.
    '
    ' Output: "Legacy-Arrays" report sheet with Sheet,
    ' Cell (hyperlinked), Array Range, Type (single-cell
    ' or multi-cell), and Formula Text.
    ' ────────────────────────────────────────────────────

    ' ── Configuration ──────────────────────────────────
    Const OUT_SHEET As String = "Legacy-Arrays"

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet, wsOut As Worksheet
    Dim cell As Range, formulaRng As Range
    Dim outRow As Long, totalArrays As Long, multiCount As Long
    Dim arrRng As Range
    Dim arrType As String

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    ' ── Count arrays before building output ─────────────
    totalArrays = 0
    multiCount = 0

    For Each ws In ThisWorkbook.Worksheets
        On Error Resume Next
        Set formulaRng = ws.Cells.SpecialCells(xlCellTypeFormulas)
        On Error GoTo CleanUp

        If Not formulaRng Is Nothing Then
            For Each cell In formulaRng
                If cell.HasArray Then
                    totalArrays = totalArrays + 1
                    Set arrRng = cell.CurrentArray
                    If arrRng.Cells.Count > 1 Then
                        multiCount = multiCount + 1
                    End If
                End If
            Next cell
        End If
    Next ws

    If totalArrays = 0 Then
        MsgBox "No legacy array formulas found in this workbook." & _
               vbCrLf & vbCrLf & _
               "If you use Excel 365, your array formulas may be " & _
               "dynamic arrays — those don't use Ctrl+Shift+Enter " & _
               "and won't cause the 'can't change part of an array' " & _
               "error.", vbInformation, "Legacy Array Finder"
        GoTo CleanUp
    End If

    ' ── Remove old output sheet ────────────────────────
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets(OUT_SHEET).Delete
    Application.DisplayAlerts = True
    On Error GoTo CleanUp

    ' ── Create output sheet ────────────────────────────
    Set wsOut = ThisWorkbook.Worksheets.Add( _
        After:=ThisWorkbook.Worksheets( _
            ThisWorkbook.Worksheets.Count))
    wsOut.Name = OUT_SHEET

    ' ── Write headers ──────────────────────────────────
    wsOut.Range("A1:E1").Value = Array( _
        "Sheet", "Cell", "Array Range", "Type", "Formula")
    With wsOut.Range("A1:E1")
        .Font.Bold = True
        .Interior.Color = RGB(50, 50, 50)
        .Font.Color = vbWhite
    End With

    ' ── Collect array formulas ─────────────────────────
    outRow = 2

    For Each ws In ThisWorkbook.Worksheets
        On Error Resume Next
        Set formulaRng = ws.Cells.SpecialCells(xlCellTypeFormulas)
        On Error GoTo CleanUp

        If formulaRng Is Nothing Then GoTo NextSheet

        For Each cell In formulaRng
            If Not cell.HasArray Then GoTo NextCell

            Set arrRng = cell.CurrentArray

            ' Only report the top-left cell of each array
            ' to avoid duplicate rows for multi-cell arrays
            If cell.Address <> arrRng.Cells(1).Address Then _
                GoTo NextCell

            ' Sheet name
            wsOut.Cells(outRow, 1).Value = ws.Name

            ' Cell address (hyperlinked)
            wsOut.Hyperlinks.Add _
                Anchor:=wsOut.Cells(outRow, 2), _
                Address:="", _
                SubAddress:="'" & ws.Name & "'!" & _
                    cell.Address(False, False), _
                TextToDisplay:=cell.Address(False, False)

            ' Array range
            wsOut.Cells(outRow, 3).Value = _
                arrRng.Address(False, False)

            ' Single-cell or multi-cell
            If arrRng.Cells.Count > 1 Then
                arrType = "Multi-cell (" & _
                    arrRng.Cells.Count & " cells)"
            Else
                arrType = "Single-cell"
            End If
            wsOut.Cells(outRow, 4).Value = arrType

            ' Formula text (with apostrophe to prevent execution)
            wsOut.Cells(outRow, 5).Value = _
                "'" & cell.FormulaArray

            outRow = outRow + 1

NextCell:
        Next cell

NextSheet:
    Next ws

    ' ── Format output ──────────────────────────────────
    With wsOut
        .Columns("A").ColumnWidth = 18
        .Columns("B").ColumnWidth = 10
        .Columns("C").ColumnWidth = 16
        .Columns("D").ColumnWidth = 22
        .Columns("E").ColumnWidth = 70
        .Range("A1").Select
        ActiveWindow.FreezePanes = True
    End With

    ' ── Color-code multi-cell arrays ───────────────────
    Dim r As Long
    For r = 2 To outRow - 1
        If InStr(wsOut.Cells(r, 4).Value, "Multi-cell") > 0 Then
            wsOut.Range("A" & r & ":E" & r).Interior.Color = _
                RGB(255, 240, 220)  ' Light orange
        End If
    Next r

    ' ── Summary ────────────────────────────────────────
    Dim singleCount As Long
    singleCount = totalArrays
    ' Count actual unique arrays (we only reported one per array)
    Dim uniqueArrays As Long
    uniqueArrays = outRow - 2

    MsgBox "Found " & uniqueArrays & " legacy array formula(s)." & _
           vbCrLf & _
           IIf(multiCount > 0, "  • " & multiCount & _
           " cell(s) belong to multi-cell arrays" & vbCrLf, "") & _
           vbCrLf & _
           "Report created on '" & OUT_SHEET & "' sheet." & _
           vbCrLf & "Multi-cell arrays are highlighted in orange." & _
           vbCrLf & vbCrLf & _
           "Click any cell address to jump to the array.", _
           vbInformation, "Legacy Array Finder"

CleanUp:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#HasArray — the one property you need to know

Every cell in Excel has a HasArray property. For a normal cell, it returns False. For a cell that is part of a CSE array formula, it returns True.

If cell.HasArray Then
    Set arrRng = cell.CurrentArray
End If

That’s it. One Boolean check. No string parsing, no Regex, no looping through characters hunting for curly braces. The VBA object model knows which cells are array formulas and hands you the full array range in one call via cell.CurrentArray.

This is the single most important design decision in the macro. It avoids the error-prone approach of parsing cell.Formula for { and } — which fails when a formula legitimately contains curly braces inside a string literal (e.g., =IF(A1="{test}", 1, 2)).

#Why we only report the top-left cell

Every cell in a multi-cell array reports HasArray = True. A 12-cell FREQUENCY array would produce 12 duplicate rows in the report — all pointing to the same array range with the same formula text. That’s noise, not information.

If cell.Address <> arrRng.Cells(1).Address Then _
    GoTo NextCell

By skipping every cell except the top-left cell of the array (arrRng.Cells(1)), the report shows one row per unique array formula. The “Array Range” column tells you how far it extends — C7:C18 is a 12-cell vertical array, D4:F4 is a 3-cell horizontal array.

#Multi-cell arrays get the orange treatment

Single-cell array formulas ({=SUM(IF(A2:A100="CA", B2:B100))}) are annoying but not dangerous. You can still edit them — just press Ctrl+Shift+Enter after making your change. The formula bar accepts it.

Multi-cell arrays are different. Try editing one cell in a multi-cell array and Excel refuses with “You can’t change part of an array.” The orange highlight in the report makes these visually distinct:

If InStr(wsOut.Cells(r, 4).Value, "Multi-cell") > 0 Then
    wsOut.Range("A" & r & ":E" & r).Interior.Color = _
        RGB(255, 240, 220)  ' Light orange
End If

When you scan the report, the orange rows are the ones you need to deal with first. They’re the ones blocking editing. Single-cell arrays can wait.

#FormulaArray vs Formula — they’re different

A cell inside a CSE array formula returns different values for .Formula and .FormulaArray:

' .Formula returns the formula WITHOUT curly braces
Debug.Print cell.Formula      ' =SUM(IF(A2:A100="CA", B2:B100))

' .FormulaArray returns the formula WITH curly braces
Debug.Print cell.FormulaArray ' {=SUM(IF(A2:A100="CA", B2:B100))}

The macro uses .FormulaArray so the report shows exactly what you’d see in the formula bar — curly braces included. This helps you recognize the formula when you click through to the cell.

#Counting first, reporting second

The macro makes two passes through every sheet. The first pass counts how many array formula cells exist and how many are in multi-cell arrays. The second pass builds the report.

' Pass 1: count
For Each ws In ThisWorkbook.Worksheets
    For Each cell In formulaRng
        If cell.HasArray Then
            totalArrays = totalArrays + 1
            If cell.CurrentArray.Cells.Count > 1 Then
                multiCount = multiCount + 1
            End If
        End If
    Next cell
Next ws

If the count is zero, the macro exits immediately with a helpful message:

If totalArrays = 0 Then
    MsgBox "No legacy array formulas found in this workbook." & _
           vbCrLf & vbCrLf & _
           "If you use Excel 365, your array formulas may be " & _
           "dynamic arrays...", vbInformation
    GoTo CleanUp
End If

This two-pass approach avoids creating a useless empty report sheet that the user has to delete.

#Dynamic arrays vs. CSE arrays — the distinction matters

In Excel 365, formulas like =SORT(A2:A100), =UNIQUE(B2:B100), and =FILTER(C2:C100, C2:C100>0) are dynamic arrays. They spill automatically without Ctrl+Shift+Enter. These are normal formulas — they don’t trigger the “can’t change part of an array” error and don’t need the curly-brace treatment.

However, cell.HasArray returns True for cells inside a dynamic array spill range, even though they’re not CSE formulas. The macro handles this by checking whether cell.FormulaArray actually contains curly braces before including it in the count:

' Dynamic array spill cells have HasArray=True but
' FormulaArray without curly braces
If cell.HasArray Then
    If Left(cell.FormulaArray, 1) = "{" Then
        ' This is a genuine CSE array
    End If
End If

Actually, in the current version the macro reports every HasArray cell. If you’re running Excel 365 and the report includes dynamic arrays, the distinction is usually clear: dynamic arrays will have single-function formulas like =SORT(...) without braces, while CSE arrays will have {=SUM(IF(...))} with braces. The curly braces in the Formula Text column are the giveaway.

#Place the report at the end — audit artifact, not a workpaper

Set wsOut = ThisWorkbook.Worksheets.Add( _
    After:=ThisWorkbook.Worksheets( _
        ThisWorkbook.Worksheets.Count))

The “Legacy-Arrays” sheet goes at the far end of the workbook, like every other report-output macro on this blog. It’s a one-time audit tool, not a permanent resident. After you’ve reviewed the arrays, converted the multi-cell ones, and confirmed everything works, delete the report sheet.

#Adapt It