Formula Chain Extractor

' Formula Chain Extractor
' Extract formulas and resolve nested cell references to show calculation chains

Sub ExtractFormulaChain()
    ' Extract visible formulas from a column and resolve references
    On Error GoTo ErrorHandler

    Application.ScreenUpdating = False

    ' Prompt for source column
    Dim sourceCol As String
    sourceCol = InputBox("Enter the column letter to extract formulas from:", "Source Column", "A")

    If sourceCol = "" Then Exit Sub

    Dim ws As Worksheet
    Set ws = ActiveSheet

    ' Find last visible row in column
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, sourceCol).End(xlUp).Row

    ' Create collection to store extracted formulas
    Dim formulaText As String
    formulaText = "Formula Chain Export - " & Format(Now, "dd/mm/yyyy hh:mm") & vbCrLf & vbCrLf

    Dim i As Long
    For i = 1 To lastRow
        ' Check if row is visible
        If Not ws.Rows(i).Hidden Then
            Dim cellRef As String
            Dim cellValue As Variant
            Dim cellFormula As String

            cellRef = sourceCol & i
            Set cellRange = ws.Range(cellRef)

            ' Get cell value
            cellValue = cellRange.Value

            ' Check if cell has formula
            If cellRange.HasFormula Then
                cellFormula = cellRange.Formula

                ' Resolve nested formulas
                Dim resolvedFormula As String
                resolvedFormula = ResolveFormulaReferences(ws, cellFormula)

                ' Add to export text
                formulaText = formulaText & "Cell " & cellRef & ":" & vbCrLf
                formulaText = formulaText & "  Formula: " & cellFormula & vbCrLf
                formulaText = formulaText & "  Resolved: " & resolvedFormula & vbCrLf
                formulaText = formulaText & "  Result: " & cellValue & vbCrLf & vbCrLf
            Else
                ' Plain value, no formula
                formulaText = formulaText & "Cell " & cellRef & ": " & cellValue & " (value)" & vbCrLf & vbCrLf
            End If

' ... 136 more lines hidden ...
'
' Sign up for free to view the complete code
' Visit: vbacode.io