Formula-to-Value by Age: Freeze Stale Formulas in Archive Sheets While Keeping Active Work Intact

TL;DR: This macro scans every sheet for formulas where all precedent cells are constants — the formulas whose inputs are already finalized values. It converts those “stale” formulas to values, leaves chained/active formulas untouched, and reports exactly what changed. Perfect for archiving historical schedules inside a multi-year workbook without breaking the current year’s logic.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook with a mix of “finalized” formulas (referencing only constants) and “active” formulas (referencing other formulas)
• Macros enabled

Limitations:

• Formulas referencing cells on other sheets (e.g., ='2025-TB'!A1) are treated as “active” and will NOT be converted. Cross-sheet references imply an active, multi-sheet dependency.
• Array formulas (CSE) are skipped to avoid breaking multi-cell arrays.
• Protected sheets are skipped and reported — unprotect them first.
• The staleness heuristic is conservative: a formula is only “stale” if EVERY direct precedent cell is a constant value. One formula reference in the chain and it stays.
• This macro cannot track formula creation dates — Excel doesn’t store that metadata. The “age” concept is implemented via the precedent-cell heuristic described in How It Works.

#The Macro

Option Explicit

Sub FormulaToValueByAge()
    ' ── Formula to Value by Age ─────────────────────────
    ' Converts "stale" formulas to their current values.
    '
    ' A formula is "stale" when every direct precedent cell
    ' is a constant — no chained formulas. This means its
    ' inputs are finalized and won't change unless someone
    ' manually edits a constant.
    '
    ' Cross-sheet formulas ('Sheet'!A1) and array formulas
    ' (CSE) are skipped — they're treated as "active."
    ' ────────────────────────────────────────────────────

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

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet
    Dim formulaRng As Range
    Dim cell As Range
    Dim totalFound As Long
    Dim totalConverted As Long
    Dim wsCount As Long
    Dim wsList As String
    Dim skipProtected As Long
    Dim scope As VbMsgBoxResult

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

    ' ── Step 1: Choose scope ──────────────────────────
    scope = MsgBox( _
        "Scan ALL sheets for stale formulas?" & vbCrLf & _
        vbCrLf & _
        "Yes = Scan every sheet" & vbCrLf & _
        "No  = Active sheet only: '" & ActiveSheet.Name & "'" & _
        vbCrLf & "Cancel = Quit", _
        vbYesNoCancel + vbQuestion, _
        "Formula to Value by Age")

    If scope = vbCancel Then GoTo CleanUp

    ' ── Step 2: Count stale formulas ──────────────────
    totalFound = 0
    wsCount = 0
    wsList = ""
    skipProtected = 0

    If scope = vbYes Then
        For Each ws In ThisWorkbook.Worksheets
            If ws.ProtectContents Then
                skipProtected = skipProtected + 1
            Else
                Dim foundOnSheet As Long
                foundOnSheet = CountStaleFormulas(ws)
                If foundOnSheet > 0 Then
                    If wsCount > 0 Then wsList = wsList & ", "
                    wsList = wsList & "'" & ws.Name & "'"
                    wsCount = wsCount + 1
                    totalFound = totalFound + foundOnSheet
                End If
            End If
        Next ws
    Else
        totalFound = CountStaleFormulas(ActiveSheet)
        wsList = "'" & ActiveSheet.Name & "'"
        wsCount = 1
    End If

    ' ── Step 3: Report and confirm ────────────────────
    If totalFound = 0 Then
        Dim msgNone As String
        msgNone = "No stale formulas found." & vbCrLf & vbCrLf & _
                  "A formula is 'stale' when all of its direct " & _
                  "precedent cells are constants."
        If skipProtected > 0 Then
            msgNone = msgNone & vbCrLf & skipProtected & _
                      " protected sheet(s) skipped."
        End If
        MsgBox msgNone, vbInformation, "Nothing to Convert"
        GoTo CleanUp
    End If

    Dim confirmMsg As String
    confirmMsg = "Found " & Format(totalFound, "#,##0") & _
                 " stale formula(s) across " & wsCount & _
                 " sheet(s)." & vbCrLf & vbCrLf & _
                 "Affected sheets: " & wsList & vbCrLf & vbCrLf & _
                 "Convert these formulas to their current values?" & _
                 vbCrLf & vbCrLf & _
                 "⚠ This cannot be undone by Ctrl+Z. " & _
                 "Save a backup first."

    If skipProtected > 0 Then
        confirmMsg = confirmMsg & vbCrLf & vbCrLf & _
                     "(" & skipProtected & _
                     " protected sheet(s) skipped.)"
    End If

    If MsgBox(confirmMsg, vbYesNo + vbExclamation + vbDefaultButton2, _
              "Confirm Conversion") = vbNo Then GoTo CleanUp

    ' ── Step 4: Convert ───────────────────────────────
    totalConverted = 0

    If scope = vbYes Then
        For Each ws In ThisWorkbook.Worksheets
            If Not ws.ProtectContents Then
                totalConverted = totalConverted + _
                                 ConvertStaleFormulas(ws)
            End If
        Next ws
    Else
        totalConverted = ConvertStaleFormulas(ActiveSheet)
    End If

    ' ── Step 5: Report results ────────────────────────
    MsgBox "Converted " & Format(totalConverted, "#,##0") & _
           " stale formula(s) to values." & vbCrLf & vbCrLf & _
           "Scope: " & IIf(scope = vbYes, "All sheets", _
           "Active sheet only") & _
           IIf(skipProtected > 0, vbCrLf & skipProtected & _
           " protected sheet(s) skipped.", ""), _
           vbInformation, "Done"

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

' ── Helper: Count stale formulas on a worksheet ───────
Private Function CountStaleFormulas( _
    ByRef ws As Worksheet) As Long

    Dim formulaRng As Range
    Dim cell As Range
    Dim count As Long

    count = 0
    On Error Resume Next
    Set formulaRng = ws.Cells.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0

    If formulaRng Is Nothing Then
        CountStaleFormulas = 0
        Exit Function
    End If

    For Each cell In formulaRng
        If AllPrecedentsAreConstants(cell) Then
            count = count + 1
        End If
    Next cell

    CountStaleFormulas = count
End Function

' ── Helper: Convert stale formulas on a worksheet ─────
Private Function ConvertStaleFormulas( _
    ByRef ws As Worksheet) As Long

    Dim formulaRng As Range
    Dim cell As Range
    Dim count As Long
    Dim cellsToConvert As New Collection

    count = 0
    On Error Resume Next
    Set formulaRng = ws.Cells.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0

    If formulaRng Is Nothing Then
        ConvertStaleFormulas = 0
        Exit Function
    End If

    ' Collect first, convert after — avoids modifying
    ' the formulaRng collection while iterating it
    For Each cell In formulaRng
        If AllPrecedentsAreConstants(cell) Then
            cellsToConvert.Add cell
        End If
    Next cell

    ' Now convert — each is an independent operation
    Dim i As Long
    For i = 1 To cellsToConvert.Count
        Set cell = cellsToConvert(i)
        cell.Value = cell.Value
        count = count + 1
    Next i

    ConvertStaleFormulas = count
End Function

' ── Helper: Are all direct precedent cells constants? ──
Private Function AllPrecedentsAreConstants( _
    ByRef cell As Range) As Boolean

    Dim formulaText As String
    Dim prec As Range
    Dim area As Range
    Dim pc As Range

    AllPrecedentsAreConstants = True
    formulaText = cell.Formula

    ' ── Filter 1: Cross-sheet references → active ────
    ' A formula like ='2025-TB'!A1 means the formula
    ' depends on another sheet — treat as active.
    If InStr(formulaText, "!") > 0 Then
        ' Exception: #REF! means the linked sheet is gone
        ' — treat as stale (it's a broken formula anyway)
        If InStr(formulaText, "#REF!") = 0 Then
            AllPrecedentsAreConstants = False
            Exit Function
        End If
    End If

    ' ── Filter 2: Array formulas → skip ──────────────
    ' Converting a single cell in a multi-cell array
    ' formula throws "Cannot change part of an array."
    If cell.HasArray Then
        AllPrecedentsAreConstants = False
        Exit Function
    End If

    ' ── Filter 3: Check same-sheet precedents ─────────
    On Error Resume Next
    Set prec = cell.DirectPrecedents
    On Error GoTo 0

    ' If DirectPrecedents fails (cross-sheet, merged
    ' cells, etc.), treat as active — conservative.
    If prec Is Nothing Then
        AllPrecedentsAreConstants = False
        Exit Function
    End If

    For Each area In prec.Areas
        For Each pc In area
            If pc.HasFormula Then
                ' At least one precedent is also a formula
                ' → this formula is NOT stale
                AllPrecedentsAreConstants = False
                Exit Function
            End If
        Next pc
    Next area

    ' All precedents are constants → stale
End Function

#How It Works

#What “stale” actually means

Excel doesn’t track when a formula was written. There’s no CreatedDate property. So the macro defines staleness by inspection: if every cell that feeds into this formula is a constant (a typed number, not another formula), then the formula’s inputs are finalized. Nobody is going to tweak B12 and automatically recalculate the formula — because B12 is already a static value.

This is a conservative heuristic. It errs on the side of leaving formulas alone. A single formula in the precedent chain and the whole thing stays. The only false negative is perfectly acceptable — an active-looking formula that’s actually finished. False positives (converting a formula that’s still needed) are far worse.

If pc.HasFormula Then
    AllPrecedentsAreConstants = False
    Exit Function
End If

#Three filters, in order

The AllPrecedentsAreConstants function applies three checks, and any one can block conversion:

1. Cross-sheet reference check — if the formula contains ! (e.g., ='2025-TB'!A1), it’s treated as active. Cross-sheet formulas imply a multi-sheet dependency — changing one sheet’s data changes another’s output. The only exception: #REF! in the formula means the linked sheet no longer exists. That formula is broken anyway, so it’s still convertible.

2. Array formula check — CSE array formulas ({=SUM(IF(...))}) can’t have individual cells converted to values. Trying to write cell.Value = cell.Value on a cell inside a multi-cell array throws a VBA error. The macro skips them.

3. Same-sheet precedent check — cell.DirectPrecedents returns the immediate precedent cells on the same sheet. If every one is a constant, the formula is stale.

#Why collect first, convert later

For Each cell In formulaRng
    If AllPrecedentsAreConstants(cell) Then
        cellsToConvert.Add cell
    End If
Next cell

For i = 1 To cellsToConvert.Count
    Set cell = cellsToConvert(i)
    cell.Value = cell.Value   ' convert to value
Next i

The SpecialCells(xlCellTypeFormulas) range shrinks as cells are converted to values. Modifying the range while iterating it causes cells to be skipped or double-counted. The macro collects all candidate cells first, then converts them in a second pass. It’s a few extra lines of code but eliminates the most common failure mode of this pattern.

#The cross-sheet #REF! exception

If InStr(formulaText, "#REF!") > 0 Then
    ' don't set AllPrecedentsAreConstants = False
End If

A formula like ='[Deleted_2024.xlsx]Sheet1'!$A$1 becomes =#REF! when the source workbook is deleted or moved. It contains ! (so the cross-sheet filter would normally block it), but it’s also a formula that evaluates to nothing useful. The macro lets these through — converting a #REF! to whatever value the cell currently shows (usually #REF! itself, but that will at least stop Excel from trying to resolve the phantom link on every recalc).

#Why cross-sheet formulas are “active”

A formula referencing another sheet means that sheet could change. Even if today the referenced cell is a constant, tomorrow someone could replace it with a formula. The macro can’t predict the future, so it errs on the side of preservation.

If you have cross-sheet formulas that you KNOW are final (e.g., =TB!B2 where TB!B2 will always be a static number), unprotect all sheets and use paste-values on that specific range. This macro is designed for bulk operations on same-sheet formula trees — the most common pattern in archive-vs-active workpaper separation.

#The “save a backup first” warning

The confirm message box includes an explicit backup reminder:

"⚠ This cannot be undone by Ctrl+Z. Save a backup first."

Formula-to-value is irreversible in Excel. There’s no undo after VBA runs. The confirm button defaults to No (vbDefaultButton2) so an accidental Enter key press doesn’t nuke 1,500 formulas. Combined with the explicit backup warning, this gives the user two layers of protection before the destructive operation. See also the Timestamped Backup macro for a one-click backup before running this one.

#Protected sheets get skipped, not ignored

If ws.ProtectContents Then
    skipProtected = skipProtected + 1

The macro reports how many protected sheets it skipped. This is important because archived sheets are often protected by the prior preparer. The user needs to know that Sched-A-2024 had 45 stale formulas but couldn’t be processed because it’s locked. Unprotect it and run again.

#Adapt It