#Requires -Version 5.1
<#
    JbTecWiz Support Centre -- generated fix script

    Fault : Excel formulas show as text, or never update
    Fix   : Set calculation back to automatic
    Source: https://jbtecwiz.com/support/win-office-excel-not-calculating

    Run as     : Excel
    Expect     : 10 minutes
    Risk       : low
    Reversible : yes

    WHEN THIS IS THE RIGHT FIX
    Results are stale and F9 updates them.

    HOW TO UNDO IT
    Set the mode back to Manual if it was deliberate -- some very large
    models are set that way on purpose, and switching them to Automatic can
    make the file unusably slow.

    This script walks the fix one step at a time and asks before each
    one. Steps with no command are things you do yourself -- it prints
    those and waits. Run with -DryRun to print without executing.
#>

[CmdletBinding()]
param(
    # Print every step and command without running anything.
    [switch]$DryRun,
    # Do not ask before each step. Read the script first if you use this.
    [switch]$Unattended
)

$ErrorActionPreference = 'Stop'
$script:Failed = 0

function Write-Rule { param([string]$Text)
    Write-Host ''
    Write-Host ('-' * 70) -ForegroundColor DarkGray
    if ($Text) { Write-Host $Text -ForegroundColor Cyan }
}

function Show-Prose { param([string]$Text, [string]$Colour = "Gray")
    if (-not $Text) { return }
    $words = $Text -split "\s+"; $line = "  "
    foreach ($w in $words) {
        if (($line.Length + $w.Length) -gt 74) { Write-Host $line -ForegroundColor $Colour; $line = "  " }
        $line += "$w "
    }
    if ($line.Trim()) { Write-Host $line -ForegroundColor $Colour }
}

function Invoke-Step {
    param(
        [int]$Number, [string]$Do, [string]$Why,
        [scriptblock]$Command, [switch]$Manual, [string]$Shell = "powershell"
    )
    Write-Rule "  Step $Number of 5"
    Show-Prose $Do "White"
    if ($Why) { Write-Host ""; Show-Prose $Why "DarkGray" }

    if ($Manual) {
        Write-Host ''
        Write-Host '  -> Do this yourself, then press Enter to carry on.' -ForegroundColor Yellow
        if (-not $Unattended -and -not $DryRun) { [void](Read-Host) }
        return
    }

    Write-Host ''
    foreach ($l in ($Command.ToString().Trim() -split "`n")) {
        Write-Host ("    " + $l.Trim()) -ForegroundColor Green
    }
    Write-Host ''

    if ($DryRun) { Write-Host "  (dry run -- not executed)" -ForegroundColor DarkGray; return }
    if (-not $Unattended) {
        $a = Read-Host "  Run this step? [Y]es / [S]kip / [Q]uit"
        if ($a -match "^[Qq]") { Write-Host "  Stopped at your request."; exit 0 }
        if ($a -match "^[Ss]") { Write-Host "  Skipped." -ForegroundColor DarkGray; return }
    }
    try { & $Command }
    catch {
        $script:Failed++
        Write-Host ("  Step $Number failed: " + $_.Exception.Message) -ForegroundColor Red
        Show-Prose "The rest of the fix may depend on this. Read the write-up before carrying on." "Red"
        if (-not $Unattended) {
            $c = Read-Host "  Carry on anyway? [y/N]"
            if ($c -notmatch "^[Yy]") { exit 1 }
        }
    }
}

Write-Rule
Write-Host '  Excel formulas show as text, or never update' -ForegroundColor White
Write-Host '  Set calculation back to automatic' -ForegroundColor Cyan
Write-Host ''
Write-Host '  Risk: low   Reversible   10 minutes'
Write-Rule

if (-not $Unattended -and -not $DryRun) {
    $go = Read-Host '  Ready? [y/N]'
    if ($go -notmatch "^[Yy]") { Write-Host "  Nothing was changed."; exit 0 }
}

Invoke-Step -Number 1 -Do 'Formulas -> Calculation Options -> Automatic. Or File -> Options -> Formulas -> Workbook Calculation.' -Manual

Invoke-Step -Number 2 -Do 'Understand where the setting came from before you close it. Excel takes the calculation mode from the FIRST workbook opened in that instance, and then applies it to every workbook opened afterwards in the same instance.' -Why 'This is why the problem appears to move between files at random, and why it comes back tomorrow. The file that is actually carrying the Manual setting is usually not the one being complained about -- it is whichever one gets opened first, often something on the desktop or in a startup folder.' -Manual

Invoke-Step -Number 3 -Do 'Find the workbook that is carrying it. Close everything, open the suspect file on its own, and look at the calculation mode.' -Manual

Invoke-Step -Number 4 -Do 'With that file open on its own, set calculation to Automatic and save it. That is what makes the fix stick.' -Manual

Invoke-Step -Number 5 -Do 'Check the XLSTART folder, since anything in it opens first every time.' -Command {
    Get-ChildItem "$env:APPDATA\Microsoft\Excel\XLSTART" -ErrorAction SilentlyContinue | Select-Object Name, LastWriteTime
}

Write-Rule "  Confirm it worked"
Show-Prose 'Change an input cell and the dependent formulas update immediately. Close Excel, reopen the file, and confirm it is still Automatic.' 'White'

Write-Rule
if ($script:Failed -gt 0) {
    Write-Host ("  Finished with " + $script:Failed + " failed step(s).") -ForegroundColor Yellow
    Show-Prose 'Read the full write-up at https://jbtecwiz.com/support/win-office-excel-not-calculating' 'Yellow'
} else {
    Write-Host '  Finished.' -ForegroundColor Green
}
Write-Host ''
Show-Prose 'To undo: Set the mode back to Manual if it was deliberate -- some very large models are set that way on purpose, and switching them to Automatic can make the file unusably slow.' 'DarkGray'
Write-Rule